Informix Guide to SQL: Syntax SQL Statements

Use the CLOSE statement when you no longer need to refer to the rows that a select or function cursor produced or when you want to flush and close an insert cursor.

Use this statement with ESQL/C.

Closing a cursor makes the cursor unusable for any statements except OPEN or FREE and releases resources that the database server had allocated to the cursor. A CLOSE statement treats a cursor that is associated with an INSERT statement differently than one that is associated with a SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement.

You can close a cursor that was never opened or that has already been closed. No action is taken in these cases.

In an ANSI-compliant database, the database server returns an error if you close a cursor that was not open.

When a cursor identifier is associated with a SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement, closing the cursor terminates the SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement. The database server releases all resources that it might have allocated to the active set of rows, for example, a temporary table that it used to hold an ordered set. The database server also releases any locks that it might have held on rows that were selected through the cursor. If a transaction contains the CLOSE statement, the database server does not release the locks until you execute COMMIT WORK or ROLLBACK WORK.

After you close a select or function cursor, you cannot execute a FETCH statement that names that cursor until you have reopened it.

When a cursor identifier is associated with an INSERT statement, the CLOSE statement writes any remaining buffered rows into the database. The number of rows that were successfully inserted into the database is returned in the third element of the sqlerrd array, sqlca.sqlerrd[2], in the sqlca structure. For information on using SQLERRD to count the total number of rows that were inserted, see Error Checking.

The SQLCODE field of the sqlca structure, sqlca.sqlcode, indicates the result of the CLOSE statement for an insert cursor. If all buffered rows are successfully inserted, SQLCODE is set to zero. If an error is encountered, the sqlca.sqlcode field in the SQLCODE is set to a negative error message number.

When SQLCODE is zero, the row buffer space is released, and the cursor is closed; that is, you cannot execute a PUT or FLUSH statement that names the cursor until you reopen it.

Tip: When you encounter an SQLCODE error, a corresponding SQLSTATE error value also exists. For information about how to get the message text, check the GET DIAGNOSTICS statement.

If the insert is not successful, the number of successfully inserted rows is stored in sqlerrd. Any buffered rows that follow the last successfully inserted row are discarded. Because the insert fails, the CLOSE statement fails also, and the cursor is not closed. For example, a CLOSE statement can fail if insufficient disk space prevents some of the rows from being inserted. In this case, a second CLOSE statement can be successful because no buffered rows exist. An OPEN statement can also be successful because the OPEN statement performs an implicit close.

You can declare both select and insert cursors on collection variables. Such cursors are called collection cursors. Use the CLOSE statement to deallocate resources that have been allocated for the collection cursor.

For more information on how to use a collection cursor, see the following sections: Fetching From a Collection Cursor and Inserting into a Collection Cursor.

The COMMIT WORK and ROLLBACK WORK statements close all cursors except those that are declared with hold. It is better to close all cursors explicitly, however. For select or function cursors, this action simply makes the intent of the program clear. It also helps to avoid a logic error if the WITH HOLD clause is later added to the declaration of a cursor.

For an insert cursor, it is important to use the CLOSE statement explicitly so that you can test the error code. Following the COMMIT WORK statement, SQLCODE reflects the result of the COMMIT statement, not the result of closing cursors. If you use a COMMIT WORK statement without first using a CLOSE statement, and if an error occurs while the last buffered rows are being written to the database, the transaction is still committed.

For how to use insert cursors and the WITH HOLD clause, see DECLARE.

In an ANSI-compliant database, a cursor cannot be closed implicitly. You must issue a CLOSE statement.

Related statements: DECLARE, FETCH, FLUSH, FREE, OPEN, PUT, and SET AUTOFREE

For an introductory discussion of cursors, see the Informix Guide to SQL: Tutorial.

For a more advanced discussion of cursors, see the Informix ESQL/C Programmer's Manual.