The SET DEFERRED_PREPARE statement

The SET DEFERRED_PREPARE statement allows you to take the following actions in the program:

• Enable the Deferred-PREPARE feature:

  EXEC SQL set deferred_prepare;

  EXEC SQL set deferred_prepare enabled;

• Disable the Deferred-PREPARE feature:

  EXEC SQL set deferred_prepare disabled;

The following code fragment shows how to enable the Deferred-PREPARE feature for the ins_curs insert cursor:

EXEC SQL BEGIN DECLARE SECTION;
   int a;
EXEC SQL END DECLARE SECTION;

EXEC SQL create database test;
EXEC SQL create table table_x (col1 integer);

/*************************************
 * Enable Deferred-Prepare feature 
 *************************************/
EXEC SQL set deferred_prepare enabled;

/*************************************
 * Prepare an INSERT statement 
 *************************************/
EXEC SQL prepare ins_stmt from 
   'insert into table_x values(?)';

/*************************************
 * Declare the insert cursor for the 
 * prepared INSERT.
 *************************************/
EXEC SQL declare ins_curs cursor for ins_stmt;
/***********************************************************
 * OPEN the insert cursor. Because the Deferred-PREPARE feature 
 * is enabled, the PREPARE is executed at this time
 **********************************************************/
EXEC SQL open ins_curs;
a = 2;
while (a<100)
   {
   EXEC SQL put ins_curs from :a; 
   a++;
   }

To execute a DESCRIBE statement on a prepared statement, you must execute the DESCRIBE after the initial OPEN statement for the cursor. In the following code fragment, the first DESCRIBE statement fails because it executes before the first OPEN statement on the cursor. The second DESCRIBE statement succeeds because it follows an OPEN statement.

EXEC SQL BEGIN DECLARE SECTION;
   int a, a_type;
EXEC SQL END DECLARE SECTION;
EXEC SQL allocate descriptor 'desc';
EXEC SQL create database test;
EXEC SQL create table table_x (col1 integer);

/**********************************************
 * Enable Deferred-Prepare feature 
 ****************************************************/
EXEC SQL set deferred_prepare enabled;

/**********************************************
 * Prepare an INSERT statement 
 ****************************************************/
EXEC SQL prepare ins_stmt from 'insert into table_x values (?)';

/********************************************************************
 * The DESCRIBE results in an error, because the description of the
 * statement is not determined until after the OPEN. The OPEN is what 
 * actually sends the PREPARE statement to the database server and
 * requests a description for it.
 **********************************************************************/
EXEC SQL describe ins_stmt using sql descriptor 'desc'; /* fails */
if (SQLCODE)
   printf("DESCRIBE : SQLCODE is %d\n", SQLCODE);

/*********************************************************************
 * Now DECLARE a cursor for the PREPARE statement and OPEN it.
 **********************************************************************/
EXEC SQL declare ins_cursor cursor for ins_stmt;
EXEC SQL open ins_cursor;

/*********************************************************************
 * Now the DESCRIBE returns the information about the columns to the
 * system-descriptor area.
 **********************************************************************/
EXEC SQL describe ins_stmt using sql descriptor 'desc'; /* succeeds */
if (SQLCODE)
   printf("DESCRIBE : SQLCODE is %d\n", SQLCODE);
a = 2;
a_type = SQLINT;
while (a<100)
   {
   EXEC SQL set descriptor 'desc' values 1
      type = :a_type, data = :a;
   EXEC SQL put ins_curs using sql descriptor 'desc'; 
   a++;
   }