CLOSE

The CLOSE statement is used to close an open cursor.

Scope

Application Programs or Procedures

SQL Syntax

  CLOSE CursorName [USING {[SQL]DESCRIPTOR {SQLDA 
                                            Areaname}                             :HostVariable [[INDICATOR]:Indicator][,...]}]

Parameters

CursorName

designates the open cursor to be closed.

USING

defines where to place return status and output parameters after closing a dynamic procedure cursor.

HostVariable

identifies a host variable for holding return status and output parameters after closing a dynamic procedure cursor. These must be specified in the same order as in the associated EXECUTE PROCEDURE statement.

Indicator

names the indicator variable, an output host variable whose value depends on whether the host variable contains a null value. The following integer values are svalid:

0: meaning the output parameter's value is not null
-1: meaning the output parameter's value is null
>0: meaning the output parameter's value is truncated (for CHAR, VARCHAR, BINARY, and VARBINARY columns)

DESCRIPTOR

defines where to place return status and output parameters after closing a procedure cursor. Specify the same location (SQLDA, area name, or host variable) as you specified in the DESCRIBE OUTPUT statement.

SQLDA

specifies that a data structure of sqlda_type named SQLDA is to be used to pass information about the prepared statement between the application and ALLBASE/SQL.

AreaName

specifies the user defined name of a data structure of sqlda_type that is to be used to pass information about the prepared statement.

Description

When it applies to a select cursor (one that is declared for a SELECT statement), the CLOSE statement can be issued in an application program or in a procedure.
When it applies to a procedure cursor (one that is declared for an EXECUTE PROCEDURE statement), the CLOSE statement can be issued only in an application program.
The CLOSE statement cannot be used in ISQL.
CLOSE returns an error if the cursor is not in the open state.
The COMMIT WORK and ROLLBACK WORK statements automatically close all cursors not opened with the KEEP CURSOR option.
To close a select cursor opened with the KEEP CURSOR option, you must perform an explicit CLOSE followed by a COMMIT WORK.
When you close a select cursor, its active set becomes undefined, and it can no longer be used in DELETE, FETCH, or UPDATE statements. To use the cursor again you must reopen it by issuing an OPEN statement.
When you close a procedure cursor, its active result set becomes undefined, and it can no longer be used in FETCH statements. To use the procedure cursor again you must reopen it by issuing an OPEN statement.
When used with a procedure cursor, CLOSE discards any pending rows or result sets from the procedure. Execution of the procedure continues with the next statement. Control returns to the application when the procedure terminates.
Note that following processing of the last multiple row result set, procedure execution cannot continue until you close or advance the procedure cursor in the application.
Upon execution of the CLOSE statement used with a procedure cursor, return status and output parameter values are available to the application in either the SQLDA or the HostVariableSpecification of the USING clause or in any host variables specified in the related DECLARE CURSOR statement.
The USING clause is allowed only for dynamic procedure cursors.

Authorization

You do not need authorization to use the CLOSE statement.

Examples

Declare and open a cursor for use in updating values in column QtyOnHand.

         DECLARE NewQtyCursor CURSOR FOR
          SELECT PartNumber,QtyOnHand FROM PurchDB.Inventory
   FOR UPDATE OF QtyOnHand
 
   OPEN NewQtyCursor

Statements setting up a FETCH-UPDATE loop appear next.

   FETCH NewQtyCursor INTO :Num :Numnul, :Qty :Qtynul

Statements for displaying a row to a user and accepting a new QtyOnHand value go here. The new value is stored in :NewQty.

             UPDATE PurchDB.Inventory
                SET QtyOnHand = :NewQty
   WHERE CURRENT OF NewQtyCursor
    .
    .
    .
   CLOSE NewQtyCursor USING sqldaout