The FETCH statement positions the cursor on a row within the resultant table and makes the values of that row available to the application program.
|
Embedded Mode P |
Dynamic Mode |
Interactive Mode |
Syntax
|
cursor_identifier |
Identifies the cursor to be used in the FETCH operation. |
|
host_variable_identifier |
A valid single host variable identifier. It must have been defined in the application program according to the host language rules. The value of the host variable must be a valid cursor identifier. A host variable can be used as cursor identifier only if the cursor is a dynamically declared cursor. |
|
host_variable_specification |
A valid host variable specification. It must reference a structure and must have been defined in the application program according to the host language rules. |
|
USING_clause |
Defines an SQL descriptor area used to receive data from the associated dynamic cursor. |
The FETCH statement performs two functions: it moves the cursor in the resultant table from top to bottom, one row at a time, and makes the relevant values of a row available to the application program according to the specification of the INTO clause or the USING clause. The mechanism used when the USING clause has been specified in USING Clause .
The FETCH statement changes the position of the cursor as follows:
If the cursor is positioned before the first row of the resultant table (as would be the case if the cursor had just been opened), it is moved to the first row.
If the cursor is positioned on a row of the resultant table, it is moved to the next one.
If the cursor is positioned on the last row of an resultant table, it is moved past the last row and the SQLCODE field in the SQLCA is set to +100.
If the row on which the cursor is positioned is deleted, the cursor is, then positioned in front of the next row in the table.
A host variable specification which references a host variable structure is equivalent to individual host variable specifications which reference all the elements of a structure singularly.
Each host variable corresponds to a resultant column of the resultant table of the cursor in question, where the first host variable is passed with the first column and so on.
Each value of a resultant column is assigned to the corresponding host variable . The assignment operation follows the normal conversion rules as described in Expressions in the Adabas SQL Gateway User Guide.
The cursor must have been prepared and opened prior to the execution of the FETCH statement.
The data type of a host variable must be compatible with the data type of its corresponding resultant column. If the data type is not compatible, an error occurs. The value of the unassignedhost variable is unpredictable.
If the number of resultant columns is smaller than the number of host variables, as many host variables as possible are assigned the values of their corresponding resultant columns. The remaining host variables are left untouched.
If the number of resultant columns is greater than the number of host variables , an error message (warning) is generated.
A USING clause may only be used in association with a dynamic cursor.
An INTO clause is mandatory, the USING clause must not be used. Only single host variable specifications are permitted.
The OPEN statement and the FETCH statement can be in different compilation units (see also DECLARE CURSOR).
Example
The following example fetches data from a cursor and places the data into three host variables.
FETCH cursor_id
INTO :host_var1,
:host_var2,
:host_var3 ;
