You should consider inserting SQL statements into your RPG-centered applications for many reasons.

Editor's note: This article is excerpted from Programming in ILE RPG, Fifth Edition (MC Press, 2015), chapter 10, "Processing Files Using SQL."

By now, you probably realize that an RPG program depends upon several components other than the program itself to do its work and that the tools used with those components use languages other than RPG: database files, whether created with SQL or DDS; printer files created with DDS; perhaps Control Language (CL) commands to set up the environment in which the program will run; and so on. Even within the program itself, some sections can exploit other languages' capabilities and features. In recent years, it has become common practice to embed SQL statements within an RPG program to access and manage its database processing. Generally, these statements are DML statements, but they can include DDL as well as a few SQL statements specifically designed for use in a program.

These SQL statements can complement or entirely replace native file operations (Read, Write, Update, Delete, etc.). You should consider inserting SQL statements into your RPG-centered applications for many reasons. Embedded SQL is especially useful for set-at-a-time processing, enabling an RPG program to act upon many records by using a single SQL statement instead of an iterative file loop. SQL provides database features (e.g., security at the field, or column, level) that are unavailable with DDS or through native RPG file operations. SQL has a rich set of functions (e.g., Avg, Count, Sum) that RPG operations or functions cannot readily duplicate. RPG supports the dynamic creation of SQL statements at runtime, allowing a great deal of flexibility in the way your program processes the database. As an industry standard, SQL provides consistency across software platforms, pervasive training and documentation, and organized collaboration. In some cases, SQL can offer performance benefits over native operations.

10.4. Exec SQL

To insert SQL statements into an RPG program, you must use the Exec SQL directive to signal that the rest of the line is an SQL statement, not an RPG operation:

Exec SQL sql-statement;

For example, the following line in an RPG program will run the SQL Delete statement:

Exec SQL Delete From Customers Where Czip = '60606';

Exec SQL is not technically an RPG operation. It is, instead, a compiler directive—an instruction that is executed once when the program is compiled, not each time the program runs. It controls what the compiler does, not what the program will do. In this case, the Exec SQL statement signals to automatically convert the SQL statement in the program source into appropriate RPG operations before the program is created. Essentially, though, you can consider Exec SQL to be an RPG operation. Later in this chapter, we examine the steps in creating a program with embedded SQL statements.

The Exec SQL directive must be on a single line, but the SQL statement itself can span multiple lines. Just as with other RPG operations, you end the SQL statement with a semicolon (;):

Exec SQL Update Customers (Cphone, Cemail)

                         Values ('3095559753', This email address is being protected from spambots. You need JavaScript enabled to view it.')

                         Where Custno = 'AB0097532';

While you can insert most SQL statements by using Exec SQL, not all statements are allowed, and some statements require modification to work. Yet other SQL statements are allowed only as embedded statements in a program.

Tip: If a program is to process a file by using SQL statements, you need not declare that file with a Dcl-f instruction. Explicit file declaration is required only if the program processes a file by using native operations (Read, Write, etc.).

10.5. Using Host Variables

The previous SQL Update statement specifies explicit values to use in the table update. But this form is not productive if you have to write a new program, or modify a program, every time the values for Cphone and Cemail change. To make the SQL statement more flexible, you can replace the explicit values with host variables. A host variable is a data item that an SQL statement uses as the instrument to synchronize RPG program variables with data from tables and views. So, instead of coding explicit values in the example Update statement, you can substitute host variables:

Exec SQL Update Customers (Cphone, Cemail) Values (:Cphone, :Cemail)

                         Where Custno = :Custno;

The host variable name is preceded by a colon (:). The name corresponds to an RPG program variable. You need not define the host variable separately from the RPG variable. It is simply a means to allow the SQL statement to refer to an RPG variable. So, if the RPG variables Cphone and Cemail have respective values of 3095559753 and This email address is being protected from spambots. You need JavaScript enabled to view it., the end result of executing either of these two Update examples is identical. You should declare the RPG variable with the same data type and size as its associated database column.

The Insert and Delete statements can also substitute host variables for explicit column values:

Exec SQL Insert Into Customers

               (Custno, Cfname, Clname, Caddr, Czip,

                 Cphone, Cemail, Cdob, Cgender)

               Values (:Custno, :Cfname, :Clname, :Caddr, :Czip,

                 :Cphone, :Cemail, :Cdob, :Cgender);

Exec SQL Delete From Customers Where Czip = :Czip;

Host variable names cannot begin with SQ, SQL, RDI, or DSN. Those names are reserved for database use.

10.5.1. Select Into

While the Insert, Update, and Delete statements can be inserted into an RPG program and can use host variables, largely without changes, embedded SQL requires a modified form of the Select statement to retrieve a result set into host variables. The Select Into variation retrieves a single row and places the result set into RPG host variables:

Exec SQL Select columns Into :host-variables From table {Where conditions};

The Into clause lists the host variables into which the result set will be placed. The result set columns and the list of host variables share a one-to-one correspondence; that is, you must list an associated host variable, in order, for each column in the result set. The result set can have only one row. Consequently, the Where clause often refers to the table's primary key. If the result set includes more than one row, SQL returns an exception code, discussed later in this chapter.

The following example illustrates the use of Select Into:

Dcl-s Custno Char(9);

Dcl-s Cfname Char(15);

Dcl-s Clname Char(20);

…

                         // Program will provide a value for Custno.

Exec SQL Select Cfname, Clname

           Into :Cfname, :Clname

           From Customers

           Where Custno = :Custno;

                         // Cfname and Clname will contain result set values.

10.5.2. Using Host Structures

Instead of listing individual host variables in an SQL statement, you may find it useful to name a single host structure instead. A host structure is a data structure that you can use with the Into clause:

Dcl-s Custno Char(9);

Dcl-ds Custdata;

Cfname Char(15);

Clname Char(20);

End-ds;

…

                       // Program will provide a value for Custno.

Exec SQL Select Cfname, Clname

           Into :Custdata

           From Customers

           Where Custno = :Custno;

                       // Custdata subfields will contain result set values.

As was the case before, a one-to-one correspondence must exist between the result set columns and the subfields in the host structure.

A host data structure is especially useful for retrieving all the columns from a record layout. In this case, an externally described data structure is appropriate. Examine the following example:

Dcl-s Custno Char(9);

Dcl-ds Customers Ext Qualified End-ds;

…

                       // Program will provide a value for Custno.

Exec SQL Select * Into :Customers From Customers

         Where Custno = :Custno;

                     // Customers subfields will contain result set values.

Here, Customers is an externally described data structure, patterned after the record layout in the Customers file. By selecting all the columns into the :Customers host structure, this code segment has effectively accomplished the same result as the native Chain operation. The data structure is a qualified data structure. While not required, making it a qualified data structure reduces the possibility that variable names in the data structure will conflict with names elsewhere in the program. The program refers to the data structure subfields as Customers.Custno, Customers.Cfname, Customers.Clname, and so forth.

10.5.3. Handling Null Values

Recall from Chapter 3 that the Null constraint allows a column to contain a null value. A null value represents the absence of any data for a column; it is an unknown value—not a zero or blank. A null-capable column can be set to null instead of an actual value. Most IBM i tables specify Not Null for each column, forcing that column to always have a value. If, however, a column is null capable, your RPG program can detect a null value in a column retrieved via SQL.

Exec SQL Select Cfname, Clname

           Into :Cfname, :Clname

           From Customers

           Where Custno = :Custno And Cfname is Not Null;

This statement does not retrieve any row with a null Cfname value even if the Custno value matches the condition.

If you want the program to retrieve null-capable columns, you can use an indicator variable to detect whether or not a column value is null. An indicator variable is similar to a host variable, except that you use an indicator variable to detect a null value in a retrieved column or to set a column to a null value. Wherever an embedded SQL statement allows a host variable, you can optionally include an indicator variable immediately following the host variable (with no comma between the host variable and the indicator variable):

Dcl-s Custno     Char(9);

Dcl-s Cfname     Char(15);

Dcl-s Clname     Char(20);

Dcl-s NullCfname Int(5);

Dcl-s NullClname Int(5);

…

Exec SQL Select Cfname, Clname

           Into :Cfname :NullCfname,

                 :Clname :NullClname

           From Customers

           Where Custno = :Custno;

In this example, :NullCfname and :NullClname are indicator variables. Even though they are named the same, an SQL indicator variable is not the same data type as an RPG indicator variable. While RPG indicators are a true/false data type, SQL indicator variables are integers (five digits, signed). In the previous example, if :Cfname has a null value, then :NullCfname has a negative value (-1). But if :Cfname is not null, then :NullCfname is positive or zero.

To set a column to a null value, you can use the SQL Update statement, setting the indicator variable to -1:

Cfname = *Blanks

Clname = *Blanks

NullCfname = -1;

NullClname = -1;

Exec SQL Update Customers

           Set   Cfname = :Cfname :NullCfname,

                 Clname = :Clname :NullClname

           Where Custno = :Custno;

If you are retrieving the result set into a host structure, you can also organize indicator variables into an indicator structure, which is simply a data structure that uses the indicator variables as subfields:

Dcl-s Custno Char(9);

Dcl-ds Custdata;

Cfname Char(15);

Clname Char(20);

End-ds;

Dcl-ds Custnulls;

NullCfname Int(5);

NullClname Int(5);

End-ds;

…

                     // Program will provide a value for Custno.

Exec SQL Select Cfname, Clname

           Into :Custdata :Custnulls

           From Customers

           Where Custno = :Custno;

                     // Custdata subfields will contain result set values.

                     // Custnulls subfields will contain indicator variables.

The indicator structure must contain a corresponding subfield for each column in the result set even if it is not null capable.

Learn more by ordering Programming in ILE RPG, Fifth Edition from the MC Press bookstore.

Jim Buck's career in IT has spanned more than 35 years, primarily in the college education, manufacturing, and healthcare industries. Past president (13 years) of the Wisconsin Midrange Computer Professional Association, he has served on several teams developing IBM and COMMON certification tests. Jim has co-authored several IBM i textbooks with Bryan Meyers that are used by many companies and in colleges worldwide. Other accomplishments include: recipient of the 2007 IBM System i Innovation - Education Excellence Award, 2014 COMMON President's Award, and 2013/2016/2017 IBM Champion - Power Systems.

Jim is the president and founder of imPower Technologies, where he provides professional IBM i training and consulting services. He is active in the IBM i community, working to help companies train their employees in the latest IBM technologies and develop the next generation of IBM i professionals.

---

MC Press books written by Jim Buck available now on the MC Press Bookstore.

		Control Language Programming for IBM i Master the A-Z of CL, including features such as structured programming, file processing enhancements, and ILE. List Price $79.95 Now On Sale
		Mastering IBM i Get the must-have guide to the tools and concepts needed to work with today's IBM i. List Price $85.95 Now On Sale
		Programming in ILE RPG Get the definitive guide to the RPG programming language. List Price $95.95 Now On Sale
		Programming in RPG IV Understand the essentials of business programming using RPG IV. 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