Informix-ESQL/C Programmer's Manual

Informix-ESQL/C Programmer's Manual
Chapter 14: Using Dynamic SQL

Home Contents Index Master Index New Book

Using a Database Cursor

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 information about how to use cursors, see the Informix Guide to SQL: Tutorial.

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

Optimizing Cursor Execution

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

Sizing the Cursor Buffer

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.

Default Buffer Size

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.

Changing Size of 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.

FET_BUF_SIZE

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.

FetBufSize

sqlhdr.h

FetBufSize

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.

ODS

Automatically Freeing a Cursor

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.

Enabling the AUTOFREE Feature

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).

IFX_AUTOFREE

AUTOFREE

cursors in any thread

Execute the SQL statement, SET AUTOFREE.

SET AUTOFREE

AUTOFREE

a particular cursor

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.

Using the SET AUTOFREE Statement

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;

SET AUTOFREE

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 has not been associated with any declared cursor
If the cursor with the prepared statement has been freed but the prepared statement has 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 has been 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 syntax and use of the SET AUTOFREE statement, see the Informix Guide to SQL: Syntax.

ODS

Deferring Execution of the PREPARE Statement

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;

Restrictions on Deferred-PREPARE

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 features 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".

Enabling the Deferred-PREPARE Feature

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).

IFX_DEFERRED_PREPARE

every

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.

SET DEFERRED_PREPARE

PREPARE

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.

Using the SETDEFERRED_PREPARE Statement

From within 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++;

	}

Informix-ESQL/C Programmer's ManualChapter 14: Using Dynamic SQL Home Contents Index Master Index New Book

Using a Database Cursor

Sizing the Cursor Buffer

Default Buffer Size

Restrictions on Deferred-PREPARE

Informix-ESQL/C Programmer's Manual
Chapter 14: Using Dynamic SQL

Home Contents Index Master Index New Book