RE: SQL Cursor closing at wrong time

Dawn,

The WITH HOLD option only applies to COMMIT's. As far as the cursor closing, 
have you taken a look at the job log after you return from the procedure? When 
you are running in debug, you'll get messages about cursors opening and closing 
and there may be something in there that explains why it's happening.

One other thought, is the procedure in a different module? If so, was it 
complied with CLOSQLCSR(*ENDMOD)? That may be causing the cursors to get closed 
in the calling program. I haven't tested that out so it may have no impact but 
it may be something to look into.

Matt

-----Original Message-----
From: Siegrist, Dawn (EM, PTL) [mailto:Dawn.Siegrist@xxxxxxxxxx]
Sent: Tuesday, November 18, 2003 4:44 PM
To: 'RPG programming on the AS400 / iSeries'
Subject: RE: SQL Cursor closing at wrong time


Ok....but I'm not actually doing COMMIT's or ROLLBACK's.

-----Original Message-----
From: Haas, Matt [mailto:Matt.Haas@xxxxxxxxxxx]
Sent: Tuesday, November 18, 2003 4:38 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: SQL Cursor closing at wrong time


Dawn,

When you do COMMIT's or ROLLBACK's, you need to specify the HOLD option on
them or the cursor's will be closed. This is explained in the SQL
programming books.

Matt

-----Original Message-----
From: Siegrist, Dawn (EM, PTL) [mailto:Dawn.Siegrist@xxxxxxxxxx]
Sent: Tuesday, November 18, 2003 4:23 PM
To: RPG400 List (E-mail)
Subject: SQL Cursor closing at wrong time


I am having a problem with an embedded SQL program which calls a procedure
that also has embedded SQL.  In the calling program, I prepare the
statement, declare the cursor, open the cursor and do a fetch statement.  It
then does some processing on the fetched record and calls a procedure that
also does SQL using a cursor (with a different cursor name).  The procedure
closes the cursor prior to the return statement.  In the calling statement,
I then do a fetch to get the next statement and find that the cursor is
closed.  Why is the cursor from the calling program closed?  I tried putting
a WITH HOLD on the DECLARE CURSOR, but that didn't help.

Anyone have any ideas?

Dawn Siegrist
Sr. Programmer/Analyst
Penske Truck Leasing Co.
610-775-6243
Dawn.Siegrist@xxxxxxxxxx