The simplest form of dynamic SQL programs do not contain SELECT statements. In such a case, there is no resultant table and no data has to be passed back to the application program.

There are two ways to execute a NON-SELECT SQL statement dynamically; using the EXECUTE IMMEDIATE statement, or using PREPARE and EXECUTE statements.

Using EXECUTE IMMEDIATE

The EXECUTE IMMEDIATE statement has a single parameter which must be a character string which contains the dynamic SQL statement. The string has to be constructed properly by the application program. The dynamic SQL statement is then compiled and executed immediately. The compiled form of the SQL statement (the prepared statement) is discarded after execution.

 

Example:

EXEC SQL

EXECUTE IMMEDIATE :dyn_sql_statement;

 

where dyn_sql_statement is a character string containing the dynamic SQL statement.

All SQL statements which can be prepared can be used in an EXECUTE IMMEDIATE statement except for the SELECT statement.

Notes: If the string representing the dynamic SQL statement cannot be compiled, the SQLCODE will indicate this error after execution of the EXECUTE IMMEDIATE statement.

It is not possible to use host variable markers in the dynamic SQL statement when using the EXECUTE IMMEDIATE statement.

Using PREPARE and EXECUTE

The compilation and execution of the dynamic SQL statement can be split into the statements PREPARE and EXECUTE. The dynamic SQL statement is contained in a string which is constructed by the application program. The PREPARE statement initiates the compilation of the dynamic SQL statement, and the EXECUTE statement executes it.

The result of a PREPARE statement is a statement ready for execution. This prepared statement is identified by an SQL statement identifier which is either set by the user as a fixed identifier or is generated by Adabas SQL Gateway Embedded SQL when a host variable has been specified. The prepared statement is kept for later execution. If it is intended that the statement identifier is to be generated by Adabas SQL Gateway Embedded SQL, it is necessary to initialize the variable with blanks or an empty string prior to execution. Otherwise, Adabas SQL Gateway Embedded SQL will use the actual (non-blank) value of the variable. The same statement identifier must then be specified with the EXECUTE statement.

 

Example:

EXEC SQL

PREPARE statement_id FROM :dyn_sql_statement;

EXEC SQL

EXECUTE STATEMENT_ID;

where dyn_sql_statement is a character string containing the dynamic SQL statement.

All SQL statements except those mentioned earlier under Limitations can be prepared by the PREPARE statement. Only NON-SELECT statements can be executed by the EXECUTE statement.

It is possible for the dynamic SQL statement to contain host variable markers. For more information, see Using Host Variable Markers later in this section.

Note: If the string representing the dynamic SQL statement cannot be compiled, the SQLCODE will indicate this error upon return from the PREPARE statement.

Summary

A program which issues dynamic NON-SELECT statements must include the following steps:

1. Construct the dynamic SQL statement.
   The dynamic SQL statement must be constructed as a character string. The process of creating this string is application-dependent. It may be that the user enters the SQL statement or part thereof directly from a terminal, or that the application program dynamically builds the statement based on other sources of information.

2. PREPARE and EXECUTE the dynamic SQL statement.
   Either EXECUTE IMMEDIATE or PREPARE and EXECUTE can be used to execute the dynamic SQL statement. Variable input values, as specified by a host variable marker "?" may have to be provided by specifying an USING clause and specifying an SQLDA in an EXECUTE statement.

3. Check the result.
   All errors are returned to the application program using SQLCODE in the SQLCA. These errors must be handled like any other error situation.