Using SQL Statements

The DB2® UDB for iSeries® database can be accessed from an ILE RPG program by embedding SQL statements into your program source. Use the following rules to enter your SQL statements:
  • Enter your SQL statements on the Calculation specification in free form or in fixed form
  • In free form
    • You start your SQL statement using the delimiter "EXEC SQL".
    • The SQL statement can be placed on several lines. No continuation character is required.
    • Use a semicolon to signal the end of your SQL statement.
  • In fixed form
    • Start your SQL statements using the delimiter ⁄EXEC SQL in positions 7-15 (with the ⁄ in position 7)
    • Use the continuation line delimiter (a + in position 7) to continue your statements on any subsequent lines
    • Use the ending delimiter ⁄END-EXEC in positions 7-15 (with the slash in position 7) to signal the end of your SQL statements.
  • You can start entering your SQL statements on the same line as the starting delimiter
Note: SQL statements cannot go past position 80 in your program.

Figure 1 and Figure 2 show examples of embedded SQL statements.

Figure 1. Free Form SQL Statements in an ILE RPG Program
 ...+....1....+....2....+....3....+....4....+....5....+....6....+....7..
      /FREE
          X = Y + Z; // ILE RPG calculation operations

          // The entire SQL statement is on one line
          EXEC SQL INSERT INTO MYLIB/MYFILE (FLD1) VALUES(12);

          // The SQL statement begins on the same line as
          // EXEC SQL and then it is is split across several lines
          EXEC SQL   INSERT
                        INTO MYLIB/MYFILE
                        (FLD1) VALUE(12);

          // The SQL statement begins on the line after
          // EXEC SQL and then it is is split across several lines
          EXEC SQL
              INSERT INTO MYLIB/MYFILE
                       (FLD1) VALUE(12);

          X = Y + Z; // ILE RPG calculation operations
      /END-FREE
Figure 2. Fixed Form SQL Statements in an ILE RPG Program
 ...+....1....+....2....+....3....+....4....+....5....+....6....+....7..
      * ILE RPG calculation operations
     C                   EVAL      X = Y + Z

      * The entire SQL statement is on one line
     C/EXEC SQL INSERT INTO MYLIB/MYFILE (FLD1) VALUES(12)
     C/END-EXEC

      * The SQL statement begins on the same line as
      * EXEC SQL and then it is is split across several lines
     C/EXEC SQL   INSERT
     C+             INTO MYLIB/MYFILE
     C+                 (FLD1) VALUE(12)
     C/END-EXEC

      * The SQL statement begins on the line after
      * EXEC SQL and then it is is split across several lines
     C/EXEC SQL
     C+      INSERT INTO MYLIB/MYFILE
     C+                (FLD1) VALUE(12)
     C/END-EXEC

      * ILE RPG calculation operations
     C                   EVAL      X = Y + Z
     C

Start of changeYou must enter a separate command to process the SQL statements. For more information, refer to the DB2 for i section of the Database and File Systems category in the IBM® i Information Center at this Web site - http://www.ibm.com/systems/i/infocenter/.End of change

Refer to the ADTS for AS/400: Source Entry Utility manual for information about how SEU handles SQL statement syntax checking.