Using SELECT and SET in embedded SQL statements is not enough for you? Time to learn how to use SQL cursors to access data!

So far, I’ve embedded SQL to take advantage of its functions. Now let’s see how you can replace data access using SQL. Before your mind starts wondering, note that I’m not suggesting that you replace all your data access with SQL statements; for single-table, keyed-record access, SQL is usually inferior to RPG’s CHAIN operation. However, SQL is a more than fit replacement for Open Query File access or multiple-table access, typically written in RPG with a series of sequential READ/CHAIN operations in which each operation provides the key to the next.

As a quick example to clarify what I mean, remember the InvMst and ItmMst tables from earlier in this series? The ItmMst table contains the item ID and respective description, while InvMst contains the inventory records. If you wanted to list a certain group of inventory items, along with their descriptions, you’d read InvMst with a READ or CHAIN, and for each record you’d do a CHAIN to ItmMst to get the description, using InvMst’s ItemID column as a key.

This shows that, typically, your data doesn’t “live all in the same place” (i.e., you have to read multiple records from different tables to get all the necessary information). That’s why SQL cursors might help. Using a well-crafted SELECT statement, you can read all the relevant bits of information from your different tables in a single operation, instead of multiple SETLL, SETGT, READ, READP, or CHAIN operations.

I’ve also shown, in the example that demonstrated how to use two or more tables in an SQL statement, that you can perform these operations in a single step: a SELECT statement joins both tables (using an inner join) and returns the relevant columns of each of them. However, most programs are ready to treat a record at a time, not the entire set of data that a SELECT statement returns. In fact, if you try to do a SELECT …INTO embedded SQL statement that returns more than one row, the compiler is going to complain, and you won’t get far. Even if the compiler doesn’t nag you, you’re only getting the first row of data. The RPG “record at a time” philosophy requires an additional concept: the SQL cursor.

Embedding SQL Cursors in Your RPG Code

The native way to access data is often referred to as Record-Level Access (RLA), because you read a record at a time. In truth, however, you’ve been using cursors without actually realizing it. Whenever you do a READ or READP operation, you’re actually navigating forward or backward in a cursor that the system creates and manages for you. The difference is that you’re limited to the cursor’s scope—the file you’re accessing. If you want something done with the data, such as sums or averages, you need to define additional work variables and code the operations yourself. If you also program in other languages, you might know the cursors by another name, recordsets.

You can define an SQL cursor using a SELECT statement, as simple or complex as you want, and navigate it using the appropriate instructions. That’s what I’ll show later. Before jumping to the code, however, I need to explain a few concepts. A cursor is a programmer-defined access to the data, so the system can’t handle it automatically; you need to define it, open it, use it, and close it yourself.

Let’s start with the declaration. It’s a straightforward affair—just name the cursor and define the SELECT statement it’ll use to get the data:

/Free

   Exec SQL

     DECLARE mainCursor CURSOR

     FOR

     SELECT InvMst.ItemID, ItemDesc, ExpDate

     FROM   InvMst InvMst

     INNER JOIN ItmMst ItmMst ON InvMst.ItemID = ItmMst.ItemID;

Note that this statement only declares the cursor; it doesn’t actually access any data. The DECLARE instruction just creates the blueprint of the results table. In order to actually access some date, you need to open the cursor with another SQL statement:

   Exec SQL

     OPEN mainCursor;

This is somewhat similar to declaring a file as user-opened in the definitions section of your program and then, somewhere in the RPG code, issuing an OPEN operation over that file: it readies the file for I/O operation, but it doesn’t actually do any I/O. Not yet, at least. That’s what we’ll discuss in the next TechTip of this series!