|
|
The CLOSE statement is used to close an open cursor.
Application Programs or Procedures
CLOSE CursorName [USING {[SQL]DESCRIPTOR {SQLDA
Areaname}
:HostVariable [[INDICATOR]:Indicator][,...]}]
- 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.
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.
You do not need authorization to use the CLOSE statement.
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
|