|
|
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.
Application Programs Only
[BULK] FETCH CursorName {INTO HostVariableSpecification
USING { [SQL] DESCRIPTOR {SQLDA
AreaName}
HostVariableSpecification } }
- 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.
:Buffer [,:StartIndex [,:NumberOfRows]]
- 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.
{:HostVariable [[INDICATOR] :Indicator] } [,...]
- 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)
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.
You do not need authorization to use the FETCH statement.
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
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.
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
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 for more detailed
examples.
FETCH Dynamic1Cursor USING DESCRIPTOR SQLDA
Close the cursor to free the active set.
CLOSE Dynamic1Cursor
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
Refer to the ALLBASE/SQL Advanced Application Programming
Guide for a pseudocode example of procedure cursor usage.
|