12 FETCH and sub-procedures

**FREE
ctl-opt ActGrp(*CALLER) DftActGrp(*NO);

//********** Prototypes for Subprocedures **********
dcl-pr OpenCursor  ind end-pr;
dcl-pr FetchCursor ind end-pr;
dcl-pr CloseCursor ind end-pr;

//********** Stand Alones **********
dcl-s MyLib char(10);
dcl-s MyFile char(10);

//********** Main Loop **********

*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;

//********** OpenCursor **********
dcl-proc OpenCursor;
dcl-pi   OpenCursor like(ReturnVar) end-pi;

dcl-s ReturnVar ind;

// The immediately following /EXEC SQL is SQL's version of RPG's H Spec
// It is never executed.  Just used at compile time.

EXEC SQL Set Option
         Naming    = *Sys,
         Commit    = *None,
         UsrPrf    = *User,
         DynUsrPrf = *User,
         Datfmt    = *iso,
         CloSqlCsr = *EndMod;

EXEC SQL Declare C1 cursor for
         Select System_Table_Schema as library,
                System_Table_Name   as file
         from qsys2/systables;

EXEC SQL Open C1;

Select;
When SqlStt='00000';
      return *on;
Other;
      return *off;
EndSl;

end-proc OpenCursor;

       //********** FetchCursor **********
dcl-proc FetchCursor;
dcl-pi   FetchCursor like(ReturnVar) end-pi;

dcl-s ReturnVar ind;

EXEC SQL Fetch C1 into :MyLib, :MyFile;

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-proc FetchCursor;

//********** CloseCursor **********
dcl-proc CloseCursor;
dcl-pi   CloseCursor like(ReturnVar) end-pi;

dcl-s ReturnVar ind;

EXEC SQL Close C1;

Select;
When sqlstt='00000';
     // cursor was closed, normal
     ReturnVar=*on;
Other;
     // alert the troops!
     ReturnVar=*off;
EndSl;

return ReturnVar;

end-proc CloseCursor;