Embedding SQL statements in ILE RPG applications that use SQL

SQL statements coded in an ILE RPG program can be placed in the calculation section or in a free-form calculation block.

SQL statements can be placed in detail calculations, in total calculations, or in RPG subroutines. The SQL statements are run based on the logic of the RPG statements.

Both uppercase and lowercase letters are acceptable in SQL statements.

Fixed-form RPG

The keywords EXEC SQL indicate the beginning of an SQL statement. EXEC SQL must occupy positions 8 through 16 of the source statement, preceded by a / in position 7. The SQL statement may start in position 17 and continue through position 80.

The keyword END-EXEC ends the SQL statement. END-EXEC must occupy positions 8 through 16 of the source statement, preceded by a slash (/) in position 7. Positions 17 through 80 must be blank.

An UPDATE statement coded in an ILE RPG program might be coded as follows:

C/EXEC SQL UPDATE DEPARTMENT
C+           SET MANAGER = :MGRNUM
C+           WHERE DEPTNO = :INTDEP
C/END-EXEC

Free-form RPG

Each SQL statement must begin with EXEC SQL and end with a semicolon (;). The EXEC SQL keywords must be on one line. The remaining part of the SQL statement can be on more than one line. Each SQL statement should start on a new line. No other statement should be on the same line as the SQL statement.

An UPDATE statement coded in free form might be coded in the following way:

EXEC SQL UPDATE DEPARTMENT
  SET MGRNO = :MGR_NUM
  WHERE DEPTNO = :INT_DEP;