SQLRPGLE Cursors and Recursion

470 pts.
Tags:
IBM iSeries
Recursion
SQL Cursors
SQLRPGLE
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • TomLiotta
    Hmmm... that bit about recursive program references assumes that pname comes out of ProgRec by way of "whfnam"... Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    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
    50,860 pointsBadges:
    report
  • philpl1jb
    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.
    50,860 pointsBadges:
    report
  • Dcantwell
    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!
    470 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following