Informix Guide to SQL: Syntax SQL Statements

Use the SET AUTOFREE statement to specify that the database server will free the memory allocated for a cursor automatically, as soon as the cursor is closed.

Use this statement with ESQL/C.

When the Autofree feature is enabled for a cursor, you do not need to explicitly use a FREE statement to free the cursor memory in the database server once the cursor is closed.

You can specify the ENABLED or DISABLED options for the SET AUTOFREE statement. If you do not specify either option, the default is ENABLED. The following example shows how to enable the Autofree feature for all subsequent cursors in the program by default:

EXEC SQL set autofree;

The SET AUTOFREE statement that enables the Autofree feature must appear before the OPEN statement that opens a cursor. If a cursor is already open, the SET AUTOFREE statement does not affect its behavior.

After a cursor is autofree enabled, you cannot open the cursor a second time.

If you do not specify a cursor _id or cursor_id_var, the SET AUTOFREE statement affects all subsequent cursors in the program.

The following example shows how to enable the Autofree feature for all subsequent cursors:

EXEC SQL set autofree enabled;

If you specify a cursor _id or cursor_id_var, the SET AUTOFREE statement affects only the cursor that you specify after the FOR keyword.

This option allows you to override a global setting for all cursors. For example, if you issue a SET AUTOFREE ENABLED statement for all cursors in a program, you can issue a subsequent SET AUTOFREE DISABLED FOR statement for a particular cursor.

In the following example, the first statement enables the Autofree feature for all cursors, while the second statement disables the Autofree feature for the cursor named x1:

EXEC SQL set autofree enabled;

EXEC SQL set autofree disabled for x1;

When a cursor is automatically freed, its associated prepared statement (or associated statement) is also freed.

The term associated statement has a special meaning in the context of the Autofree feature. A cursor is associated with a prepared statement if it is the first cursor that you declare with the prepared statement, or if it is the first cursor that you declare with the statement after the statement is detached.

The term detached statement has a special meaning in the context of the Autofree feature. A prepared statement is detached if you do not declare a cursor with the statement, or if the cursor with which the statement is associated was freed.

If the Autofree feature is enabled for a cursor that has an associated prepared statement, the database server frees memory allocated to the prepared statement as well as the memory allocated for the cursor. Suppose that you enable the Autofree feature for the following cursor:

/*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;

Because the sel_stmt statement is freed automatically, you cannot declare a new cursor on it unless you prepare the statement again.

A potential problem exists with cursors that have the Autofree feature enabled. In a non-ANSI-compliant database, if you do not close a cursor explicitly and then open it again, the cursor is closed implicitly. This implicit closing of the cursor triggers the Autofree feature. The second time the cursor is opened, the database server generates an error message (cursor not found) because the cursor is already freed.

Related statements: CLOSE, DECLARE, FETCH, FREE, OPEN, and PREPARE

For more information on the Autofree feature, see the Informix ESQL/C Programmer's Manual.