Using SQL Statements

The Db2 for i 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

You 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 IBM® Documentation at this web site: https://www.ibm.com/docs/i.