FREE statement

Use the FREE statement to release resources that are allocated to a prepared statement or to a cursor.

Syntax

(1)
Notes:
  • 1 ESQL/C only
Element Description Restrictions Syntax
cursor_id Name of a cursor Must have been declared Identifier
cursor_id_var Host variable that holds the value of cursor_id Must be a character data type Language specific
statement_id Identifier of a prepared SQL statement Must be defined in a previous PREPARE statement PREPARE statement
statement_id_var Host variable that stores the name of a prepared object Must be declared as a character data type. PREPARE statement

Usage

Use this statement, which is an extension to the ANSI/ISO standard for SQL, with or with SPL.

FREE releases the resources that the database server and (for ESQL/C) the application-development tool allocated for a prepared statement or for a cursor.

If you declared a cursor for a prepared statement, FREE statement_id (or statement_id_var) releases only the resources in the application development tool; the cursor can still be used. The resources in the database server are released only when you free the cursor.

If you prepared a statement (but did not declare a cursor for it), FREE statement_id (or FREE statement_id_var) releases the resources in both the application development tool and the database server.

After you free a statement, you cannot execute it or declare a cursor for it until you prepare it again.

The following example shows the sequence of statements that is used to free an implicitly prepared statement: 
EXEC SQL prepare sel_stmt from 'select * from orders';
...
EXEC SQL free sel_stmt;
The following example shows the sequence of statements that are used to release the resources of an explicitly prepared statement. The first FREE statement in this example frees the cursor. The second FREE statement in this example frees the prepared statement. 
sprintf(demoselect, "%s %s",
   "select * from customer ",
   "where customer_num between 100 and 200");
EXEC SQL prepare sel_stmt from :demoselect;
EXEC SQL declare sel_curs cursor for sel_stmt;
EXEC SQL open sel_curs;
...
EXEC SQL close sel_curs;
EXEC SQL free sel_curs;
EXEC SQL free sel_stmt;

If you declared a cursor for a prepared statement, freeing the cursor releases only the resources in the database server. To release the resources for the statement in the application-development tool, use FREE statement_id (or FREE statement_id_var). If a cursor is not declared for a prepared statement, freeing it releases the resources in both the application-development tool and the database server. For an ESQL/C example of a FREE statement that frees a cursor, see the previous example.

After a cursor is freed, it cannot be opened until it is declared again. The cursor should be explicitly closed before it is freed.

When an SPL routine completes execution, the database server automatically releases any resources that had been allocated to the cursor or to prepared statements by PREPARE or DECLARE statements in the routine, if these have not already been released by the FREE statement.

The FREE statement in SPL routines cannot reference the cursor_id of a direct cursor that the FOREACH statement of SPL can declare.