RE: SQL Cursor closing at wrong time

I figured out what my problem was.  

The cursor didn't actually close.  The problem was that the called SQL
module was connecting to a different database, and I needed to do a SET
CONNECTION to set it back to the original database before doing the FETCH.

Thanks for the suggestions.

Dawn

-----Original Message-----
From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx]
Sent: Wednesday, November 19, 2003 9:23 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: SQL Cursor closing at wrong time


Once again, you might want to look at the SET OPTION sql statement.  This 
way you don't need to remember to compile with CLOSQLCSR(*ENDMOD)

     C/EXEC SQL
     C+ Set Option
     C+     Naming    = *Sys,
     C+     Commit    = *None,
     C+     UsrPrf    = *User,
     C+     DynUsrPrf = *User,
     C+     CloSqlCsr = *EndMod
     C/END-EXEC


sql's version of the h spec.

Rob Berendt
-- 
"They that can give up essential liberty to obtain a little temporary 
safety deserve neither liberty nor safety." 
Benjamin Franklin 




"Haas, Matt" <Matt.Haas@xxxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
11/18/2003 05:11 PM
Subject
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