Home > IT Answers > AS/400 > SQLRPGLE Cursors and Recursion
Dcantwell
310 pts.
Q:
SQLRPGLE Cursors and Recursion
SQLRPGLE, iSeries, SQL Cursors, Recursion
I'm trying to recursively call a PI in my SQLRPGLE program.

Problem is, I get an SQLCODE error of -519 and I'm sure it has to do with cursors and the prepare statement in my PI.

Here's the code:

 pprintPrograms    b
      dprintPrograms    PI
      d program                       10a   value
      d spacer                        10a   value
      d
      C/FREE
        clear sql_stmt;
        //Prepare the statement with the name passed
        sql_stmt='SELECT whfnam,whlnam FROM DAVE WHERE WHPNAM='''+program+
           ''' AND WHOBJT=''P'' ORDER BY whfnam';
        exec sql prepare stmt from :sql_stmt;
        exec sql declare programs cursor for stmt;
        exec sql open programs;
        //Lets process all of the records
        exec sql fetch programs into :ProgRec;
        DOW sqlcode=0;
           output=%trim(spacer)+%trim(lname)+'/'+pname;
           write dcout;
           lines+=1;
           IF lines=50;
             write DChead;
             lines=0;
           ENDIF;
           //Recursively call with the program passed to it!
           printPrograms(pname:%trim(spacer)+'--');
           exec sql fetch programs into :ProgRec;
        ENDDO;
        exec sql close programs;
        return;
       /END-FREE
      PprintPrograms    e                           


I was wondering if there's some way to dynamically create cursors.
 
Any ideas??

Thanks,
Dave
ASKED: Nov 19 2009  11:09 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
Teandy
4495 pts.
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • Bookmark and Share
I think that this is one of those cases where native I/O works better than SQL.

Here is a subprocedure I wrote a while back for a BOM project. You should be able to modify it to work for you.



 
 ‚**********************************************************************  
 ‚*  SET COMPILER OPTIONS                                                 
 ‚**********************************************************************  
 HNOMAIN                                                                  
 HOPTION(*NODEBUGIO:*SRCSTMT)                                             
 ‚**********************************************************************  
 ‚*  PROGRAM NAME: GETSTDCOMR                                             
 ‚*  CREATION DATE: 09/07/07                                              
 ‚*  PURPOSE OF PROGRAM: RETURNS AN ARRAY OF COMPONENTS USED BY THE       
 ‚*                      PASSED IN PRODUCT NUMBER FOR THE STANDARD        
 ‚*                      COST SHEET.                                      
 ‚*                                                                       
 ‚*********************************************************************   
 ‚* FILES USED IN PROGRAM.                                                
 ‚*********************************************************************   
 FBLLRMTL1  IF   E           K DISK                                       
 FCOMPONENPFIF A E           K DISK                                       
 ‚*********************************************************************   
 ‚* PROTOTYPE FOR PROCEDURE                                               
 ‚*********************************************************************   
 D  GETSTDCOMR     PR                                                     
 D  PROD#                        15A                                      
 ‚*********************************************************************   
 ‚* START PROCEDURE DEFINITION                                            
 ‚*********************************************************************   
 P  GETSTDCOMR     B                   EXPORT                             
 D  GETSTDCOMR     PI                                                     
 D  PROD#                        15A                                      
 ‚*********************************************************************   
 ‚* FIELD DEFINITIONS.                                                    
 ‚*********************************************************************   
 D  ORIGINAL_PROD#...                                                     
 D                 S             15    INZ(*BLANKS)                       
 D  ORIGINAL_SEQ#...                                                      
 D                 S              3  0 INZ(0)                             
                                                                          
                                                                          
                                                                          
   //‚******************************************************************  
   //‚* START FREE FORM CALCS.                                            
   //‚******************************************************************  
                                                                          
  /FREE                                                                   
                                                                          
   //‚******************************************************************  
   //‚* READ QUOTE MATERIAL FILE.                                         
   //‚******************************************************************  
                                                                          
     ORIGINAL_PROD# = PROD#;                                              
                                                                          
     SETLL (PROD#) BLLRMTL1;                                              
                                                                          
     READE (PROD#) BLLRMTL1;                                              
                                                                          
     DOW NOT %EOF(BLLRMTL1);                                              
                                                                          
   //‚******************************************************************  
   //‚* IF THE PRODUCT/MATERIAL FLAG = P, DO THE FOLLOWING.               
   //‚* SAVE THE SEQUENCE NUMBER.                                         
   //‚* WRITE THE COMPONET TO THE WORK FILE.                              
   //‚* RECURSIVELY CALL THE SUBPROCEDURE.                                
   //‚* SET THE FILE POINTER TO THE NEXT RECORD.                          
   //‚******************************************************************  
                                                                          
       IF NPMFLG = 'P';                                                   
                                                                          
         ORIGINAL_SEQ# = NRTGSQ;                                          
         COMPROD# = NCOMP#;                                               
         WRITE COMPONENT;                                                 
         CLEAR COMPONENT;                                                 
                                                                          
         CALLP GETSTDCOMR(NCOMP#);                                        
                                                                          
         SETGT (ORIGINAL_PROD# : ORIGINAL_SEQ#) BLLRMTL1;                 
                                                                          
       ENDIF;                                                             
                                                                          
   //‚******************************************************************  
   //‚* READ NEXT RECORD USING ORIGINAL PROD#                             
   //‚******************************************************************  
                                                                          
       READE (ORIGINAL_PROD#) BLLRMTL1;                                   
                                                                          
     ENDDO;                                                               
                                                                          
                                                                          
                                                                          
                                                                          
  /END-FREE                                                               
                                                                          
 P  GETSTDCOMR     E
Last Answered: Nov 25 2009  8:33 PM GMT by Teandy   4495 pts.
  •   
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

TomLiotta   15455 pts.  |   Nov 20 2009  11:50PM GMT

I hope you don’t run across any recursive program references — there’s no way out that I can see if you do.

Assuming that a PR for printPrograms() would reference it as a proc…

I’m not at all confident that this would work. I would probably try having printPrograms() referencing an EXTPGM() rather than an internal (recursive) proc. I’d have that program run in a *NEW activation group to see how much control I could have over the SQL definitions and their relationships to available boundaries. By forcing each level into a new AG, you might be able to have scoping control over everything. Maybe.

But you still need to ensure that there are no recursive program references in DAVE.

Tom

 

TomLiotta   15455 pts.  |   Nov 20 2009  11:58PM GMT

Hmmm… that bit about recursive program references assumes that pname comes out of ProgRec by way of “whfnam”…

Tom

 

Philpl1jb   25660 pts.  |   Nov 21 2009  11:38AM GMT

no..but if you pass one more variable .. level - add one to it each level down and subtract one whenever you complete a level
and have seperate code based on a select where Level structure with 10 seperate cursors CursorLevel1 .. say to CursorLevel10.
You would then support 10 levels of recursion.

Phil

 

Philpl1jb   25660 pts.  |   Nov 21 2009  11:40AM GMT

Continuing on .. seperate cursors for levels would be necessary because when you return to a higher level you expect to pick up by fetching the next row from that level’s cursor.

 

Dcantwell   310 pts.  |   Nov 23 2009  1:08PM GMT

Hmm…. Looks like this isn’t going to work the way I want it to.

It’s a little more elaborate than I’d like it to be. I wanted all the processing handled inside of the same program. I might just have to skip using SQLRPGLE and try something else.

Thanks for your help everyone!