Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2011
    Posts
    10

    Unanswered: endless loop when use cursor to fetch data to param

    Hello,
    I have a store procedure using a cursor to fetch data to a parametter and then call a different store procedure. But it run with endless loop. How can I fix it?

    CREATE PROCEDURE SP_TEMP
    (OUT v_RETURNVALUE INTEGER)
    LANGUAGE SQL
    MODIFIES SQL DATA
    BEGIN
    DECLARE v_IDPRODUCT INTEGER;
    DECLARE v_SUB_RETURN CHAR(5) DEFAULT '00000';
    DECLARE SQLCODE INTEGER DEFAULT 0;
    DECLARE C1 CURSOR FOR SELECT IDPRODUCT FROM TBLPRODUCT ;
    BEGIN

    OPEN C1;
    FETCH C1 INTO v_IDPRODUCT;

    WHILE (SQLCODE=0) DO
    CALL SP_DATA_DELETE(v_IDPRODUCT,v_SUB_RETURN );
    FETCH C1 INTO v_IDPRODUCT;
    END WHILE;
    CLOSE C1;

    END;

    SET v_RETURNVALUE = 0;

    END

    Thank !
    Last edited by nmcuong; 10-17-11 at 01:36.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Your problem is using SQLCODE for your loop control. It is set after every SQL statement. Use a condition handler to set a variable and use that variable as the loop control.

    Andy

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Alternatively, you could use a FOR loop: FOR (cursor variant) statement (PL/SQL) - IBM DB2 9.7 for Linux, UNIX, and Windows This will avoid the separate cursor and the end-of-data handling.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Jul 2011
    Posts
    10
    Quote Originally Posted by stolze View Post
    Alternatively, you could use a FOR loop: FOR (cursor variant) statement (PL/SQL) - IBM DB2 9.7 for Linux, UNIX, and Windows This will avoid the separate cursor and the end-of-data handling.
    Ok, thank !

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by ARWinner View Post
    Your problem is using SQLCODE for your loop control. It is set after every SQL statement.
    That can't explain the infinite loop here: the SQLCODE tested in the WHILE condition is always the one returned by FETCH. And there are no fetches with untested SQLCODE. (Unless there are and we don't see them here in this simplified version...)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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