How do I iterate over a set of records in RPG with embedded SQL?

A person on StackOverflow asked a good question a while back.

How do I iterate over a set of records in RPG(LE) with embedded SQL?

This is a mix of information gleamed from many sources. I use a standard template for every program and modify as needed from there. Here is the core of the program I use for SQLRPGLE programs:

       // SQL Fields
     D sqlStatement    S           2048A   varying
     D SqlResult       DS                  qualified
      /copy modules/qcopysrc,sql_h


   //*************************************************************************
   // Internal Subprocedures
 D Init            PR
 D Main            PR
 D BuildSqlStatement...
 D                 PR
 D OpenCursor      PR             5A
 D FetchNext       PR             5A
 D CloseCursor     PR             5A
   //*************************************************************************
   // Entry Parms
 D T_RPT_SQL       PR                  extpgm('T_RPT_SQL')
 D T_RPT_SQL       PI
   //*************************************************************************
  /free
   Init();
   Main();
   *inlr = *on;
  /end-free

 P*--------------------------------------------------
 P* Procedure name: Init
 P* Purpose:
 P* Returns:
 P*--------------------------------------------------
 P Init            B
 D Init            PI
  /free
   exec sql
     set option naming = *sys, commit = *none, usrprf = *user,
                dynusrprf = *user, datfmt = *iso, closqlcsr = *endmod;
   return;
  /end-free
 P Init            E

 P*--------------------------------------------------
 P* Procedure name: Main
 P* Purpose:
 P* Returns:
 P*--------------------------------------------------
 P Main            B
 D Main            PI
  /free
   BuildSqlStatement();
   if (OpenCursor() = SQL_SUCCESS);
       dow (FetchNext() = SQL_SUCCESS);
           // *** INSERT CODE HERE ***
       enddo;
   endif;
   CloseCursor();
   return;
  /end-free 
 P Main            E

 P*--------------------------------------------------
 P* Procedure name: BuildSqlStatement
 P* Purpose:
 P* Returns:
 P*--------------------------------------------------
 P BuildSqlStatement...
 P                 B
 D BuildSqlStatement...
 D                 PI
  /free
   gSqlStatement = ' ';
   return;
  /end-free
 P BuildSqlStatement...
 P                 E

 P*--------------------------------------------------
 P* Procedure name: OpenCursor
 P* Purpose:
 P* Returns:
 P*--------------------------------------------------
 P OpenCursor      B
 D OpenCursor      PI             5A
  /free
   exec sql
     close c1;
   exec sql
     prepare sel from :sqlStatement;
   if sqlStt = SQL_SUCCESS;
     exec sql
       declare c1 cursor for sel;
     exec sql
       open c1;
   endif;
   return sqlStt;
  /end-free
 P OpenCursor      E

 P*--------------------------------------------------
 P* Procedure name: FetchNext
 P* Purpose:
 P* Returns:
 P*--------------------------------------------------
 P FetchNext       B
 D FetchNext       PI             5A
  /free
   exec sql
     fetch c1 into :SqlResult;
   return sqlstt;
  /end-free
 P FetchNext       E

 P*--------------------------------------------------
 P* Procedure name: CloseCusor
 P* Purpose:
 P* Returns:
 P*--------------------------------------------------
 P CloseCursor     B
 D CloseCursor     PI             5A
  /free
   exec sql
     close c1;
   return sqlstt;
  /end-free
 P CloseCursor     E

Let’s break this down some. First, the copybook sql_h defines some standard SQL return statuses including SQL_SUCCESS (which is 00000). My mainline has three lines of code:

  Init();
  Main();


*inlr = *on;

This is for when using RDi and WDSc outlines. I can quickly jump to any portion of the code. You can’t jump to the beginning of the mainline code that I know of in the outline.

Next, we have the BuildSqlStatement() subprocedure. I used this mostly for when I need a dynamically built SQL statement. I hope OpenCursor(), CloseCusor(), and FetchNext() are self-explanatory. One thing I want to note, I realized that SQLSTT is a global value but if you return it in the subprocedure, you can then do shorten the calling code to if (OpenCursor() = SQL_SUCCESS);.

So this is how I iterate over a set of records using SQL in RPG. Is there a better way? I am open to suggestions!

If you like this article, please up-vote my answer.