msalem msalem - 3 months ago 8
MySQL Question

SQL code not working, unknown error

I have written a piece of code:

CREATE PROCEDURE test()
BEGIN
DECLARE
ok INT default FALSE;
curs_r1 CURSOR FOR SELECT * FROM t WHERE (b > 1 and b < 3) and (c < 2);
curs_r2 CURSOR FOR SELECT * FROM t WHERE (a = 1) and (b > 2);
CONTINUE HANDLER FOR NOT FOUND SET ok = TRUE;


SET ok = False;
DROP TABLE IF EXISTS t;
CREATE TABLE IF NOT EXISTS t (
id int,
a int,
b int,
c int
);
DROP TABLE IF EXISTS res;
CREATE TABLE IF NOT EXISTS res (
id int not null unique,
score float
);

insert into t values (0,1,2,3), (1,1,3,2), (2,3,2,1);

--------------------------------------------
OPEN curs_r1;
SET score_r1 = 0.5;
REPEAT
FETCH curs_r1 INTO
id, a, b, c;
INSERT IGNORE INTO res VALUES (id, score_r1);
UNTIL ok END REPEAT;
CLOSE curs_r1;

--------------------------------------------
SET ok = FALSE;

OPEN curs_r2;
SET score_r2 = 0.25;
REPEAT
FETCH curs_r2 INTO
id, a, b, c;
INSERT IGNORE INTO res VALUES (id, score_r2);
UNTIL ok END REPEAT;
CLOSE curs_r2;



SELECT * FROM res;
END


but this is generating too many errors like:


Error: near line 1: near "PROCEDURE": syntax Error

Error: near line 5: near "curs_r1": syntax Error

Error: near line 6: near "curs_r2": syntax Error

Error: near line 7: near "CONTINUE": syntax Error

Error: near line 10: near "SET": syntax Error

Error: near line 27: near "OPEN": syntax Error

Error: near line 28: near "SET": syntax Error

Error: near line 29: near "REPEAT": syntax Error

Error: near line 32: near "IGNORE": syntax Error

Error: near line 33: near "UNTIL": syntax Error

Error: near line 34: near "CLOSE": syntax Error

Error: near line 37: near "SET": syntax Error

Error: near line 39: near "OPEN": syntax Error

Error: near line 40: near "SET": syntax Error

Error: near line 41: near "REPEAT": syntax Error

Error: near line 44: near "IGNORE": syntax Error

Error: near line 45: near "UNTIL": syntax Error

Error: near line 46: near "CLOSE": syntax Error

Error: incomplete SQL: END


does anyone have an idea about the root cause please?

thank you very much in advance.




The errors above where fixed (thank you Darwin for the first clue) and here is the new piece of code:

DELIMITER $$
DROP PROCEDURE IF EXISTS test;
CREATE PROCEDURE test()
BEGIN
DECLARE ok INT default FALSE;
DECLARE score_r1 FLOAT default 0.5;
DECLARE score_r2 FLOAT default 0.25;
DECLARE id, a, b, c INT;
DECLARE curs_r1 CURSOR FOR SELECT * FROM t WHERE (b > 1 and b < 3) and (c < 2);
DECLARE curs_r2 CURSOR FOR SELECT * FROM t WHERE (a = 1) and (b > 2);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET ok = TRUE;

SET ok = False;
DROP TABLE IF EXISTS t;
CREATE TABLE IF NOT EXISTS t (
id int,
a int,
b int,
c int
);
DROP TABLE IF EXISTS res;
CREATE TABLE IF NOT EXISTS res (
id int not null unique,
score float
);

insert into t values (0,1,2,3), (1,1,3,2), (2,3,2,1);

OPEN curs_r1;
OPEN curs_r2;

REPEAT
FETCH curs_r1 INTO id, a, b, c;
INSERT IGNORE INTO res VALUES (id, score_r1);
UNTIL ok END REPEAT;
CLOSE curs_r1;

SET ok = FALSE;


REPEAT
FETCH curs_r2 INTO id, a, b, c;
INSERT IGNORE INTO res VALUES (id, score_r2);
UNTIL ok END REPEAT;
CLOSE curs_r2;

SELECT * FROM res;
END;
$$
DELIMITER ;


in the output, I am expecting to see two lines with id = 2 and id = 1
but I am having only one line with id=0 and score=0.5

what am I missing here?
thank you very much

Answer

So essentially you're getting an error message on every statement. In order to create a procedure from the mysql client you must enclose the entire CREATE statement within DELIMITER directives, like this:

DELIMITER $$
CREATE PROCEDURE test()
  BEGIN
  ... --code body goes here
  END;
$$
DELIMITER ;

You do have many other errors in your code, but that will solve the first one.

For more information on DELIMITER, search the Stack Overflow archives.