HP 3000 Manuals HP 3000 Manuals
FETCH
The FETCH statement advances the position of an opened cursor to the next
row of the active set and copies selected columns into the specified host
variables or data buffer. The row to which the cursor points is called
the current row.
Scope
Application Programs Only
SQL Syntax
{INTO HostVariableSpecification }
{ {[SQL]DESCRIPTOR {SQLDA }}}
[BULK] FETCH CursorName {USING { {AreaName}}}
{ { }}
{ {HostVariableSpecification }}
Parameters
BULK is specified in an application program to
retrieve multiple rows with a single execution
of the FETCH statement. After a BULK FETCH
statement, the current row is the last row
fetched.
BULK can be specified with the INTO clause (for
a statically executed cursor), but not with the
USING clause (for a dynamically executed
cursor).
BULK is disallowed in a procedure.
CursorName identifies a cursor. The cursor's active set,
determined when the cursor was opened, and the
cursor's current position in the active set
determine the data to be returned by each
successive FETCH statement.
INTO The INTO clause defines where to place rows
fetched for a statically preprocessed SELECT or
EXECUTE PROCEDURE statement.
USING The USING clause defines where to place rows
fetched for a dynamically preprocessed SELECT
or EXECUTE PROCEDURE statement, or for a
statically preprocessed EXECUTE PROCEDURE
statement with an unknown format.
HostVariableSpecification identifies one or more host variables for
holding and describing the row(s) in the active
set.
When used with the INTO clause, the syntax of
HostVariableSpecification depends on whether
the BULK option is specified. If BULK is
specified, HostVariableSpecification identifies
an array that holds the rows fetched. If BULK
is not specified, the host variable declaration
identifies a list of individual host variables.
The syntax of BULK and non-BULK variable
declarations is shown in separate sections
below.
The USING clause with a
HostVariableSpecification allows non-BULK
variable declarations only.
DESCRIPTOR The DESCRIPTOR identifier defines where to
place rows selected in accord with a
dynamically preprocessed SELECT or EXECUTE
PROCEDURE statement that has been described by
a DESCRIBE statement. For a select cursor,
specify the same location (SQLDA, area name, or
host variable) as you specified in the DESCRIBE
statement. For a procedure cursor, specify the
same location you specified in the ADVANCE
statement or DESCRIBE RESULT statement (for a
procedure created WITH RESULT).
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
between the application and ALLBASE/SQL.
SQL Syntax--BULK HostVariableSpecification
:Buffer [,:StartIndex [,:NumberOfRows]]
Parameters--BULK HostVariableSpecification
Buffer is a host array structure that is to receive the output
of the FETCH statement. This structure contains fields
for each column in the active set and indicator variables
for columns that contain null values. Whenever a column
can contain nulls, an indicator variable must be included
in the structure definition immediately after the
definition of that column. The indicator variable can
receive the following integer values after a FETCH:
0 meaning the column's value is not null
-1 meaning the column's value is null
>0 meaning the column's value is truncated (for
CHAR, VARCHAR, BINARY, and VARBINARY columns)
StartIndex is a host variable whose value specifies the array
subscript denoting where the first row fetched should be
stored; default is the first element of the array.
NumberOfRows is a host variable whose value specifies the maximum
number of rows to fetch; default is to fill from the
starting index to the end of the array.
The total number of rows fetched is returned in the
SQLERRD field of the SQLCA. You should check this area in
case the number of rows returned is less than the maximum
number of rows so that you don't process an incomplete
result.
SQL Syntax--non-BULK HostVariableSpecification
{:HostVariable [[INDICATOR] :Indicator]} [,...]
Parameters--non-BULK HostVariableSpecification
HostVariable identifies the host variable corresponding to one column in
the row fetched.
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 column's value is not null
-1 meaning the column's value is null
>0 meaning the column's value is truncated (for CHAR,
VARCHAR, BINARY, and VARBINARY columns)
Description
* This statement cannot be used interactively.
* When using this statement to access LONG columns, the name of the
file is returned in the appropriate field in the host variable
declaration parameter, SQLDA, or area name parameter specified.
If the output mode is specified with $, then each LONG column in
each row accessed is stored in a file with a unique name.
* The use of a descriptor area implies a multiple row result set.
You cannot use the BULK keyword if you employ the DESCRIPTOR
identifier.
* For a procedure cursor that returns results of a single format, if
the procedure was created with the WITH RESULT clause, since all
result sets have the same format, it is not necessary to issue an
ADVANCE statement to advance from one result set to the next. No
end of result set condition is generated on a FETCH statement
until all result sets have been fetched. When the end of a result
set has been reached, the next FETCH statement issued causes
procedure execution to continue either until the next result set
is encountered and the first row of the next result set is
returned or until procedure execution terminates.
* The USING clause is not allowed within a procedure.
* The BULK option is not allowed within a procedure.
Authorization
You do not need authorization to use the FETCH statement.
Examples
1. Static update
A cursor for use in updating values in column QtyOnHand is
declared and opened.
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 and accepting a new QtyOnHand
value from a user go here. The new value is stored in :NewQty.
UPDATE PurchDB.Inventory
SET QtyOnHand = :NewQty
WHERE CURRENT OF NewQtyCursor
CLOSE NewQtyCursor
2. Static bulk fetch
DECLARE ManyRows CURSOR FOR
SELECT * FROM PurchDB.Inventory
In some instances, using the BULK option is more efficient than
advancing the cursor a row at a time through many rows, especially
when you want to operate on the rows with non-ALLBASE/SQL
statements.
OPEN ManyRows
BULK FETCH ManyRows INTO :Rows, :Start, :NumRow
The query result is returned to an array called Rows.
3. Dynamic select cursor using an sqlda_type data structure
Assume that host variable Dynam1 contains a SELECT statement. The
statement stored in :Dynam1 is dynamically preprocessed.
PREPARE Dynamic1 FROM :Dynam1
The DESCRIBE statement loads the specified sqlda_type data
structure with the characteristics of the FETCH statement. See
the ALLBASE/SQL application programming guides for complete
information regarding this data structure.
DESCRIBE Dynamic1 INTO SQLDA
Define a cursor to be used to move through the query result row by
row.
DECLARE Dynamic1Cursor CURSOR FOR Dynamic1
Open the cursor to define rows of the active set.
OPEN Dynamic1Cursor
Fetch the selected data into the data buffer. Additional rows are
fetched with each execution of the FETCH statement until all rows
have been fetched. See the ALLBASE/SQL application programming
guides for more detailed examples.
FETCH Dynamic1Cursor USING DESCRIPTOR SQLDA
Close the cursor to free the active set.
CLOSE Dynamic1Cursor
4. Dynamic select cursor using host variables
Assume that host variable Dynam1 contains a SELECT statement. The
statement stored in :Dynam1 is dynamically preprocessed.
PREPARE Dynamic1 FROM :Dynam1
Define a cursor to be used to move through the query result row by
row.
DECLARE Dynamic1Cursor CURSOR FOR Dynamic1
Open the cursor to define rows of the active set.
OPEN Dynamic1Cursor
Fetch the selected data into the specified host variables. With
each execution of the FETCH statement one additional row is
fetched until all rows have been fetched.
FETCH Dynamic1Cursor USING :HostVariable1, :HostVariable2
Close the cursor to free the active set.
CLOSE Dynamic1Cursor
5. Refer to the ALLBASE/SQL Advanced Application Programming Guide
for a pseudocode example of procedure cursor usage.