<img src="02fetch19.gif" height="49" width="100"> FREE

Informix Guide to SQL: Syntax
SQL Statements

FREE

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

Use this statement with ESQL/C.

Syntax

Element Purpose Restrictions Syntax

cursor_id Name of a cursor A DECLARE statement must have previously created the cursor. Identifier, p. 4-205

cursor_id_var Host variable that holds the value of cursor_id Variable must be a character data type. Cursor must have been previously created by a DECLARE statement. Name must conform to language-specific rules for variable names.

statement_id String that identifies an SQL statement The statement identifier must be defined in a previous PREPARE statement. After you release the database-server resources, you cannot use the statement identifier with a DECLARE cursor or with the EXECUTE statement until you prepare the statement again. PREPARE, p. 2-579

statement_id_var Host variable that identifies an SQL statement The statement identifier must be defined in a previous PREPARE statement. The host variable must be a character data type. PREPARE, p. 2-579

Element	Purpose	Restrictions	Syntax
cursor_id	Name of a cursor	A DECLARE statement must have previously created the cursor.	Identifier, p. 4-205
cursor_id_var	Host variable that holds the value of cursor_id	Variable must be a character data type. Cursor must have been previously created by a DECLARE statement.	Name must conform to language-specific rules for variable names.
statement_id	String that identifies an SQL statement	The statement identifier must be defined in a previous PREPARE statement. After you release the database-server resources, you cannot use the statement identifier with a DECLARE cursor or with the EXECUTE statement until you prepare the statement again.	PREPARE, p. 2-579
statement_id_var	Host variable that identifies an SQL statement	The statement identifier must be defined in a previous PREPARE statement. The host variable must be a character data type.	PREPARE, p. 2-579

Usage

The FREE statement releases the resources that the database server and application-development tool allocated for a prepared statement or a declared cursor.

Freeing a Statement

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

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.

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

The following ESQL/C 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 ESQL/C 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;

Freeing a Cursor

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

If a cursor is not declared for a prepared statement, freeing the cursor releases the resources in both the application-development tool and the database server.

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

For an example of a FREE statement that frees a cursor, see the second example in Freeing a Statement.

Related Information

Related statements: CLOSE, DECLARE, EXECUTE, EXECUTE IMMEDIATE, OPEN, PREPARE, and SET AUTOFREE