Embedded SQL in RPG on the AS/400

Today's post is a quick introduction to using SQL embedded within an RPGIV or RPGLE program.

The example SQL:
Select PEPSN from F5542330 where PEDOCO = 1148919 and
PELNID = 1000

Note: The RPG member type must be SQLRPGLE in both cases.
Note: The user of a colon before a variable indicates it is otherwise declared within the program.
Note: The compiler optimizes nondynamic SQL statements in an SQL precompiler.

in C specs

C/EXEC SQL
C+ Select PEPSN into :PEPSN from F5542330
C+ Where PEDOCO = 1148919 and
C+ PELNID = 1000
C/END-EXEC

in /FREE specs

Exec SQL Select PEPSN into :PEPSN from F5542330
Where PEDOCO = 1148919 and PELNID = 1000;

In either case, a dynamic SQL statement can be run by creating an SQL statement within the program.

in C specs
C/EXEC SQL
C+ Declare Lines Cursor for
C+ Select PEPSN from F5542330
C+ Where PEDOCO = 1148919

C/END-EXEC
C/EXEC SQL
C+ Open Lines
C/END-EXEC

C/EXEC SQL
C+ Fetch next from Lines for 100 rows into :PSNS
C/END-EXEC

C/EXEC SQL
C+ Close Lines
C/END-EXEC

in /FREE specs
Exec SQL Declare Lines Cursor for
Select PEPSN from F5542330 Where PEDOCO =1148919

Exec SQL Open Lines

Exec SQL Fetch next from Lines
for 100 rows into :PSNS

Exec SQL Close Lines

As you can see, the total amount of lines is slightly smaller for using /FREE. The biggest benefits are the requirement to close the EXEC SQL, and that no C specs would be required, breaking up the rest of the /FREE code.

SQL is not always the right solution, but being able to make use of it adds a significant tool to your arsenal.

Regards,
Todd
tojosan - at - gmail.com