SQLRPGLE Cursors and 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:
Software/Hardware used:
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 eI was wondering if there's some way to dynamically create cursors. Any ideas?? Thanks, Dave
Software/Hardware used:
ASKED:
November 19, 2009 11:09 PM
UPDATED:
April 12, 2012 10:30 AM
Answer Wiki:
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.
<pre>
‚**********************************************************************
‚* 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
</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
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
Hmmm… that bit about recursive program references assumes that pname comes out of ProgRec by way of “whfnam”…
Tom
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
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.
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!