Q. I've decided to start using embedded SQL in my RPG programs, but I have a question. When I embed a SELECT statement which would normally return more than one record from a file, the RPG program only gets a single record. Is there a way to "wrap" the SELECT statement inside a loop, so that I can process all the records in the SELECT statement?

• Declare the cursor. This step associates a name with an SQL statement, but does not execute it.
• Open the cursor. The step executes the SQL statement, making the results available for processing.
• Fetch record(s) from the cursor. This step reads the records, one at a time. You'd code this step in a loop.
• Close the cursor. This step is similar to closing a file.

Here's a short program that illustrates the process:

D Ok              C                   0

D MyData        E Ds                  Extname(Myfile)

 /Free
  // Set SQL options
  Exec Sql Set Option Datfmt=*Iso, Commit=*None, Closqlcsr=*Emdmod;

  // Declare/open SQL cursor
  Exec Sql Declare Mycursor Cursor For
                            Select *
                              From Mylib/Myfile
                              // Select statement may include other
                              // clauses, e.g., Where, Order by...
                            For Fetch Only;
  Exec Sql Open Mycursor;

  // Table read loop
  Dow Sqlcode = Ok;
    Exec Sql Fetch Mycursor Into :Mydata;
    If Sqlcode = Ok;
      Exsr Process;
    Endif;
  Enddo;

  // Close SQL cursor, end program
  Exec Sql Close Mycursor;
  *Inlr = *On;
  Return;

  // ----------------------------------------------------------------------
  //
  // Subroutine - Process - Process Mydata subfields
  Begsr Process;
    // ... Record processing goes here
  Endsr;
 /End-free

This program will read records from MYFILE using cursor MYCURSOR (there's no magic in the name...you could call the cursor almost anything). The FETCH statement reads the records, one at a time, putting the data into the MYDATA data structure.

The data structure (MYDATA in this example) must match the structure of the SELECT statement's result table. In this example, if I had selected a limited set of columnns (instead of SELECT * ), I would have adjusted the MYDATA subfield definitions to include only the selected columns.

It's important that you place the cursor declaration early in the program source; the DECLARE CURSOR statement must physically appear in the source before the program tries to process that cursor.

The source type for this program is SQLRPGLE. To compile it, use the CRTRPGSQLI (Create SQL ILE RPG Object) command.

This program reads records, but will not allow updates or deletes. To make the program update-capable, a few simple changes are necessary.

First, the cursor declaration must remove the "For Fetch Only" restriction. To be able to update all fields in the record, use the following example:

  Exec Sql Declare Mycursor Cursor For
                            Select * From Mylib/Myfile;

Or, you may restrict the updates to specific field(s):

  Exec Sql Declare Mycursor Cursor For
                            Select * From Mylib/Myfile
                            For Update of Myfield;

When your program is ready to update or delete a record, you will use a special syntax of the SQL Update or Delete statement to associate it with the currently fetched record:

  Exec Sql Update Myfile
                  Set Myfield = Newvalue
           Where Current of Mycursor;

Or:

  Exec Sql Delete From Myfile
           Where Current of Mycursor;