sql question - declare cursor

[sever]

hi all,

i'm a newbie in rpg, and i would like to ask about declare cursor for sqlrpgle.

first, i declare a cursor for my select statement, like this:

c/exec sql                                          
c+ declare C_Sql scroll cursor for                  
c+   select * from EmpTbl
c+   where DeptName = :Dept
c/end-exec                                          
                                                    
c/exec sql                                          
c+   OPEN C_Sql                                     
c/end-exec                                          
                                                    
c/exec sql                                          
c+   FETCH Next From C_Sql INTO :Emp##
c/end-exec                                          

 *
 *
 *

c/exec sql                                                 
c+   CLOSE C_Sql                                           
c/end-exec

after that, i'm fetching it to a data structure and close the cursor.

my question, if i want to execute another sql select statement, can i use the cursor that already i declared above? if the answer is yes, then how? i tried to use declare again, it's return error when i compiled it. it said that "Cursor or procedure C_Sql previously declared".

help me please.

thx in advance.

[gcraill]

Re: sql question - declare cursor

I am also an SQL newbie ... but I'll take first shot

If your SQL returns more than 1 row then a cursor is required. For multi row selects I do it like this, where the subroutines listed contain the SQL statements as normal;

ExSr ClsCurSlct; 
ExSr DecCurSlct;                           
ExSr OpnCurSlct;                           
If   SQLCOD = 0;                           
  ExSr FetCurSlct;                         
  Dow  SQLCOD = 0;                         
    ExSr WriteToSFL;    
    ExSr FetCurSlct;    
  EndDo;                
  ExSr ClsCurSlct;      
EndIf;

If only 1 row (or no rows) is returned then a cursor is not required and this can be done;

C     CountUsers    BegSr                   
C                   Eval      LSTUSR = 0    
C/Exec Sql                                  
C+ SELECT COUNT(UGGPAD) into :LSTUSR        
C+ From SIUSRG00                            
C+ Where UGGNAM = :LSTGRP And UGGPAD = 'N'  
C/End-Exec                                  
C                   EndSr                   

 --- OR ---

C/Exec Sql                
C+ DELETE FROM SILIBL00   
C+ WHERE LLGNAM = :LSTGRP 
C/End-Exec

If you want to re-use the SAME SQL as you have defined in C_Sql then use the looping method above checking on the SQL_COD = 0 (another row was returned) to read all the rows selected.

If however you want to run a different SQL then you have to decalre (for multi row) another cursor - say C_Sql_2 etc

Hopefully I didn't screw that up too badly, and it helped.

Cheers
GC

[arrow483]

Re: sql question - declare cursor

The easiest way is to declare a new cursor for it.

[sever]

Re: sql question - declare cursor

So, it means there is no way to use the declared cursor again.
thx for the reply much apreciate it.

i'm also have another quetsion, if you don't mind.
hehe

if i have 2 identically the same tables call Table1 and TempTab1, and it contains 3 fields; field A, field B, and field C.

In my sqlrpgle, i code like this :

c/exec sql                     
c+ insert into Table1
c+     (A, B, C)
c+ select
c+      A, B, :var
c+ from TempTab1
c/end-exec

if someday i change the tables, adding 1 field call field D. So, i must change my code.
is there another way to insert, so if i change the table, i dont' need to change the code?

thx in advance

[pjk]

Re: sql question - declare cursor

If you close the cursor, you can resuse it in the same program for whatever you like (same file, or a completely different one). Also I tend to use cursors even if I only want to retrieve one record. That way I can use the prepare statement to dynamically create my SQL, rather than hardcode it in the program--then using the fetch to receive the actual field data.