Declare handler not found with curosr issue

nithinics · #1 (**permalink**) 08-23-11, 02:26

Hi,

Code:

....
DECLARE done INT DEFAULT 0;
DECLARE s VARCHAR(100) DEFAULT '';

#first query
DECLARE reader CURSOR FOR
    SELECT id, title FROM products ;
  DECLARE  CONTINUE HANDLER FOR NOT FOUND SET done=1;

OPEN reader 
REPEAT 

FETCH reader INTO vId, vTitle;

SET s = CONTACT(s, done, ':') ;
#second query
SELECT someother, details FROM another_table WHERE id=someid;
SET s = CONTACT(s, done) ;

UNTIL done
END REPEAT;

I've 10 records in products table so i want to iterate this loop 10 times. But when there is no item found in second query( another_table) loop exits. When i select the var s, i get the result
0:1.

That means When there is no result in SECOND query the DECLARE HANDLER NOT FOUND will execute.

Actually I dont want to stop the loop if there is no result in second query, I want to stop only if there is
no more items in first query.

How to do that, please help me.

thanks to all..

it-iss.com · #2 (**permalink**) 08-23-11, 04:34

Try this:
Use BEGIN/END and declare continue handler for inside this.

Code:

....
DECLARE s VARCHAR(100) DEFAULT '';

#first query
DECLARE done INT DEFAULT 0;
DECLARE reader CURSOR FOR
    SELECT id, title FROM products ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN reader 
REPEAT 

FETCH reader INTO vId, vTitle;

#second query
BEGIN
  DECLARE innerdone INT DEFAULT 0;
  DECLARE  CONTINUE HANDLER FOR NOT FOUND SET innerdone=1;

  SELECT someother, details FROM another_table WHERE id=someid;
  SET s = CONCAT(s, done) ;
END;

UNTIL done
END REPEAT;

Internet Services

Web Development

Digital Marketing

Consumer Tech