SQL for RPG Programmers

IBM i Database Overview. A database is a collection of related information, organized to facilitate processing of data to serve a specific function. The IBM i operating system is unique in the way it handles data. Unlike other systems, which require additional, costly software to provide them with database capabilities, IBM i was designed with database applications in mind, and the database is tightly integrated into the operating system.

IBM i programmers use a variety of utilities to generate and process SQL statements, but the three primary options are as follows: Interactive SQL, RUNSQLSTM (Run SQL Statements) command, IBM Data Studio.

You use Data Definition Language (DDL) statements to create new objects, such as tables, and to alter the structure or properties of existing database objects. DDL statements concern themselves with creating and defining the database objects themselves. SQL tables contain the actual data in a database. When SQL creates a new table, the database creates a single-member physical file object in a library. Tables are arranged into rows (records) and columns (fields).

You must assign a data type to each column in a table. The column’s data type determines how its values are stored, how much storage the column occupies, and what kinds of operations can be performed on the column when it is used within a program. Commonly used data types fall into three general categories: Character, Numeric, Date.

SQL views provide alternative ways of accessing the data in a table. Although a program can retrieve and process virtual rows from a view, the view does not actually contain data. Instead, it stores an access path, a sequence of pointers to the actual data in one or more tables. When SQL creates a new view, the database constructs an unkeyed logical file based on a physical file.

SQL categorizes a number of statements as Data Manipulation Language (DML). While DDL statements deal with the database objects themselves, DML statements retrieve and manage the data within the tables (physical files).

A SELECT query lets you view the data in a table, but how did those records get in the database in the first place? Use the INSERT query to create new records.

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.

When RPG executes an SQL statement, SQL returns to the program several feedback tools that you can use to diagnose the results of executing the statement. If the SQL statement ends in error, the program does not stop but continues to run with the next subsequent statement. So, it’s important to check the diagnostic response before continuing with the RPG program. The SQL Communication Area (SQLCA) is a data structure that contains return codes, subfields with valuable diagnostic information.

Embedded SQL is an excellent choice for set-at-a-time processing (i.e., treating entire groups of rows in one statement). Other SQL statements—notably, Select Into—work well for processing a single row from a table or view. But at times, a program may need to retrieve multiple rows into a result set and then process each of those rows individually. For those cases, sometimes known as row-at-a-time processing, SQL provides a mechanism called a cursor.

This ILE RPG example explains and shows the steps to use an SQL cursor in an SQLRPGLE program. Copy and use as a template for your own SQLRPGLE program.

The SQL Fetch statement retrieves a row from a multiple-row result set into host variables or a host structure (or both). This type of Fetch is called a single fetch. If the host structure is an array data structure, Fetch can perform a multiple-row fetch, retrieving several rows with one statement and placing the result set into individual elements of the array data structure.

Up to now, all the embedded SQL statements we’ve discussed have been static SQL. With static SQL, the basic structure of each SQL statement is known at the time of program compilation. The SQL statement can use host variables to substitute values at runtime, but its general purpose and construction don’t change once the program is created. Alternatively, an RPG program can build a complete SQL statement as a character string by using data in the program. The resulting dynamic SQL statement is prepared at runtime, rather than when the program is created

In addition to the Sqlcode, Sqlstate, Sqlwarn, and Sqlerrd diagnostic feedback, SQL also supports the Whenever statement to specify an action to take when an exception occurs. A Whenever statement serves as a form of monitor for an SQLRPGLE program, watching for an exception in the subsequent SQL statements and indicating the next statement to process when the exception arises.

The SQL Set Option statement establishes processing options to use during a program that uses embedded SQL.

Earlier versions of ILE RPG (prior to IBM i Release 5.4) used an alternative compiler directive for embedding SQL statements. Other than the alternative fixed format syntax, all the same concepts for embedded SQL apply to those programs.