RE: logic to prepare and close sql cursor

Wrong assumption.

An open cursor is much like a file open.  If the file or cursor is
empty...they can still be opened.  As a rule of thumb I would code the
close cursor & if sql error occurs due to unopened cursor just ignore
it. 


Thanks,
Tommy Holden


-----Original Message-----
From: rpg400-l-bounces+tommy.holden=hcahealthcare.com@xxxxxxxxxxxx
[mailto:rpg400-l-bounces+tommy.holden=hcahealthcare.com@xxxxxxxxxxxx] On
Behalf Of Jim Franz
Sent: Friday, March 03, 2006 10:49 AM
To: RPG400-L@xxxxxxxxxxxx
Subject: logic to prepare and close sql cursor

I keep getting this msg in the job log. This process is not halting, but
something seems wrong. 
Below is a clip of the rpgle code that handles prepare, open, fetch,
close. 
I do see that often when a select is run, and no records exist matching
the select, it reaches the 
close section with sqlcod=100 and sqlstt=02000. 
I am assuming with these codes, the cursor is not open, and does not
need to be closed.
Sorry for the length of this post, but I've searched many prev posts and
cannot see exactly how 
this should work. 

jim franz

SQL0519    Diagnostic              30   03/02/06  23:47:06.128992
QSQRUN1      QSYS        *STMT    QSQRUN1     QS
                                     From module . . . . . . . . :
QSQPREP                                       
                                     From procedure  . . . . . . :
CLEANUP                                       
                                     Statement . . . . . . . . . :
10872                                         
                                     To module . . . . . . . . . :
QSQPREP                                       
                                     To procedure  . . . . . . . :
CLEANUP                                       
                                     Statement . . . . . . . . . :
10872                                         
                                     Message . . . . :   Prepared
statement S2 in use.                             
                                     Cause . . . . . :   The application
program has attempted to prepare statement
                                       S2. This statement is the SELECT
statement for cursor C2 that is currently  
                                       open. Recovery  . . . :   Change
the statement name in the PREPARE statement
                                       or correct the logic of the
application program so that it closes cursor C2 
                                       before attempting the PREPARE
statement again.             

The code-this program processes many records, and executes a select this
select statement for each :

c                   eval      #close1  = 0        
c                   clear                   sqlcod
c                   clear                   sqlstt
c/exec sql whenever sqlerror go to end2             
c/end-exec                                          
c/exec sql whenever not found go to end2            
c/end-exec                                          
c/exec sql                                          
c+ prepare s2 from :salestmt                        
c/end-exec                                          
c/exec sql                                          
c+ declare c2 cursor for s2                         
c/end-exec                                          
c/exec sql                                          
c+ open c2                                          
c/end-exec                                          
c                   dou       SqlCod     <> *zero   
c/exec sql                                       
c+ fetch c2 into :InputPS                        
c/end-exec                                       
c     sqlstt        cabeq     '24501'       end2 
...lots of code....   
c                   enddo                                     
c     end2          tag                                       
 * close cursor                                               
c* if open cursor error - close cursor (only do once)         
C                   if        sqlstt = '24506' and #close1= 0 
c                   eval      #close1= #close1+ 1             
c/exec sql                                                    
c+ close c2                                                   
c/end-exec                                                    
c                   endif