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.