By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,851 Members | 1,752 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,851 IT Pros & Developers. It's quick & easy.

Not able to close cursors.

P: 1
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
Mar 15 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.