Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2005
    Posts
    127

    Unanswered: Invalid cursor exception

    Hi,

    I have a DB2 stored procedure which has two cursors in it. The values fetched from the first cursor are stored in to a local variable and then feeded to the select statement of the second cursor. To make this work i ve a while loop which loops until something is fetched from the first cursor.

    But during execution the loop is successful only for the first iteration and the second iteration of the loop throws some exception with SQL CODE -502.

    cud some one let me know where i am going wrong.

    the stored procedure is as follows..
    Code:
    CREATE PROCEDURE TESTRES
    (
    	OUT                        NumberofRows	INTEGER
    )
    SPECIFIC  TESTRES
    DYNAMIC RESULT SETS 2
    LANGUAGE     SQL
    BEGIN
    	DECLARE   SQLCODE                    INTEGER   DEFAULT  0;
    	DECLARE   S_SQLCODE                  INTEGER   DEFAULT  0;
    	DECLARE   var                     SMALLINT;
    
    	DECLARE TESTCUR CURSOR WITH HOLD FOR SELECT col from tab1 FOR READ ONLY;
    
    	DECLARE TEMPCUR CURSOR WITH HOLD WITH RETURN TO CLIENT FOR SELECT col FROM  tab2 WHERE COLID  = var;
    
    	DECLARE   CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
    		SET   S_SQLCODE=  SQLCODE;
    
    	OPEN TESTCUR;
    
    		FETCH  TESTCUR INTO var;
    
    		Loop1:	 WHILE SQLCODE = 0	DO
    			BEGIN
    				OPEN   TEMPCUR ;  ----> this place throws the error with SQLCODE : -502 in the second iteration of the while loop
    				IF SQLCODE <> 0
    					return -20;
    				END IF;
    				FETCH  var;
    			END;
    		END WHILE;
    		RETURN  0;
    END
    @

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    It is advisable to do a join . For the momet, can't think of anything else

    Cheers
    Sathyaram

    Quote Originally Posted by Shefu
    Hi,

    I have a DB2 stored procedure which has two cursors in it. The values fetched from the first cursor are stored in to a local variable and then feeded to the select statement of the second cursor. To make this work i ve a while loop which loops until something is fetched from the first cursor.

    But during execution the loop is successful only for the first iteration and the second iteration of the loop throws some exception with SQL CODE -502.

    cud some one let me know where i am going wrong.

    the stored procedure is as follows..
    Code:
    CREATE PROCEDURE TESTRES
    (
        OUT                        NumberofRows    INTEGER
    )
    SPECIFIC  TESTRES
    DYNAMIC RESULT SETS 2
    LANGUAGE     SQL
    BEGIN
        DECLARE   SQLCODE                    INTEGER   DEFAULT  0;
        DECLARE   S_SQLCODE                  INTEGER   DEFAULT  0;
        DECLARE   var                     SMALLINT;
     
        DECLARE TESTCUR CURSOR WITH HOLD FOR SELECT col from tab1 FOR READ ONLY;
     
        DECLARE TEMPCUR CURSOR WITH HOLD WITH RETURN TO CLIENT FOR SELECT col FROM  tab2 WHERE COLID  = var;
     
        DECLARE   CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
            SET   S_SQLCODE=  SQLCODE;
     
        OPEN TESTCUR;
     
            FETCH  TESTCUR INTO var;
     
            Loop1:     WHILE SQLCODE = 0    DO
                BEGIN
                    OPEN   TEMPCUR ;  ----> this place throws the error with SQLCODE : -502 in the second iteration of the while loop
                    IF SQLCODE <> 0
                        return -20;
                    END IF;
                    FETCH  var;
                END;
            END WHILE;
            RETURN  0;
    END
    @
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Dec 2005
    Posts
    273
    before you can open the cursor TEMPCUR the second time, you have to close it first. I do not see a CLOSE TEMPCUR statement within your procedure

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    pls. ignore - posted double

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    My assumption was 'OPEN TEMPCUR ' was used to return the dynamic resultsets , Shefu, isn't this the case ?

    umayer, if you close the cursor, don't you have to DECLARE it again ?

    Cheers
    Sathyaram



    Quote Originally Posted by umayer
    before you can open the cursor TEMPCUR the second time, you have to close it first. I do not see a CLOSE TEMPCUR statement within your procedure
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Apr 2005
    Posts
    127
    Thanks folks

    and Sathyaram u r right, it returns the dynamic result sets to the client. if i closed the cursor it would not return the result set.

    Any way tried out a new solution. it works fine ..

    i wrote a separate procedure p1(In param1) with a cursor declared in it . The cursor inside 'p1' uses the parameter param1 in its select query. and lets it open..

    in the while loop of the above procedure i call this procedure p1 instead of that OPEN TEMPCUR cursor.

    this provides me the necessary result set to the client..

    Thanks and regards,
    Sn

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Good work, Shefu ..

    Is there a reason you cannot use a join and make the stmts into a single cursor....? Just out of curiosity

    Cheers
    Sathyaram

    Quote Originally Posted by Shefu
    Thanks folks

    and Sathyaram u r right, it returns the dynamic result sets to the client. if i closed the cursor it would not return the result set.

    Any way tried out a new solution. it works fine ..

    i wrote a separate procedure p1(In param1) with a cursor declared in it . The cursor inside 'p1' uses the parameter param1 in its select query. and lets it open..

    in the while loop of the above procedure i call this procedure p1 instead of that OPEN TEMPCUR cursor.

    this provides me the necessary result set to the client..

    Thanks and regards,
    Sn
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Apr 2005
    Posts
    127
    Is there a reason you cannot use a join and make the stmts into a single cursor....? Just out of curiosity

    Cheers
    Sathyaram

    I can well try with a join too.. jus to know how it works with a separate cursor,...


    I have another query regarding the cursors and temporary tables.. jus follow the below steps

    1. I declare a global temp. table in a procedure P1

    2. From P1 I call another procedure P2

    3. In P2 i ve the following Insert statement to populate the data into temp table in P1

    Code:
    		INSERT INTO  SESSION.TEMPTAB
    		SELECT COL1 FROM LOCTAB
    		WHERE	 COLID   = var
    4. now in P1 i open the cursor declared with the select query

    Code:
    CREATE PROCEDURE  P1
    (
    	IN                         var	smallint;
    )
    SPECIFIC  P1
    DYNAMIC RESULT SETS 1
    LANGUAGE     SQL
    BEGIN
    	DECLARE   SQLCODE                    INTEGER   DEFAULT  0;
    	DECLARE   S_SQLCODE                  INTEGER   DEFAULT  0;
    
    	DECLARE   CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
    	SET   S_SQLCODE=  SQLCODE;
    
    	DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMPTAB
    		(
    			col1  smallint
    		) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;
    
    	BEGIN
    		DECLARE   S_Temp_Cursor0    CURSOR    WITH HOLD     WITH RETURN TO CLIENT FOR   SELECT * From SESSION.TEMPTAB;
    
    		CALL P2(var);
    
    		OPEN   S_Temp_Cursor0 ;
    
    		RETURN  0;
    	END;
    END
    @

    The select query in step (3) returns the column value of just two rows.. but the result set returned by the procedure P1 has all the rows of LOCTAB table...

    Am i missing some thing..

    Regards
    Sn

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Post P2's code also ..
    Quote Originally Posted by Shefu
    I can well try with a join too.. jus to know how it works with a separate cursor,...


    I have another query regarding the cursors and temporary tables.. jus follow the below steps

    1. I declare a global temp. table in a procedure P1

    2. From P1 I call another procedure P2

    3. In P2 i ve the following Insert statement to populate the data into temp table in P1

    Code:
            INSERT INTO  SESSION.TEMPTAB
            SELECT COL1 FROM LOCTAB
            WHERE     COLID   = var
    4. now in P1 i open the cursor declared with the select query

    Code:
     
    CREATE PROCEDURE  P1
    (
        IN                         var    smallint;
    )
    SPECIFIC  P1
    DYNAMIC RESULT SETS 1
    LANGUAGE     SQL
    BEGIN
        DECLARE   SQLCODE                    INTEGER   DEFAULT  0;
        DECLARE   S_SQLCODE                  INTEGER   DEFAULT  0;
     
        DECLARE   CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
        SET   S_SQLCODE=  SQLCODE;
     
        DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMPTAB
            (
                col1  smallint
            ) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;
     
        BEGIN
            DECLARE   S_Temp_Cursor0    CURSOR    WITH HOLD     WITH RETURN TO CLIENT FOR   SELECT * From SESSION.TEMPTAB;
     
            CALL P2(var);
     
            OPEN   S_Temp_Cursor0 ;
     
            RETURN  0;
        END;
    END
    @

    The select query in step (3) returns the column value of just two rows.. but the result set returned by the procedure P1 has all the rows of LOCTAB table...

    Am i missing some thing..

    Regards
    Sn
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  10. #10
    Join Date
    Apr 2005
    Posts
    127
    Here it is... Stored Procedure P2...

    Code:
    CREATE PROCEDURE P2
    (
    	IN                         var	smallint
    )
    SPECIFIC  P2
    LANGUAGE     SQL
    BEGIN
    	DECLARE   SQLCODE                    INTEGER   DEFAULT  0;
    	DECLARE   S_SQLCODE                  INTEGER   DEFAULT  0;
    	DECLARE   CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
    	        SET   S_SQLCODE=  SQLCODE;
    	
            INSERT INTO  SESSION.TEMPTAB
    	SELECT COL
    	FROM  LOCTAB  WHERE COLID  = var;
    
    		RETURN  0;
    END
    @
    Regards
    Sn

Posting Permissions

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