Re: SQL RPG and open cursors

[Krish Thirumalai <krishnithya@xxxxxxxxx>]

Thanks Rick for the code that you swiped from Rob :-).

This seems to be working now.  What we ended up adding to the pgm was this:

C/EXEC SQL                   
C+ SET OPTION                
C+   CLOSQLCSR  = *ENDACTGRP 
C/END-EXEC    

IBM's documentation implies that the default compiler parm is
*ENDACTGRP, but apparently our system is using *ENDMOD.

Thanks for your help.  Aside from resolving the current issue this was
a good learning experience.

On 5/11/05, Rick DuVall <R_C_DuVall@xxxxxxxxxx> wrote:
> I would venture to guess that you need to set the crtsqlrpgi compile time
> option on the called program to Close SQL cursor . . . . . . . . CLOSQLCSR
> *ENDACTGRP
> 
> Or use
> 
> C/EXEC SQL
> C+ Set Option
> C+     Naming    = *Sys,
> C+     Commit    = *None,
> C+     UsrPrf    = *User,
> C+     DynUsrPrf = *User,
> C+     CloSqlCsr = *EndActGrp
> C/END-EXEC                       (Swiped From Rob)
> 
> HTH
> 
> Rick DuVall
> Systems Manager
> Dealer's Auto Auction of Okc
> 405 947-2886 Ext:143
> rick@xxxxxxxxxx
> 
> 
> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Krish Thirumalai
> Sent: Wednesday, May 11, 2005 1:59 PM
> To: RPG programming on the AS400 / iSeries
> Subject: SQL RPG and open cursors
> 
> The idea is the calling pgm gets the variables that determine which
> cursor to use and passes them to the SQL pgm.  On the first call for
> that set of parms the called pgm will determine which cursor to use,
> open it, and fetch the first row.  On subsequent calls we pass a flag
> that lets the pgm know the variables haven't changed so we just want
> to fetch the next row.  We only want to close the open cursor, run
> through the determination process, and open the new cursor if the
> rules from the calling pgm have changed.
> 
> The problem is we are always getting an error on the second fetch from
> a cursor opened under a previous call.  INLR is not on.
> 
> Does anyone know a way around this or have you done something similar?
> 
> Regards,
> --
> Krish Thirumalai
> 
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
> 
> 


-- 
Krish Thirumalai