Informix-ESQL/C Programmer's Manual

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

Home Contents Index Master Index New Book

Optimizing OPEN, FETCH, and CLOSE

When an ESQL/C application uses DECLARE and OPEN statements to execute a cursor, each statement involves a round trip of message requests between the application and the database server. The optimize-OPEN-FETCH-CLOSE feature (OPTOFC) reduces the number of round trips by two, as follows:

ESQL/C saves one round trip because it does not send the OPEN statement as a separate command.

ESQL/C saves a second round trip because it does not send the CLOSE statement as a separate command.

Important: ESQL/C does not send the CLOSE statement to the database server. However, if you include the CLOSE statement, no error is generated.

ODS

Restrictions on OPTOFC

With the OPTOFC feature enabled, the following restrictions exist:

You can only use the OPTOFC feature on select cursors whose SELECT statement has been prepared. For example, the OPTOFC feature reduces network round trips for the following select cursor: /* Valid select cursor for OPTOFC optimization */
EXEC SQL prepare sel_stmt 'select * from customer';
EXEC SQL declare sel_curs cursor for sel_stmt;
The OPTOFC feature eliminates execution of the OPEN statement as a separate step. Therefore, any error conditions that opening the cursor might generate are not returned until after the initial FETCH.
Static cursors are not freed when they are closed.

With the OPTOFC feature disabled, a static cursor is freed when it is closed. When ESQL/C reaches a CLOSE statement for a static cursor, it actually sends a message to close the cursor and free memory associated with this cursor. However, dynamic cursors are not implicitly freed when they are closed.

The GET DIAGNOSTICS statement does not work for SQL statements that are delayed on the way to the database server. For example, in the following sequence of SQL statements, GET DIAGNOSTICS returns 0, indicating success, even though the OPEN is delayed until the first fetch: EXEC SQL declare curs1 ...
EXEC SQL open curs1
EXEC SQL get diagnostic
EXEC SQL fetch curs1

Enabling the OPTOFC Feature

The OPTOFC environment variable enables the OPTOFC feature. You can assign the following values to the OPTOFC environment variable.


Value	Description
`1`	This value enables the OPTOFC feature. When you specify this value, you enable the OPTOFC feature for every cursor in every thread of the application.
`0`	This value disables the OPTOFC feature for all threads of the application.

The default value of the OPTOFC 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.

On UNIX operating systems, you can set OPTOFC from within the application with the putenv() system call (as long as your system supports the putenv() function). For example, the following call to putenv() enables the OPTOFC feature:

putenv("OPTOFC=1");

In Windows environments, you can use the ifx_putenv() function.

With putenv() or ifx_putenv(), you can activate or deactivate the OPTOFC feature for each connection or within each thread. You must call the putenv() or ifx_putenv() function before you establish a connection.

Important: Informix utilities do not support the IFX_AUTOFREE, OPTOFC, and IFX_DEFERRED_PREPARE environment variables. Use these environment variables only with INFORMIX-ESQL/C client applications.

Informix-ESQL/C Programmer's Manual, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.