INFORMIX-ESQL/C Programmer's Manual Using Dynamic SQL

A database cursor is an identifier associated with a group of rows. It is, in a sense, a pointer to the current row in a buffer. You must use a cursor in the following cases:

• Statements that return more than one row of data from the database server:
• A SELECT statement requires a select cursor.
• An EXECUTE FUNCTION statement requires a function cursor.
• An INSERT statement that sends more than one row of data to the database server requires an insert cursor.

The following sections summarize how to use cursors in an ESQL/C application. For more information about how to use cursors, see the Informix Guide to SQL: Tutorial.

Statements that return one row of data include a singleton SELECT and an EXECUTE FUNCTION statement whose user-defined function returns only one row of data. Statements that can return more than one row of data include:

• a non-singleton SELECT.

When a SELECT statement returns more than one row, define a select cursor with the DECLARE statement.

• an EXECUTE FUNCTION statement whose user-defined function returns more than one row.

When an EXECUTE FUNCTION statement executes a user-defined function that returns more than one row, define a function cursor with the DECLARE statement.

For the select or function cursor, you can use a sequential, scroll, hold, or update cursor. Figure 14-5 summarizes the SQL statements that manage a select or function cursor.

For more information on any of these statements, see their entries in the Informix Guide to SQL: Syntax. You can change the size of the select or fetch buffer with the Fetch-Buffer-Size feature. For more information, see Sizing the Cursor Buffer.

A select cursor enables you to scan multiple rows of data that a SELECT statement returns. The DECLARE statement associates the SELECT statement with the select cursor. In the DECLARE statement, the SELECT statement can be in either of the following formats:

• A literal SELECT statement in the DECLARE statement

The following DECLARE statement associates a literal SELECT statement with the slct1_curs cursor:

EXEC SQL declare slct1_curs for select * from customer;

• A prepared SELECT statement in the DECLARE statement

The following DECLARE statement associates a prepared SELECT statement with the slct2_curs cursor:

EXEC SQL prepare slct_stmt from
'select * from customer';
EXEC SQL declare slct2_curs for slct_stmt;

If the SELECT returns only one row, it is called a singleton SELECT and it does not require a select cursor to execute.

A function cursor enables you to scan multiple rows of data that the user-defined function returns. The following user-defined functions can return more than one row:

• An SPL function that contains the WITH RESUME keywords in its RETURN statement

For information on how to write this type of SPL function, see the chapter on SPL in the Informix Guide to SQL: Tutorial.

• An external function that is an iterator function

For information on how to write an iterator function, see the DataBlade API Programmer's Manual.

You execute a user-defined function with the EXECUTE FUNCTION statement. The DECLARE statement associates the EXECUTE FUNCTION with the function cursor. In the DECLARE statement, the EXECUTE FUNCTION statement can be in either of the following formats:

• A literal EXECUTE FUNCTION statement in the DECLARE statement

The following DECLARE statement associates a literal EXECUTE FUNCTION statement with the func1_curs cursor:

EXEC SQL declare func1_curs for execute function
func1();

• A prepared EXECUTE FUNCTION statement in the DECLARE statement

The following DECLARE statement associates a prepared EXECUTE FUNCTION statement with the func2_curs cursor:

EXEC SQL prepare func_stmt from
'execute function func1()';
EXEC SQL declare func2_curs for func_stmt;

If the external or SPL function returns only one row, it does not require a function cursor to execute.

When you execute the INSERT statement, the statement sends one row of data to the database server. When an INSERT statement sends more than one row, define an insert cursor with the DECLARE statement. An insert cursor enables you to buffer multiple rows of data for insertion at one time. The DECLARE statement associates the INSERT statement with the insert cursor. In the DECLARE statement, the INSERT statement can be in either of the following formats:

• A literal INSERT statement in the DECLARE statement

The following DECLARE statement associates a literal INSERT statement with the ins1_curs cursor:

EXEC SQL declare ins1_curs for
insert into customer values (?);

• A prepared INSERT statement in the DECLARE statement

The following DECLARE statement associates a prepared INSERT statement with the ins2_curs cursor:

EXEC SQL prepare ins_stmt from
'insert into customer values (?)';
EXEC SQL declare ins2_curs for ins_stmt;

If you use an insert cursor it can be much more efficient than if you insert rows one at a time, because the application process does not need to send new rows to the database as often. You can use a sequential or hold cursor for the insert cursor. Figure 14-6 summarizes the SQL statements that manage an insert cursor.

For more information on any of these statements, see their entries in the Informix Guide to SQL: Syntax. You can change the size of the insert buffer with the Fetch-Buffer-Size feature. For more information, see Sizing the Cursor Buffer.

In an ESQL/C program, you can specify a cursor name with any of the following items:

• A literal name must follow the rules for identifier names. See the Identifier segment in the Informix Guide to SQL: Syntax.
• A delimited identifier is an identifier name that contains characters that do not conform to identifier-naming rules. For more information, see SQL Identifiers.
• A dynamic cursor is a character host variable that contains the name of the cursor. This type of cursor specification means that the cursor name is specified dynamically by the value of the host variable. You can use a dynamic cursor in any SQL statement that allows a cursor name except the WHERE CURRENT OF clause of the DELETE or UPDATE statement.

Dynamic cursors are particularly useful to create generic functions to perform cursor-management tasks. You can pass in the name of the cursor as an argument to the function. If the cursor name is to be used in an ESQL/C statement within the function, make sure you declare the argument as a host variable with the PARAMETER keyword. Figure 14-7 on page 14-19 shows a generic cursor deallocation function called remove_curs().

void remove_curs(cursname) EXEC SQL BEGIN DECLARE SECTION; PARAMETER char *cursname; EXEC SQL END DECLARE SECTION; { EXEC SQL close :cursname; EXEC SQL free :cursname; }

Figure 14-7 A Sample ESQL/C Function That Uses a Dynamic Cursor

INFORMIX-ESQL/C supports the following features that allow you to minimize network traffic when an ESQL/C application fetches rows from a database server:

• Change the size of the fetch and insert buffers
• Automatically free the cursor
• Defer the PREPARE statement until the OPEN statement

The cursor buffer is the buffer that an ESQL/C application uses to hold the data (except simple large-object data) in a cursor. ESQL/C has the following uses for the cursor buffer:

• The fetch buffer holds data from a select or function cursor.

When the database server returns rows from the active set of a query, ESQL/C stores these rows in the fetch buffer.

• The insert buffer holds data for an insert cursor.

ESQL/C stores the rows to be inserted in the insert buffer then sends this buffer as a whole to the database server for insertion.

With a fetch buffer, the client application performs the following tasks:

1. Sends the size of the buffer to the database server and requests rows when it executes the first FETCH statement.

The database server sends as many rows as will fit in the fetch buffer to the application.

2. Retrieves the rows from the database server and puts them in the fetch buffer.
3. Takes the first row out of the fetch buffer and puts the data in the host variables that the user has provided.

For subsequent FETCH statements, the application checks whether more rows exist in the fetch buffer. If they do, it takes the next row out of the fetch buffer. If no more rows are in the fetch buffer, the application requests more rows from the database server, sending the fetch-buffer size.

The client application uses an insert buffer to perform the following tasks:

1. Put the data from the first PUT statement into the insert buffer.
2. Check whether more room exists in the insert buffer for subsequent PUT statements.

If more rows can fit, the application puts the next row into the insert buffer. If no more rows can fit into the insert buffer, the application sends the contents of the insert buffer to the database server.

The application continues this procedure until no more rows are put into the insert buffer. It sends the contents of the insert buffer to the database server when:

• the insert buffer is full.
• it executes the FLUSH statement on the insert cursor.
• it executes the CLOSE statement on the insert cursor.

The client application sends the prepared statement that is associated with the cursor to the database server and requests DESCRIBE information about the statement. If the cursor has an associated prepared statement, ESQL/C makes this request when the PREPARE statement executes. If the cursor does not have an associated statement, ESQL/C makes the request when the DECLARE statement executes.

When it receives this request, the database server sends the DESCRIBE information about each column in the projection list to the application. With this information, ESQL/C can determine the size of a row of data. By default, ESQL/C sizes this cursor buffer to hold one row of data. It uses the following algorithm to determine the default size of the cursor buffer:

1. If one row fits in a 4096-byte buffer, the default buffer size is 4096 bytes (4 kilobytes).
2. If the size of one row exceeds 4096 bytes, the default buffer size is the size of that row.

Once it has the buffer size, ESQL/C allocates the cursor buffer.

An ESQL/C application might want to increase the size of the cursor buffer to increase the number of rows that can be passed at one time between the client application and the database server. When you increase the number of rows that can be passed, you reduce the number of round-trip message requests that need to be sent between the client application and the database server. The bigger the cursor buffer, the more data that can be sent in one round trip.

To increase the size of the cursor buffer, you can set either of the following variables in the client environment:

• At runtime, you can set the FET_BUF_SIZE environment variable to determine the size of the cursor buffer for all select, fetch, and insert cursors in all client applications.

For example, the following command sets the FET_BUF_SIZE environment variable in the C-shell environment to 20,000 bytes (20 kilobytes):

setenv FET_BUF_SIZE 20000

• At compile time, you can set the FetBufSize global variable in an ESQL/C application to set the size of the cursor buffer from cursor to cursor in a particular client application.

The FetBufSize global variable is defined in the ESQL/C sqlhdr.h header file. (The ESQL/C processor automatically includes the sqlhdr.h header file in all ESQL/C programs.) You set the size of the cursor buffer at the point that you make the assignment to the FetBufSize variable. This new size takes effect the next time ESQL/C checks the buffer size. ESQL/C checks the size of the cursor buffer when it allocates the cursor buffer. ESQL/C also checks the size of a fetch buffer when it tells the database server how many rows to send at one time.

The global variable overrides any value in the FET_BUF_SIZE environment variable. The following C code sets FetBufSize to 30,000 bytes (30 kilobytes):

FetBufSize = 30000;

Tip: Contrary to what the names might imply, the FET_BUF_SIZE environment variable and the FetBufSize global ESQL/C variable do not change the size of only the fetch buffer. They change the size of the cursor buffer, which is used for both the fetch buffer and the insert buffer.

In a client-server environment, you must set the cursor buffer size on the client side of the application because this buffer resides in the application process. Specify the size of the cursor buffer in bytes, up to the maximum size of a small integer (short data type) on your system. For most 32-bit platforms, this maximum is 32,767 bytes. If you specify a buffer size that is less than the default size or greater than the system maximum, the new size is ignored. If you do not specify a buffer size, the database server uses the default size.

Important: Informix extends ANSI-standard syntax to set the size of the cursor buffer.

If you increase the size of the cursor buffer, you reduce the overhead of refilling the buffer. The database server can buffer more data before it sends it to the application. A larger fetch buffer can enhance performance when you fetch a large number of rows or when a single row is large. The greater the size of the fetch buffer, the fewer number of times the application needs to wait while the database server retrieves a large number of rows or a few large rows. However, when you fetch only a few rows (unless the row is very large), the performance gain is not as apparent.

When an ESQL/C application uses a cursor, it usually sends a FREE statement to the database server to deallocate memory assigned to a select cursor once it no longer needs that cursor. Execution of this statement involves a round trip of message requests between the application and the database server. The Automatic-FREE feature (AUTOFREE) reduces the number of round trips by one.

When the AUTOFREE feature is enabled, ESQL/C saves a round trip of message requests because it does not need to execute the FREE statement. When the database server closes a select cursor, it automatically frees the memory that it has allocated for it. Suppose you enable the AUTOFREE feature for the following select cursor:

/* Select cursor associated with a SELECT statement */

EXEC SQL declare sel_curs cursor for

	select * from customer;

When the database server closes the sel_curs cursor, it automatically performs the equivalent of the following FREE statement:

FREE sel_curs

If the cursor had an associated prepared statement, the database server also frees memory allocated to the prepared statement. Suppose you enable the AUTOFREE feature for the following select cursor:

/* Select cursor associated with a prepared statement */

EXEC SQL prepare sel_stmt 'select * from customer';

EXEC SQL declare sel_curs2 cursor for sel_stmt;

When the database server closes the sel_curs2 cursor, it automatically performs the equivalent of the following FREE statements:

FREE sel_curs2;

FREE sel_stmt

You must enable the AUTOFREE feature before you open or reopen the cursor.

You can enable the AUTOFREE feature for an ESQL/C application in either of the following ways:

• Set the IFX_AUTOFREE environment variable to 1 (one).

When you use the IFX_AUTOFREE environment variable to enable the AUTOFREE feature, you automatically free cursor memory when cursors in any thread of the program are closed.

• Execute the SQL statement, SET AUTOFREE.

With the SET AUTOFREE statement, you can enable the AUTOFREE feature for a particular cursor. You can also enable or disable the feature in a particular connection or thread.

Warning: Be careful when you enable the AUTOFREE feature in legacy ESQL/C applications. If a legacy application uses the same cursor twice, it generates an error when it tries to open the cursor for the second time. When the AUTOFREE feature is enabled, the database server automatically frees the cursor when it closes it. Therefore, the cursor does not exist when the legacy application attempts to open it a second time, even though the application does not explicitly execute the FREE statement.

For more information on the syntax and use of the SET AUTOFREE statement, see Using the SET AUTOFREE Statement. For more information on the IFX_AUTOFREE environment variable, see the Informix Guide to SQL: Reference.

You can use the SQL statement, SET AUTOFREE, to enable and disable the AUTOFREE feature. The SET AUTOFREE statement allows you to take the following actions in an ESQL/C program:

• Enable the AUTOFREE feature for all cursors:
EXEC SQL set autofree;
EXEC SQL set autofree enabled;

These statements are equivalent because the default action of the SET AUTOFREE statement is to enable all cursors.

• Disable the AUTOFREE feature for all cursors:
EXEC SQL set autofree disabled;
• Enable the AUTOFREE feature for a specified cursor identifier or cursor variable:
EXEC SQL set autofree for cursor_id;
EXEC SQL set autofree for :cursor_var;

The SET AUTOFREE statement overrides any value of the IFX_AUTOFREE environment variable.

The following code fragment uses the FOR clause of the SET AUTOFREE statement to enable the AUTOFREE feature for the curs1 cursor only. After the database server executes the CLOSE statement for curs1, it automatically frees the cursor and the prepared statement. The curs2 cursor and its prepared statement are not automatically freed.

EXEC SQL BEGIN DECLARE SECTION;

	int a_value;

EXEC SQL END DECLARE SECTION;



EXEC SQL create database tst_autofree;

EXEC SQL connect to 'tst_autofree';

EXEC SQL create table tab1 (a_col int);

EXEC SQL insert into tab1 values (1);



/* Declare the curs1 cursor for the slct1 prepared 

  * statement */

EXEC SQL prepare slct1 from 'select a_col from tab1';

EXEC SQL declare curs1 cursor for slct1;



/* Enable AUTOFREE for cursor curs1 */

EXEC SQL set autofree for curs1;


/* Open the curs1 cursor and fetch the contents */

EXEC SQL open curs1;

while (SQLCODE == 0)

	{

	EXEC SQL fetch curs1 into :a_value;

	printf("Value is: %d\n", a_value);

	}



/* Once the CLOSE completes, the curs1 cursor is freed and

  * cannot be used again. */

EXEC SQL close curs1;



/* Declare the curs2 cursor for the slct2 prepared 

  * statement */

EXEC SQL prepare slct2 from 'select a_col from tab1';

EXEC SQL declare curs2 cursor for slct2;



/* Open the curs2 cursor and fetch the contents */

EXEC SQL open curs2;

while (SQLCODE == 0)

	{

	EXEC SQL fetch curs2 into :a_value;

	printf("Value is: %d\n", a_value);

	}



/* Once this CLOSE completes, the curs2 cursor is still

  * available for use. It has not been automatically freed. */

EXEC SQL close curs2;



/* You must explicitly free the curs2 cursor and slct2

  * prepared statement.  */

EXEC SQL free curs2;

EXEC SQL free slct2;

When you use the AUTOFREE feature, make sure you do not cause a prepared statement to become detached. This situation can occur if you declare more than one cursor on the same prepared statement. A prepared statement is associated or attached to the first cursor that specifies it in a DECLARE statement. If the AUTOFREE feature is enabled for this cursor, then the database server frees the cursor and its associated prepared statement when it executes the CLOSE statement on the cursor.

A prepared statement becomes detached when either of the following events occur:

• If the prepared statement was not associated with any declared cursor
• If the cursor with the prepared statement was freed but the prepared statement was not.

This second condition can occur if the AUTOFREE feature is not enabled for a cursor and you free only the cursor, not the prepared statement. The prepared statement becomes detached. To reattach the prepared statement, declare a new cursor for the prepared statement. Once a prepared statement was freed, it cannot be used to declare any new cursor.

The following code fragment declares the following cursors on the slct1 prepared statement:

• The curs1 cursor, with which the slct1 prepared statement is first associated
• The curs2 cursor, which executes slct1 but with which slct1 is not associated
• The curs3 cursor, with which slct1 is associated

The following code fragment shows how a detached prepared statement can occur:

/******************************************************************
* Declare curs1 and curs2. The slct1 prepared statement is *
* associated curs1 because curs1 is declared first. */

EXEC SQL prepare slct1 'select a_col from tab1';

EXEC SQL declare curs1 cursor for slct1;

EXEC SQL declare curs2 cursor for slct1;

/******************************************************************
* Enable the AUTOFREE feature for curs2 */

EXEC SQL set autofree for curs2;

/*****************************************************************
* Open the curs1 cursor and fetch the contents */

EXEC SQL open curs1;
{
EXEC SQL fetch curs1 into :a_value;
printf("Value is: %d\n", a_value);
}

EXEC SQL close curs1;

/* Because AUTOFREE is enabled only for the curs2 cursor, this *
* CLOSE statement frees neither the curs1 cursor nor the slct1 *
* prepared statement. The curs1 cursor is still defined so the *
* slct1 prepared statement does not become detached. *

*************************************************************************/

/*****************************************************************
* Open the curs2 cursor and fetch the contents */

EXEC SQL open curs2;

while (SQLCODE == 0)
{
EXEC SQL fetch curs2 into :a_value;
printf("Value is: %d\n", a_value);
}

EXEC SQL close curs2;

/* This CLOSE statement frees the curs2 cursor but does not free *
* slct1 prepared statement because the prepared statement is not*
* associated with curs2. *
*************************************************************************/

/*****************************************************************
* Reopen the curs1 cursor. This open is possible because the *
* AUTOFREE feature has not been enabled on curs1. Therefore, the*
* database server did not automatically free curs1 when it closed it. */

EXEC SQL open curs1;

while (SQLCODE == 0)
{

EXEC SQL fetch curs1 into :a_value;

printf("Value is: %d\n", a_value);

}

EXEC SQL close curs1;

EXEC SQL free curs1;

/* Explicitly freeing the curs1 cursor, with which the slct1 *
* statement is associated, causes slct1 to become detached. It *
* is no longer associated with a cursor. * *
*************************************************************************/

/***************************************************************
* This DECLARE statement causes the slct1 prepared statement *
* to become reassociated with a cursor. Therefore, the slct1 *
* statement is no longer detached. */

EXEC SQL declare curs3 cursor for slct1;

EXEC SQL open curs3;

/* Enable the AUTOFREE feature for curs */

EXEC SQL set autofree for curs3;

/* Open the curs3 cursor and fetch the content */

EXEC SQL open curs3;

while (SQLCODE == 0)
{

EXEC SQL fetch curs3 into :a_value;

printf("Value is: %d\n", a_value);

}

EXEC SQL close curs3;

/* Because AUTOFREE is enabled for the curs3 cursor, this CLOSE*
* statement frees the curs3 cursor and the slct1 PREPARE stmt.*
***********************************************************************/

/***************************************************************
* This DECLARE statement would generate a run time error *
* because the slct1 prepared statement has been freed. */

EXEC SQL declare x4 cursor for slct1;

/***********************************************************************/

For more information on the ../sqls/intro.htmltax and use of the SET AUTOFREE statement, see the Informix Guide to SQL: Syntax.

When an ESQL/C application uses a PREPARE/DECLARE/OPEN statement block to execute a cursor, each statement involves a round trip of message requests between the application and the database server. The Deferred-PREPARE feature reduces the number of round trips by one. When the Deferred-PREPARE feature is enabled, ESQL/C saves a round trip of message requests because it does not need to send a separate command to execute the PREPARE statement. Instead, the database server automatically executes the PREPARE statement when it receives the OPEN statement.

Suppose you enable the Deferred-PREPARE feature for the following select cursor:

/* Select cursor associated with a SELECT statement */

EXEC SQL prepare slct_stmt FOR

	'select * from customer';

EXEC SQL declare sel_curs cursor for slct_stmt;

EXEC SQL open sel_curs;

The ESQL/C application does not send the PREPARE statement to the database server when it encounters the PREPARE before the DECLARE statement. Instead, it sends the PREPARE and the OPEN to the database server together when it executes the OPEN statement.

You can use the Deferred-PREPARE feature in ESQL/C applications that contain dynamic SQL statements that use statement blocks of PREPARE, DECLARE, and OPEN to execute the following statements:

• SELECT statements (select cursors)
• EXECUTE FUNCTION statements (function cursors)
• INSERT statement (insert cursors)

For example, the Deferred-PREPARE feature reduces network round trips for the following select cursor:

/* Valid select cursor for Deferred-PREPARE optimization */

EXEC SQL prepare sel_stmt 'select * from customer';

EXEC SQL declare sel_curs cursor for sel_stmt;

EXEC SQL open sel_curs;

When you enable the deferred-PREPARE feature, the client application does not send PREPARE statements to the database server when it encounters them. The database server receives a description of the prepared statement when it executes the OPEN statement.

The database server generates an error if you execute a DESCRIBE statement on a prepared statement before the first OPEN of the cursor. The error occurs because the database server has not executed the PREPARE statement that the DESCRIBE statement specifies. When the deferred-PREPARE feature is enabled, you must execute the DESCRIBE statement after the first OPEN of a cursor.

Important: The deferred-PREPARE feature eliminates execution of the PREPARE statement as a separate step. Therefore, the application does not receive any error conditions that might exist in the prepared statement until after the initial OPEN.

For more information, see Using the SETDEFERRED_PREPARE Statement.

You can enable the Deferred-PREPARE feature for an ESQL/C application in either of the following ways:

• Set the IFX_DEFERRED_PREPARE environment variable to 1 (one).

When you use the IFX_DEFERRED_PREPARE environment variable to enable the Deferred-PREPARE feature, you automatically defer execution of the PREPARE statement until just before the OPEN statement executes for every PREPARE statement in any thread of the application.

The default value of the IFX_DEFERRED_PREPARE environment variable is 0 (zero). If you set this environment variable from the shell, make sure you set it before you start the ESQL/C application.

• Execute the SQL statement, SET DEFERRED_PREPARE.

With the SET DEFERRED_PREPARE statement, you can enable the Deferred-PREPARE feature for a particular PREPARE statement. You can also enable or disable the feature in a particular connection or thread.

For more information on the syntax and use of the SET DEFERRED PREPARE statement, see "Using the SETDEFERRED_PREPARE Statement." For more information on the IFX_DEFERRED_PREPARE environment variable, see the Informix Guide to SQL: Reference.

In an ESQL/C application you can use the SQL statement, SET DEFERRED_PREPARE, to enable and disable the Deferred-PREPARE feature. The SET DEFERRED_PREPARE statement allows you to take the following actions in an ESQL/C 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 SET DEFERRED_PREPARE statement overrides any value of the IFX_DEFERRED_PREPARE environment variable.

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++;
}