Embedded Structured Query Language (SQL) has been available to the RPG programmer for a long time.
Editor's Note: This article is excerpted from chapter 7 of Free-Format RPG IV: Third Edition, by Jim Martin.
This chapter is intended to show you the differences between fixed-format RPG IV with embedded SQL and embedded SQL within a free-format RPG IV program. My assumption is that you already know how to code SQL statements. Only the differences between fixed- and free-format embedded SQL will be presented.
Database and Embedded SQL
SQL allows a user a great deal of flexibility when working with a database. A user can affect a large number of records within a file with very little code. The strengths of a procedural language such as RPG IV are combined with the strengths of SQL when embedding SQL statements within a program.
RPG source programs that contain embedded SQL have a member type of
SQLRPGLE rather than RPGLE. This member type causes the SQL precompiler
to be invoked. The precompiler modifies the /Exec SQL statements (mostly into API calls) prior to invoking the regular RPG IV compiler. The general rules to follow in fixed-format RPG IV are as follows:
- An SQL statement must be preceded by the precompiler directive C/Exec SQL
- The SQL statement must be followed by another precompiler directive, C/End-SQL.
- The SQL statement can begin on the same line as the C/Exec SQL line, but most programmers code the SQL statement on a separate line.
- Access to host variables is accomplished by preceding the variable name with a colon (:).
- The SQL communications area is included in every program (in a data structure) in definition specifications.
- Each source line of the SQL statement between the C/Exec SQL line and the C/End-SQL line must contain C+ in positions 6 and 7.
- The SQL statement can be entered anywhere in positions 9–80. Position 8 must be left blank.
Listing 7-1 shows an example of an SQL statement in fixed-format RPG IV.
Listing 7-1: Sample embedded SQL statement in fixed-format RPG IV
A nice feature of fixed format is the ability to prompt (using F4) and fill in values on the prompt screen.
While free format introduces some differences from fixed-format SQL, some things stay the same:
- The source member type is still SQLRPGLE.
- The SQL communications area (data structure) is included in every program, as before, in definition specifications.
- Access to host variables is also the same, achieved by preceding the variable name with a colon (:).
The following points detail the changes to embedded SQL when you are coding within a free-format section of RPG IV code:
- No C is placed in position
- The SQL precompiler directive includes no preceding forward slash (/). The statement is just Exec SQL.
- No C+ is used on subsequent lines; continuation is automatic. If a character literal must be split between lines, include a “+” symbol at the end of the first line to indicate that the literal is continued on the next line.
- The SQL statement may be entered anywhere in positions 8–80, the same as any free-format statement.
- The embedded SQL statement is ended with a semicolon (;), the same as other free-format statements. This character replaces the End-SQL statement that fixed format uses.
- Prompting is not
Listing 7-2 shows an example of an embedded SQL statement coded in free-format RPG IV. It is the same statement that was used in Listing 7-1. The indenting in this example is done manually to aid the program reader. Multiple lines are used for the same reason, and to make it easier to make changes.
Listing 7-2: Sample embedded SQL statement in free-format RPG IV