Informix Guide to SQL: Syntax SQL Statements

Use the SET DEFERRED_PREPARE statement to defer sending a PREPARE statement to the database server until the OPEN or EXECUTE statement is sent.

Use this statement with ESQL/C.

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.

The Deferred-Prepare feature works with the following sequences:

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

You can specify the ENABLED or DISABLED options for the SET DEFERRED_PREPARE statement. If you do not specify either option, the default is ENABLED. The following example shows how to enable the Deferred-Prepare feature by default:

EXEC SQL set deferred_prepare;

Use the ENABLED option to enable the Deferred-Prepare feature within the application. The following example shows how to use the ENABLED option:

EXEC SQL set deferred_prepare enabled;

When you enter a SET DEFERRED_PREPARE ENABLED statement in your application, the Deferred-Prepare feature is enabled for all 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 a prepared statement contains 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 you use the Deferred-Prepare feature 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 statement follows the PREPARE statement, the DESCRIBE statement results in an error.

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

EXEC SQL set deferred_prepare disabled;

When you disable the Deferred-Prepare feature, the application sends each PREPARE statement to the database server when the PREPARE statement is executed.

The following code fragment shows a SET DEFERRED PREPARE statement with a PREPARE, EXECUTE statement block. In this case, the database server executes the PREPARE and EXECUTE statements all at once.

EXEC SQL BEGIN DECLARE SECTION;

	int a;

EXEC SQL END DECLARE SECTION;

EXEC SQL allocate descriptor 'desc';

EXEC SQL create database test;

EXEC SQL create table x (a int);


/* Enable Deferred-Prepare feature */

EXEC SQL set deferred_prepare enabled;


/* Prepare an INSERT statement */

EXEC SQL prepare ins_stmt from 'insert into x values(?)';


a = 2;

EXEC SQL EXECUTE ins_stmt using :a; 

if (SQLCODE)

	printf("EXECUTE : SQLCODE is %d\n", SQLCODE);

You can use the Deferred-Prepare and Open-Fetch-Close Optimization (OPTOFC) features together in your application. The OPTOFC feature delays sending the OPEN message to the database server until the FETCH message is sent.

The following situations occur if you enable the Deferred-Prepare and OPTOFC features at the same time:

• If the text of a prepared statement contains syntax errors, the error messages are not returned to the application until the first FETCH statement is executed.
• A DESCRIBE statement cannot be executed until after the FETCH statement.
• You must issue an ALLOCATE DESCRIPTOR statement before a DESCRIBE or GET DESCRIPTOR statement can be executed.

The database server performs an internal execution of a SET DESCRIPTOR statement which sets the TYPE, LENGTH, DATA, and other fields in the system descriptor area. You can specify a GET DESCRIPTOR statement after the FETCH statement to see the data that is returned.

Related statements: DECLARE, DESCRIBE, EXECUTE, OPEN, and PREPARE

For a task-oriented discussion of the PREPARE statement and dynamic SQL, see the Informix Guide to SQL: Tutorial.

For more information about concepts relating to the SET DEFERRED_PREPARE statement, see the Informix ESQL/C Programmer's Manual.