HP 3000 Manuals HP 3000 Manuals
CLOSE
The CLOSE statement is used to close an open cursor.
Scope
Application Programs or Procedures
SQL Syntax
[ {[SQL]DESCRIPTOR {SQLDA } }]
CLOSE CursorName [USING { {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