Embedded SQL Using a Cursor

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?

A. To process all the records in the SELECT statement, you’ll need to use a program entity called an SQL cursor. Think of a cursor as a means of giving a name to an SQL statement. Your program needs to go through these steps to process a cursor:

  • 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:

Dcl-c Ok 0;
Dcl-ds MyData Extname(Myfile) End-ds;
// 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;
// Close SQL cursor, end program
Exec Sql Close Mycursor;
*Inlr = *On;
// Subroutine - Process - Process Mydata subfields
Begsr Process;
// ... Record processing goes here

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 uses “fully free RPG,” which requires Release 7.1 TR11 or Release 7.2 TR3.)

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;


Exec Sql Delete From Myfile
Where Current of Mycursor;