Hi,
I am new to DB2. I have created a stored proc and have DECLARED a Cursor on a TEMPORAY TABLE. When I execute this SP from a .Net code using command.Executereader it leaves a cursor open even after closing the Reader.
BUT If DECLARE my cursor on a database table and NOT on TEMPORARY Table, cursor is closed as soon as I close the datareader.
Can anybody tell me why is this cursor behaving differently depending on what is an underlying table.
This is a dummy SP that I am trying to execute
******************************************
CREATE PROCEDURE TEST1SP ( )
RESULT SETS 1
LANGUAGE SQL
FENCED
COLLID NULLID
WLM ENVIRONMENT D2TAENV3
RUN OPTIONS 'NOTEST(NONE,*,*,*)'
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
-- SQLState 42704 is set when dropping temporary table if it doesn't exist
DECLARE err_drop_temp condition for '42704';
DECLARE err_dummy INTEGER default 0;
-- Declare cursor
DECLARE SANCUR_1 CURSOR WITH RETURN FOR
SELECT C_S_VIN, C_S_VRM
FROM SESSION.TESTTEMP;
-- Drop the temporary table, continuing if it didn't exist.
DECLARE CONTINUE HANDLER FOR err_drop_temp set err_dummy = 1;
DROP TABLE Session.TESTTEMP;
DECLARE GLOBAL TEMPORARY TABLE TESTTEMP
(
C_S_VIN CHAR(25),
C_S_VRM CHAR(12)
)
ON COMMIT PRESERVE ROWS;
INSERT INTO SESSION.TESTTEMP(C_S_VIN, C_S_VRM)
SELECT C_S_VIN, C_S_VRM
FROM T002_DVLAREG
WHERE C_S_VRM='P817SPV';
-- Cursor left open for client application
OPEN SANCUR_1;
END P1
**********************************
Thanks in advance.
Sandeep