9. Embedded SQL – ILE RPG in Easy Bytes

Back to: ILE RPG in Easy Bytes

ILE RPG in Easy Bytes

Unit 9 discusses the use of SQL statements embedded into an ILE RPG program, as well as how and when to use embedded SQL as a complement to native RPG file operations. It also introduces SQL’s Data Manipulation Language and demonstrates how to coordinate retrieved SQL data with RPG program variables. In addition, you learn how to handle return codes from SQL statements, and how to build dynamic SQL statements as the program is executed. Finally, the unit examines the SQL RPG precompiler and its role in creating RPG programs that use SQL.

This course is a unit of the ILE RPG in Easy Bytes  series, a complete introductory ILE RPG programming self-guided tutorial.

If you are already familiar with free format ILE RPG, but need more details on SQL, including embedded SQL, you may find the more detailed SQL for RPG Programmers course appropriate to your needs.

 

One-Time Payment

$19.95
 
Enroll
 

Members Only

FREE
MEMBER PRICING

Instead of paying for each unit individually, you may purchase a membership which will automatically enroll you at a discount in the entire “ILE RPG in Easy Bytes” series.

 
Purchase Membership

SQL Data Manipulation Language

SQL Data Manipulation Language

SQL also 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). The DML statements appropriate for embedded SQL are as follows:
Introduction to Embedded SQL

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.
Handling SQL Return Codes

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.
Using SQL Cursors

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.
Building Dynamic SQL Statements

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.

Creating SQLRPGLE Programs

SQL Environment Options

The Set Option statement takes the following form: Exec SQL Set Option option = value, …; If you use it in a program, the Set Option statement must be coded before any other SQL statements (i.e.
Creating SQLRPGLE Programs

Unlike previous programs, however, which used a member type of RPGLE, if the program includes SQL statements, the member type should be SQLRPGLE. Once the source member contains all the required code, you use a different compiler command, CRTSQLRPGI (Create SQL ILE RPG Object), to compile the source and create the program (or module or service program, discussed later units). The CRTSQLRPGI command employs a precompiler to translate the SQL portions of the source code before actually compiling it.
SQLRPGLE Example Programs

To recall, the company wants to give selected employees a 5 percent raise. A transaction file called Raisetrans contains the IDs of these employees (in field Empid). **Free // ********************************************************************* // This program updates employees’ pay by 5% based on employee IDs // contained in table RaiseTrans.
Legacy Code (SQL)

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. The alternative syntax follows this form: