AS/400 Links Partner with Rochester Institute Contact Us
Training Catalog Free Downloads Privacy / Usage


FREE AS/400, iSeries, and System i / iSeries Downloads
HOME >> FREE AS/400, iSeries, and System i / iSeries Downloads >> The Art of the Flexible Cursor



The Art of the Flexible Cursor
Author: Daniel Jacobs


Those of us who know and love RPG are used to the convenience of a rich assortment of file access operation codes. Operation codes such as read, reade, readp, readpe, and chain make it easy to access data so the data you retrieve exactly fits the business logic of the RPG code.

As the move towards DBU and SQL gathers strength, a question presents itself. Can fetching an SQL cursor offer the same functionality and flexibility as proprietary RPG operation codes? The answer, I am happy to say, is a resounding yes! In this article, I show you how.

To start, here is a quick review of how to bring data from a file or SQL table into an RPG program using embedded SQL. SQL queries are performed by executing an SQL Select statement. To retrieve rows of data from a Select statement, first you must declare a cursor. The SQL DECLARE statement names the cursor and associates it with an SQL SELECT statement that defines the actual query. After you declare the cursor, it is subsequently opened and then rows of data can be fetched. When you are done fetching rows of data, the cursor is closed. A simple example illustrates the technique.

C/EXEC SQL
C+          DECLARE MyCursor CURSOR FOR
C+                SELECT empno, lastnm, firstnm, salary
C+                FROM EMPLOYEE
C+                WHERE salary > :@AMOUNT
C/END-EXEC

 
Notice that an SQL statement embedded in RPG must start and end with the compiler directives /EXEC SQL and /END-EXEC. In between, C+ is used to denote an SQL continuation line. Notice also that the SQL Select statement logic is associated with the cursor name in this DECLARE statement. And finally the colon (:) before @AMOUNT indicates that @AMOUNT is a host variable (a field defined in your RPG program). Assuming that you want to read through all the rows of data returned by the Select statement, here is the rest of the code.


C/EXEC SQL
C+          OPEN MyCursor
C/END-EXEC
C                      eval SQLstt = '00000'
C                      dow  SQLstt <> '02000'

C/EXEC SQL
C+          FETCH NEXT FROM MyCursor
C+                INTO :@EmpNbr,
C+                          :@LastName,
C+                          :@FirstName,
C+                          :@Salary
C/END-EXEC
C*                business logic here, e.g. - update salary with a %10 raise
C                      eval @SALARY = @SALARY +
C                           .1 * @SALARY

C/EXEC SQL
C+          UPDATE EMPLOYEE
C+                SET salary = @SALARY
C+                WHERE CURRENT of MyCursor
C/END-EXEC

C                       enddo
C/EXEC SQL
C+          CLOSE MyCursor
C/END-EXEC

 
The SQLstt field is part of the SQL communication area, which is a data structure that is autoloaded into your program code during the SQL precompile. The SQLstt value '02000' indicates an end-of-file condition. This code reads through every row of data returned by the cursor and loads the database columns into your program host variables. After that, you can perform whatever business logic is required.
Using this kind of cursor, you can declare and fetch rows from a file in a way that is functionally similar to the READE operation. Here is one way to do it:

C/EXEC SQL
C+          DECLARE MyCursor CURSOR FOR
C+           SELECT empno, lastnm, firstnm, salary
C+           FROM EMPLOYEE
C+           WHERE state = 'NY'
C+           ORDER BY lastnm
C/END-EXEC

 
The rest of the code does not change. The Where clause in the statement above conditions the SQL Select to return the set of all and only those rows where the employees live in New York state. So this statement is equivalent to exercising a reade on a file keyed by state using a single kfld with the value of 'NY'. Notice however that as an extra bonus, you have an optional ORDER BY clause, so the return set of rows is automatically sorted by last name.

The only problem is that this type of SQL cursor can only read through the returned set of rows once. In this regard, it is sort of like an RPG primary file. You can't read backwards, and once you hit the end-of-file condition, you can't do anything else. Fortunately, cursors come in many flavors. The cursor I just showed you is an updateable serial cursor. In the next example, I declare another type of cursor: the scroll cursor. This cursor closely resembles an RPG fully procedural file. Declaring a scroll cursor is almost identical to declaring a serial cursor:

C/EXEC SQL
C+          DECLARE MyScrollCursor DYNAMIC SCROLL CURSOR FOR
C+                SELECT empno, lastnm, firstnm, salary
C+                FROM EMPLOYEE
C+                WHERE salary > :@AMOUNT
C/END-EXEC

 
Notice that instead of coding CURSOR, I coded DYNAMIC SCROLL CURSOR. That change is the only difference. All the rest of the code to read through all the rows of the selected data and place the returned columns into host variables can be the same as the cursor I called MyCursor in the first example. The dynamic scroll cursor can do much more though. The principal benefit of the scroll cursor is that it allows unlimited iteration over the data set. You can read forwards and backwards through the rows of data to your heart's content. To accomplish this feat, instead of being limited to just the FETCH NEXT statement as the serial cursor is, the dynamic scroll cursor can also FETCH PRIOR (like readp), FETCH FIRST (fetching the first row of the data set returned), and FETCH LAST (fetching the last row of data returned).

You also can use FETCH BEFORE, which positions the cursor before the first row returned so that a subsequent FETCH NEXT will retrieve the first row. Or you can use FETCH AFTER, which positions the cursor so that a FETCH PRIOR will retrieve the last row. The FETCH FIRST and FETCH LAST statements are similar to setting a file pointer in RPG with SETLL or SETGT. A FETCH CURRENT even lets you re-read the same row. A FETCH RELATIVE uses a positive or negative displacement value n to fetch the row that is n rows after or before the current row like so:

C                 eval @displacement = -3
C/EXEC SQL
C+          FETCH RELATIVE :@displacement
C+                FROM MyScrollCursor
C+                INTO :@EmpNbr,
C+                           :@LastName,
C+                           :@FirstName,
C+                           :@Salary
C/END-EXEC

 
The code above fetches the row that is 3 rows prior to the current row.
One point worth mentioning is that both the serial and scroll cursors can be updateable or not updateable. Assuming of course that the user has the proper authority and the file or table is not locked, the default is that the cursor is updateable. If your program does not need to perform an UPDATE/DELETE CURRENT of cursor_name, it is a good idea to add a line at the end of the DECLARE statement that says FOR READ ONLY. The more the SQL engine thinks your program might do with a cursor, the more resources it requires. For the same reason, if all you are planning to do is read once through the data set from start to finish, it is better not to declare a SCROLL CURSOR (cursors are serial unless you declare them otherwise). In SQL, never ask for permission to do more than you need to do.

Of course the full range of what you can do using cursors and the Select statement is beyond the scope of this article. You could fill a whole manual with tricks that are part of the complete SQL SELECT statement, such as joining tables, nested subselects, predicate functions, column functions, and on and on. But I hope at least that I have convinced you that the SQL family of cursors offers a robust set of functions that can effectively replace the proprietary RPG operation codes and satisfy all of your data access programming needs.


Daniel Jacobs is President of his own consulting firm, Lakewood DataWorks, Inc. He can be reached at
daniel@lkwdw.com