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.
- 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.)
- 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.