SET DEFERRED_PREPARE statement

By default, the SET DEFERRED_PREPARE statement causes the application program to delay sending the PREPARE statement to the database server until the OPEN or EXECUTE statement is executed. In effect, the PREPARE statement is bundled with the other statement so that one round-trip of messages, instead of two, is sent between the client and the server. This Deferred-Prepare feature can affect the following series of Dynamic SQL statement:

• PREPARE, DECLARE, OPEN statement blocks that operate with the FETCH or PUT statements
• PREPARE followed by the EXECUTE or EXECUTE IMMEDIATE statement

If you specify no option, the default is ENABLED. The following example enables the Deferred-Prepare feature by default:

EXEC SQL set deferred_prepare;

The ENABLED option enables the Deferred-Prepare feature within the application. The following example explicitly specifies the ENABLED option:

EXEC SQL set deferred_prepare enabled;

After an application issues SET DEFERRED_PREPARE ENABLED, the Deferred-Prepare feature is enabled for subsequent PREPARE statements in the application. The application then exhibits the following behavior:

• The sequence PREPARE, DECLARE, OPEN sends the PREPARE statement to the database server with the OPEN statement. If the prepared statement has syntax errors, the database server does not return error messages to the application until the application declares a cursor for the prepared statement and opens the cursor.
• The sequence PREPARE, EXECUTE sends the PREPARE statement to the database server with the EXECUTE statement. If a prepared statement contains syntax errors, the database server does not return error messages to the application until the application attempts to execute the prepared statement.

Use the DISABLED option to disable the Deferred-Prepare feature within the application. The following example specifies the DISABLED option:

EXEC SQL set deferred_prepare disabled;