CONNX Data Integration Suite 14.8.0 | CONNX Embedded SQL for Adabas | Standard SQL Statements | FETCH
 
FETCH
Function
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.
Invocation
Embedded Mode P Dynamic Mode Interactive Mode
Syntax
The syntax is FETCH, cursor or host variable identifier with optional INTO or USING clause. If you will use INTO,you can use * or a host variable specification.
cursor_identifier
Identifies the cursor to be used in the FETCH operation.
host_variable_identifier
A valid single host variable identifier. It must be 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 be defined in the application program according to the host language rules.
USING clause
Defines the SQL descriptor area used that will receive data from the associated dynamic cursor.
Description
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 with the USING clause is 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 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 one by one.
*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.
Limitations
*The cursor must be 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.