Last time around, I introduced the concept of using an SQL cursor as a way to replace the typical RPG opcodes to access data. Now let’s go deeper and find out where cursors can really help you.

By Rafael Victória-Pereira

The previous TechTip explained what a cursor is and how to declare it. Now let’s see how to use it and, more importantly, why you should consider using it. As I wrote in the previous article, SQL is no match to RPG’s CHAIN. Since I previously showed how to declare the cursor, let’s go over the actual read operations, hidden under the FETCH instruction.

Let’s pick up where we left off on the previous TechTip: the mainCursor has been opened, and it’s ready to be used. That means you’re now ready to access the data. A results table has been created from the SELECT… part of the DECLARE statement, and the access is performed with the FETCH instruction. Think of FETCH as a kind of SELECT…INTO statement. It reads data from the cursor’s results table and places it in the variables you specify:

   Exec SQL

     FETCH FIRST FROM mainCursor

     INTO :W_ItemID, :W_ItemDesc, W_ExpDate;

Note that the example code uses all uppercase for the reserved words, but that’s purely optional—it’s just a way to clearly identify what belongs to “the system” and what belongs to “the programmer” in terms of responsibilities. Feel free to use whichever mix of capitalization you’re comfortable with.

Anyway, this last statement reads (or fetches) the first row of data from the mainCursor cursor, and puts the retrieved columns in the W_ItemID, W_ItemDesc, and W_ExpDate work variables. The use of FIRST in the fetch operation is optional, because the cursor has just been opened and the next row of data is actually the first row.

You can now navigate in your cursor, using FETCH’s navigational keywords:

• NEXT positions the cursor on the next row of the results table, relative to the current cursor position. NEXT is the default if no other cursor orientation is specified.
• PRIOR positions the cursor on the previous row of the results table, relative to the current cursor position.
• FIRST positions the cursor on the first row of the results table.
• LAST positions the cursor on the last row of the results table.
• BEFORE positions the cursor before the first row of the results table.
• AFTER positions the cursor after the last row of the results table.
• CURRENT does not reposition the cursor but maintains the current cursor position.
• RELATIVE K refers to a variable or literal integer assigned to an integer value k, where k is the number of rows before (if k is negative) or after (if k is positive) the last fetched row. If a variable is specified, it must be a numeric variable without decimal places.

Most of the time, you’ll probably use FETCH without an additional keyword. However, if you try to read before or after the limits of the cursor, things will end badly, so you need to check for “navigational errors,” just like you would in RPG with opcodes such as %EOF.

Handling Navigational Errors

Each of the SQL statements returns a status code in the SQLCOD SQL variable. Don’t worry; you don’t need to define it because the system automatically does that for you in the SQL Communications Area (SQLCA). Anyway, it’s good practice to check SQLCOD after each SQL statement, because SQL errors don’t “blow up in your face” like RPG errors do. This makes the next few lines of particular importance:

• SQLCOD = 0 means that the statement executed successfully.
• SQLCOD > 0 mean that the statement executed with warnings.
• SQLCOD = 100 is particularly important because it means that a record was not found.
• SQLCOD < 0 means an error occurred. In this case, another SQL variable named SQLSTATE contains additional information.

Typically, reading data rows from a cursor goes something similar to this:

   // Table read loop

     DoW SQLCOD = 0;

       Exec Sql FETCH mainCursor INTO :W_Some_Var,

                                     :W_Another_One;

       If SQLCOD = 0;

         // Do something with the data;

       EndIf;

     Enddo;

You just keep looping until something changes the SQLCOD value. This is equivalent to a DOW Not %EOF(<file name>) loop. Once you’re done with the cursor, you must close it using another SQL instruction:

   Exec SQL

     CLOSE mainCursor;

Four operations, DECLARE, OPEN, FETCH, and CLOSE, are all that you need to use a cursor. I’ve talked about the first two in the previous TechTip and the latter two in this one. It’s now your turn to take a long, hard look at your programs and figure out where and when to use SQL cursors to replace convoluted RPG I/O operations. Let me give you a hint, which is also a teaser for the next TechTip: Open Query File operations!