SQL code not working, unknown error

Question

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

By cutting off your error message where you did, you've eliminated the most informative part of it. — Uueerdo
– Uueerdo, Commented Aug 12, 2016 at 16:30
First thing I notice is only the first variable has the required DECLARE. Also, generally bad form to use SELECT * in a cursor, but since you've got the table creations in the proc, it's not entirely horrible. — Uueerdo
– Uueerdo, Commented Aug 12, 2016 at 16:32
You cut the error message exactly where it tells you where the error is. — axiac
– axiac, Commented Aug 12, 2016 at 16:33
What are you using to write and/or execute this? MySQL Workbench should give you at least a basic framework to start with so you don't error on line 1. — Uueerdo
– Uueerdo, Commented Aug 12, 2016 at 17:04

Darwin von Corax · Accepted Answer · 2016-08-12 16:40:03Z

2

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.

answered Aug 12, 2016 at 16:40

Darwin von Corax

5,2643 gold badges20 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dark Over a year ago

thanks for the first tip, unblocking me there helped me debug the rest. could you please have an additional look at my edit? thanks a lot

dark Over a year ago

it is ok now, changing the variable names fixed it. thank you

Darwin von Corax Over a year ago

Looks okay for the most part, although I notice you aren't doing anything with the data you FETCH from your cursor.

Collectives™ on Stack Overflow

SQL code not working, unknown error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related