Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2011
    Posts
    4

    Question Unanswered: Declare handler not found with curosr issue

    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..

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •