Snippets/Examples

Examples

Subprocedure skeleton:

Subprocedure skeleton

     * ${proc_comment}
    p ${proc_name}        b
    d ${proc_name}        pi            10i 0
    d  ${parm_name}                      1a
    
    d rc                            10i 0 inz
     
    c/free
       return rc;
     /end-free
    p                 e

EXEC SQL

ref [1]

    C/EXEC SQL
    C+ SELECT * FROM mylib/myfile
    C/END-EXEC
free
 exec sql select * from mylib/myfile;

Open Cursor and Fetch Mainline

ref [2]

    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

Open Cursor Procedure

ref [3]

    P OpenCursor      B
    D OpenCursor      PI                  like(ReturnVar)
    D ReturnVar       s               n
      // The immediately following /EXEC SQL is SQL's version of RPG's H Spec
      // It is never executed.  Just used at compile time.
    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

Fetch Cursor Procedure

ref [4]

    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

Close Cursor Procedure

ref [5]

    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 

#top