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

  [BULK] FETCH CursorName{INTO HostVariableSpecification 
                          USING { [SQL] DESCRIPTOR {SQLDA 
                                                    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

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.