RE: Is SQL cursor open at the job level?

Lim,

Wished the cursor could be a variable, too. 

Perhaps I was too short in answering and therfore not clear enough. I mean, 
declare multiple cursors and open each one based on the level passed.

Somethink like:
C                                 Select
C                                 When                  Level = 1
C/exec sql                                                
C+ declare c1 scroll cursor for                           
C+   select *                                             
C+     from MYFILE                                        
C+     where PARENT = :piMember or                        
C+           CHILD  = :piMember                           
C/end-exec                                                
C                                 When                  Level = 2
C/exec sql                                                
C+ declare c2 scroll cursor for                           
C+   select *                                             
C+     from MYFILE                                        
C+     where PARENT = :piMember or                        
C+           CHILD  = :piMember                           
C/end-exec                                                
C                                 EndSl
C                                 Select 
C                                 When                  Level = 1
C/exec sql                                                
C+ open C1                                                
C/end-exec                                                
C                                 When                  Level = 2
C/exec sql                                                
C+ open C2                                                
C/end-exec                                                
C                                  EndSl  
.... etc.

I know it will enlarge the code a lot. You should know how many levels deep it 
can go, as well.

Regards,
Carel Teijgeler  

*********** REPLY SEPARATOR  ***********

On 12-4-06 at 14:45 Lim Hock-Chai wrote:

>I don't think embedded sql allow a variable name for a cursor.  Unless I miss 
>something. 
>
>-----Original Message-----
>
>Pass a level to the procedure and use that level to open another cursor,
>When returning to the previous level close the cursor.
>
>This means you have to declare different cursors.
>
>*********** REPLY SEPARATOR  ***********
>
>>I can't close the cursor, the parent thread still needs it.  See below  for 
>>detail on what I'm doing:
>>
>>Below is what I got (Not able to test it because when it recursive 
>>calls the getFamilyMember, it gets the cursor already open error and the 
>>return result become incorrect:
>>
>>D getFamilyMember...                                          
>>D                 pr            90    varying                 
>>D  piMember                      3    value                   
>>                                                              
>>D MYFILE        E DS                                          
>>D family          s             90    varying                 
>>                                                              
>>                                                              
>>C                   eval      family = getFamilyMember('DDD') 
>>                                                              
>>C                   eval      *inlr = *on                     
>> *************************************************************
>>P getFamilyMember...                                          
>>P                 B                   export                  
>>D                 pi            90    varying                 
>>D  piMember                      3    value                   
>>                                                              
>>D prList          s             90    varying static          
>>                                                              
>>C                   if        piMember = *blank           
>>C                   return    prList                      
>>C                   endif                                 
>>                                                          
>>C                   eval      prList = prList+piMember+';'
>>                                                          
>>C/exec sql                                                
>>C+ declare c1 scroll cursor for                           
>>C+   select *                                             
>>C+     from MYFILE                                        
>>C+     where PARENT = :piMember or                        
>>C+           CHILD  = :piMember                           
>>C/end-exec                                                
>>                                                          
>>C/exec sql                                                
>>C+ open C1                                                
>>C/end-exec