SQL 101 is back, and it’ll help you bring your SQL code into your RPG programs.

RPG is not (and never will be) the focus of this series, but sometimes a bit of SQL can help to untangle problems and/or business rules that are too complex or too “buried” in RPG programs. That’s what we’ll be talking about over the next few TechTips.

It’s possible to embed SQL code in your RPG programs, allowing them to interact with massive quantities of data at a time, breaking the “process-a-record-at-a-time” RPG traditional approach. Don’t get me wrong—I’m not saying that you should change the way your programs interact with every single data interaction—but some of them could probably have their performance, readability, and reliability improved by SQL. SQL statements can be placed in detail calculations, in total calculations, and in RPG.

The SQL statements are executed based on the logic of the RPG statements. For instance, when you place an SQL statement inside an RPG IF statement, the SQL statement will be executed if the condition of the RPG IF statement is met. Both uppercase and lowercase letters are acceptable in SQL statements.

By now you’re eager to begin, right? First, you need to learn how to embed SQL code in your code.

How to Embed SQL Code in Fixed-Format RPG

Before you start chastising me for bringing up fixed-format RPG, remember that there are still old programs out there that can benefit from a quick-and-easy SQL fix. Here’s how it’s done.

In order for the compiler to recognize the SQL statement as such, you need to signal it appropriately, by using compiler directives that indicate where the SQL statement starts and ends. Those compiler directives are /EXEC SQL and /END SQL.

The /EXEC SQL directive must occupy positions 7 through 16 of the source statement. The SQL statement may start in position 17 and continue through position 80. The same applies to /END SQL: it must occupy positions 7 through 16 of the source statement, and you must leave the rest of the line blank.

Here’s a simple SELECT example:

C* Some RPG code here…

C/EXEC SQL SELECT      COUNT(*)

C+          FROM        InvMst

C/END-SQL

C* And some more here…

Notice the indentation? It’s not really necessary, but it improves readability. What’s really needed is the plus sign (+) in each line other than the /EXEC SQL line. You can write the whole statement on the same line (assuming it fits between positions 17 and 80), but that will hamper readability and future maintainability of the code. Anyway, the code looks simple enough, doesn’t it? Well, you’ll see that it’s even simpler in free-format.

How to Embed SQL Code in Free-Format RPG

You’re probably thinking, “This is really simple! How can it get even simpler?” As you know by now, free-format statements must end with the semicolon character (;). For embedded SQL statements, that means that you can do without the /END SQL directive. You also know that a free-format statement can span multiple lines without any special indication. For SQL statements, this means no more plus sign in each line of the statement. Finally, you can simply write EXEC SQL as if it were an ordinary RPG operation code. The only requirement is that you write the words “EXEC” and “SQL” on the same line. Putting all of that together, here’s the same simple SELECT in free-format:

EXEC SQL

      SELECT      COUNT(*)

      FROM        InvMst;

This allows you to seamlessly integrate SQL statement with your code, in a way that likens RPG to the so-called “modern programming languages.” There’s only one small flaw in these two examples: they don’t actually work! While in interactive SQL or the i Navigator, you have a way to “see” the output, that’s not the case here. You need something to communicate with the SQL statements. In other words, you need something to get data in and out, thus allowing you to perform, for instance, surgical updates or deletes over massive amounts of data. The easiest way to establish this two-way communication is by using host variables.

How to Get RPG and SQL to Talk to One Another in Your Programs

Let’s modify the sample SELECT statement from the previous example so that it passes table InvMst’s total number of rows back to the (imaginary) RPG program in which the statement is embedded.

I didn’t mention this before, but the SELECT SQL instruction has another optional clause, which only makes sense when you’re using the instruction in a programming environment. You’re not simply querying the database with a SELECT statement; instead, the SELECT statement is part of a stored procedure (which I’ll eventually explain later on in the SQL 101 TechTip series) or an embedded statement, like the case at hand. Here’s the “new” syntax:

SELECT <column 1>

      [, <column 2>, <column 3>…., <column n>]

[INTO host-variable 1, host-variable 2 …, host-variable n]

FROM  <table name>

[WHERE <condition 1> <logical operator> <condition 2>, etc]

The INTO clause directs the output of the SELECT clause to one or more host variables. These variables must be compatible with the data types of the respective columns in the SELECT clause. In other words, if column1 is a string, then host variable1 should, ideally, be a string.

It’s now time to revisit the SELECT sample and change it, so that its output is directed to host variable W_TotRegs:

EXEC SQL

      SELECT      COUNT(*)

      INTO        :W_TotReg

      FROM        InvMst;

Notice the colon character (:) before the host variable name. That’s the way to tell the compiler that the variable is not an SQL field, but a program field instead. After this code is executed, W_TotRegs is going to hold the total number of records of table InvMst.

There are other ways to use host variables to provide additional flexibility and functionality to embedded SQL, but that’s something for the next TechTip.

Until then, feel free to question, comment, and criticize in the Comments section below.

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