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!

Rafael Victória-Pereira has more than 20 years of IBM i experience as a programmer, analyst, and manager. Over that period, he has been an active voice in the IBM i community, encouraging and helping programmers transition to ILE and free-format RPG. Rafael has written more than 100 technical articles about topics ranging from interfaces (the topic for his first book, Flexible Input, Dazzling Output with IBM i) to modern RPG and SQL in his popular RPG Academy and SQL 101 series on mcpressonline.com and in his books Evolve Your RPG Coding and SQL for IBM i: A Database Modernization Guide. Rafael writes in an easy-to-read, practical style that is highly popular with his audience of IBM technology professionals.

Rafael is the Deputy IT Director - Infrastructures and Services at the Luis Simões Group in Portugal. His areas of expertise include programming in the IBM i native languages (RPG, CL, and DB2 SQL) and in "modern" programming languages, such as Java, C#, and Python, as well as project management and consultancy.

---

MC Press books written by Rafael Victória-Pereira available now on the MC Press Bookstore.

		Evolve Your RPG Coding: Move from OPM to ILE...and Beyond Transition to modern RPG programming with this step-by-step guide through ILE and free-format RPG, SQL, and modernization techniques. List Price $79.95 Now On Sale
		Flexible Input, Dazzling Output with IBM i Uncover easier, more flexible ways to get data into your system, plus some methods for exporting and presenting the vital business data it contains. List Price $79.95 Now On Sale
		SQL for IBM i: A Database Modernization Guide Learn how to use SQL’s capabilities to modernize and enhance your IBM i database. List Price $79.95 Now On Sale

Also Read

TechTip: Prevent Unintentional Deletion of Objects with RESTRICT ON DROP

SQL 101: DDL Recap—Improving Our Basic SQL Tables, Part 2

TechTip: Automate Complex Tasks Using SQL and User-Defined Table Functions