Download as DOCX, PDF, TXT or read online from Scribd
Download as docx, pdf, or txt
You are on page 1/ 6
Q.
Ive 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, youll 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. Youd code this step in a loop.
Close the cursor. This step is similar to closing a file.
Heres a short program that illustrates the process:
**Free 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; 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;
This program will read records from MYFILE using cursor MYCURSOR (theres no magic in the nameyou 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 statements 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. Its 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;
Or:
Exec Sql Delete From Myfile
Where Current of Mycursor;
Search for:SEARCH
CHANGING THE RPG PARADIGM
Now is the time to move away from RPGs columnar-based programming history to a more modern, free-format methodology. In this excellent article from IBM Systems Magazine, Jim Utsler makes the case: Changing the RPG Paradigm Copyright 2000-2016, Enskill.com. All Rights Reserved.
