Invalid cursor exception

Shefu · #1 (**permalink**) 01-19-06, 03:33

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
@

sathyaram_s · #2 (**permalink**) 01-19-06, 05:41

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
@

umayer · #3 (**permalink**) 01-19-06, 06:15

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

umayer · #4 (**permalink**) 01-19-06, 06:15

pls. ignore - posted double

sathyaram_s · #5 (**permalink**) 01-19-06, 06:30

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

Shefu · #6 (**permalink**) 01-19-06, 06:42

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

sathyaram_s · #7 (**permalink**) 01-19-06, 06:47

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

Shefu · #8 (**permalink**) 01-19-06, 07:17

Quote:

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

sathyaram_s · #9 (**permalink**) 01-19-06, 07:23

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

Shefu · #10 (**permalink**) 01-19-06, 07:28

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

Internet Services

Web Development

Digital Marketing

Consumer Tech