MySQL CURSOR ERROR 1193: Unknown system variable

Question

I am creating a cursor for the first time.(referring this site) I made this so far(

CREATE PROCEDURE `abc`.`cursordemo` (IN start_date DATETIME,IN end_date DATETIME)
BEGIN
DECLARE done INT DEFAULT FALSE;
  DECLARE k1,k2,g,s,last_status VARCHAR(45);
  DECLARE b, c INT;
  DECLARE cur1 CURSOR FOR SELECT `key` FROM `abc`.`temp_weekly`;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur1;
 read_loop: LOOP
    FETCH cur1 INTO k1;
    IF done THEN
      LEAVE read_loop;
    END IF;
    block_cursor:BEGIN
        DECLARE cur2 CURSOR FOR SELECT `key`,`group`,`status` FROM `abc`.`jira_local` WHERE `key` = k1 AND updateddate < end_date;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE;
        OPEN cur2;
        REPEAT
            FETCH cur2 INTO k2,g,s;
            IF NOT done1 THEN


            IF s != last_status THEN
                CASE
                    WHEN s = 'verified' THEN
                    SET c = c +1;
                    WHEN s = 'closed' THEN
                    SET c = c +1;
                    WHEN s = 'to be scheduled' THEN
                    SET c = c +1;
                    WHEN s = 'deferred' THEN
                    SET c = c +1;
/*'resolved','closed','to be scheduled','deferred','validated','assigned','l3 need more info','l2 need more info','need more info'*/
                    WHEN s = 'resolved' THEN
                    SET c = c +1;
                    WHEN s = 'validated' THEN
                    SET c = c +1;
                    WHEN s = 'assigned' THEN
                    SET c = c +1;
                    WHEN s = 'l3 need more info' THEN
                    SET c = c +1;
                    WHEN s = 'l2 need more info' THEN
                    SET c = c +1;
                    WHEN s = 'need more info' THEN
                    SET c = c +1;
                END CASE;
                SET last_status = s;
            END IF;
            END IF; 
        UNTIL NOT done1 END REPEAT;
        INSERT INTO ticketsResolvedCount values(k2,g,s,c);
    END block_cursor;

  END LOOP;

  CLOSE cur1;
  CLOSE cur2;
END$$

What I am doing 1) read all keys from temp_weekly and iterate 2) find all the records from jira_local table for a particular key and count the number of times it was verified,resolved etc.

Problem : When I compile this it gives an error

ERROR 1193: Unknown system variable 'done1'

Also, I referred this link to create a stored procedure inside a loop

UPDATE: After declaring done/done1 my procedure looks like this

BEGIN

      DECLARE k1,k2,g,s,last_status VARCHAR(45);
      DECLARE b, c INT;
      DECLARE cur1 CURSOR FOR SELECT `key` FROM `abc`.`temp_weekly`;
        DECLARE done1 BOOLEAN DEFAULT FALSE;
        DECLARE done BOOLEAN DEFAULT FALSE;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        .
        .
        .
block_cursor:BEGIN
        DECLARE cur2 CURSOR FOR SELECT `key`,`group`,`status` FROM `abc`.`jira_local` WHERE `key` = k1 AND updateddate < end_date;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE;
       .
       .
       .

this gives me

ERROR 1337: Variable or condition declaration after cursor or handler declaration

I do `DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE;` look after the 2nd cursor — Bhavik Shah, Apr 05 '13 at 12:51

fedorqui · Accepted Answer · 2015-01-07T16:51:48.803

12

You need to declare done1 as you did with done:

From

DECLARE done INT DEFAULT FALSE;

to

DECLARE done, done1 INT DEFAULT FALSE;
            ^^^^^^^

edited Jan 07 '15 at 16:51

answered Apr 05 '13 at 12:53

fedorqui

275,237
103
548
598

I thought done1 should be declared within 2nd block `block_cursor:BEGIN` otherwise how will the compiler know which `done/done1` corresponds to which cursor – Bhavik Shah Apr 05 '13 at 12:55
1

I found a good reference to work with: http://stackoverflow.com/a/6099837/1983854 . Cursors are a little tricky, so I strongly recommend you to learn how to do it just with one and then move on to two, three... – fedorqui Apr 05 '13 at 12:59
`DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;` line is not declaring `done` variable. It's declaring a handler. `SET done = TRUE` is the body of the handler. Just seems like that's not clearly understood. – Nasir Apr 05 '13 at 13:09
@nsr81 : yes I guess that is the problem I'll try declaring it and then compile – Bhavik Shah Apr 05 '13 at 13:11
`ERROR 1337: Variable or condition declaration after cursor or handler declaration` it means you cannot declare anything after cursors or handlers. – fedorqui Apr 05 '13 at 13:19

MySQL CURSOR ERROR 1193: Unknown system variable

1 Answers1