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!

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