SET DEFERRED_PREPARE statement

Use the SET DEFERRED_PREPARE statement to control whether a client process postpones sending a PREPARE statement to the database server until the OPEN or EXECUTE statement is sent.

Only Informix® supports this statement, which is an extension to the ANSI/ISO standard for SQL. You can use this statement only with Informix® ESQL/C.

Syntax

SET DEFERRED_PREPARE { ENABLED | [ { DISABLED } ] }

Usage

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

You can specify ENABLED or DISABLED options for SET DEFERRED_PREPARE.

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.

If Deferred-Prepare is enabled in a PREPARE, DECLARE, OPEN statement block that contains a DESCRIBE statement, the DESCRIBE statement must follow the OPEN statement rather than the PREPARE statement. If the DESCRIBE follows PREPARE, the DESCRIBE statement results in an error.

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;

If you specify the DISABLED option, the application sends each PREPARE statement to the database server when the PREPARE statement is executed.