Embedding SQL statements in application programs

You can include SQL statements in a source program that will be submitted to the Db2 coprocessor or Db2 precompiler. Such statements are said to be embedded statements in the application program. An embedded statement can be placed anywhere in the application program where a host language statement is allowed. Each embedded statement must be preceded by a keyword (or keywords) to indicate that the statement is an SQL statement.

Tip: The Db2 coprocessor is the recommended method for processing SQL statements in application programs. Compared to the Db2 precompiler, the Db2 coprocessor has fewer restrictions on SQL programs, and more fully supports the latest SQL and programming language enhancements. See Processing SQL statements by using the Db2 coprocessor.
  • In C and COBOL, each embedded statement must be preceded by the keywords EXEC SQL.
  • In Java™, each embedded statement must be preceded by the keywords #sql.
  • In REXX, each embedded statement must be preceded by the keyword EXECSQL.

Executable statements embedded in applications

An executable statement embedded in an application program is executed every time a statement of the host language would be executed if specified in the same place. (Thus, for example, a statement within a loop is executed every time the loop is executed, and a statement within a conditional construct is executed only when the condition is satisfied.)

An embedded statement can contain references to host variables. A host variable referred to in this way can be used in one of two ways:
As input
The current value of the host variable is used in the execution of the statement.
As output
The variable is assigned a new value as a result of executing the statement.

In particular, all references to host variables in expressions and predicates are effectively replaced by current values of the variables; that is, the variables are used as input. The treatment of other references is described individually for each statement.

The successful or unsuccessful execution of the statement is indicated by setting the SQLCODE and SQLSTATE fields in the SQLCA.1 You must therefore follow all executable statements by a test of SQLCODE or SQLSTATE. Alternatively, you can use the WHENEVER statement (which is itself nonexecutable) to change the flow of control immediately after the execution of an embedded statement.

Nonexecutable statements embedded in applications

An embedded nonexecutable statement is processed only by the Db2 coprocessor or Db2 precompiler. The Db2 coprocessor or Db2 precompiler reports any errors encountered in the statement. The statement is never executed, and acts as a no-operation if placed among executable statements of the application program. Therefore, do not follow such statements with a test of an SQL return code.

1 SQLCODE and SQLSTATE cannot be in the SQLCA when the SQL processing option STDSQL(YES) is in effect. See SQL standard language.