 |
» |
|
|
|
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 valid:
- 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
|
|
