Automatically freeing a cursor

When the 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, 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.