RE: Looping through an SQL query - End of Recordset?

Bah, I completely forgot about cursors.  Thanks for the reminder!

Brian.

-----Original Message-----
From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx] 
Sent: Tuesday, May 30, 2006 10:58 AM
To: RPG programming on the AS400 / iSeries
Subject: Re: Looping through an SQL query - End of Recordset?

Something like this:

     H ActGrp(*CALLER)
     H DftActGrp(*NO)

     D OpenCursor      PR              n
     D FetchCursor     PR              n
     D CloseCursor     PR              n

     D MyLib           s             10a
     D MyFile          s             10a

      /free
       *inlr=*on;
       if not OpenCursor();
         // perform error routine to alert the troops
         // ...
       Else;
         Dow FetchCursor();
           // putting the fetchcursor on the do loop allows the user of
           // iter, and thus iter will not perform an infinite loop
           // normal processing here...
         EndDo;
         CloseCursor();
       EndIf;
       return;
      /end-free

     P OpenCursor      B
     D OpenCursor      PI                  like(ReturnVar)
     D ReturnVar       s               n

     C/EXEC SQL
     C+ Set Option
     C+     Naming    = *Sys,
     C+     Commit    = *None,
     C+     UsrPrf    = *User,
     C+     DynUsrPrf = *User,
     C+     Datfmt    = *iso,
     C+     CloSqlCsr = *EndMod
     C/END-EXEC

     C/EXEC SQL
     C+ Declare C1 cursor for
     C+  Select System_Table_Schema as library,
     C+         System_Table_Name   as file
     C+  from qsys2/systables
     C/END-EXEC

     C/EXEC SQL
     C+ Open C1
     C/END-EXEC

      /free
       Select;
         When SqlStt='00000';
           return *on;
         Other;
           return *off;
       EndSl;
      /end-free
     P OpenCursor      E

      /eject
     P FetchCursor     B
     D FetchCursor     PI                  like(ReturnVar)
     D ReturnVar       s               n
     C/EXEC SQL
     C+ Fetch C1 into :MyLib, :MyFile
     C/END-EXEC
      /free
       Select;
         When sqlstt='00000';
           // row was received, normal
           ReturnVar=*on;
         When sqlstt='02000';
           // same as %eof, sooner or later this is normal
           ReturnVar=*off;
         Other;
           // alert the troops!
           ReturnVar=*off;
       EndSl;
       return ReturnVar;
      /end-free
     P FetchCursor     E

      /eject
     P CloseCursor     B
     D CloseCursor     PI                  like(ReturnVar)
     D ReturnVar       s               n
     C/EXEC SQL
     C+ Close C1
     C/END-EXEC
      /free
       Select;
         When sqlstt='00000';
           // cursor was closed, normal
           ReturnVar=*on;
         Other;
           // alert the troops!
           ReturnVar=*off;
       EndSl;
       return ReturnVar;
      /end-free
     P CloseCursor     E 

Rob Berendt