FETCH

Positions a cursor on a specified row in the result set addressed by the cursor, and assigns values from the row to host variables.

 

Usage

Embedded, Procedural.

Description

The named cursor is positioned on the specified row in the result set defined by the cursor declaration. This row becomes the current row for the cursor.

There are two imaginary row positions for a cursor: ‘one row prior to FIRST and ‘one row after LAST’. The cursor will be positioned on these rows if any of the orientation specifications cause the cursor position to move before the FIRST row or after the LAST row of the result set respectively. Once the cursor position reaches one of the imaginary rows it will not advance any further in that direction.

The descriptor-name is identified by a host variable or a literal.

See ALLOCATE CURSOR for a description of extended cursors.

When using a scrollable cursor, the position of the cursor can be specified with an orientation specification.

The orientation can be specified in one of the following ways:

NEXT – Position the cursor on the row next to the current row

PRIOR – Position the cursor on the row prior to the current row

FIRST – Position the cursor on the first row in the result set

LAST – Position the cursor on the last row in the result set

ABSOLUTE – Position the cursor on the row with a specified absolute row number in the result set. Row zero does not exist and will return NOT FOUND. Negative numbers count from the end of the result set (i.e. -1 = LAST.)

RELATIVE – Position the cursor on the row specified with a row number relative to the current row in the result set. Zero is the current row, positive numbers count toward the end of the result set and negative numbers toward the beginning.

Values from the current row are assigned to target variables as listed in the INTO clause or specified in the named SQL descriptor area. The form FETCH… USING SQL DESCRIPTOR is used when an appropriate SQL descriptor area has been established. See the Mimer SQL Programmer's Manual, SQL Descriptor Area, for a discussion of the use of SQL descriptor areas.

The columns retrieved from the database are defined by the SELECT clause in the cursor declaration. The value from the first column in the SELECT clause is assigned to the first variable, that from the second column to the second variable, and so on. The data type of each variable must be assignment-compatible with the value in the corresponding column.

The number of columns in a row must be the same as the number of variables specified in the INTO clause.

If there is no next row in the set of rows, the cursor is placed ‘after the last row’, no new values are assigned to the variables and a NOT FOUND condition code is returned, see Return Status and Conditions.

Language Elements

target-variable, see Target Variables.

Restrictions

SELECT access to the table or view addressed by the table reference is checked when the cursor used for the FETCH statement is opened. Access to the base table is not required for a FETCH operation on a view.

The cursor cannot be identified by specifying extended-cursor-name in a procedural usage context. The INTO SQL DESCRIPTOR clause cannot be used in a procedural usage context.

Notes

If the cursor is not declared as scrollable, the FETCH operation always positions the cursor at the next row in the result set. For such a cursor, orientation specification is not allowed (except for NEXT).

If the orientation specification is omitted for a scrollable cursor, NEXT is implicit.

If the cursor that is used by the FETCH statement is not declared with an ORDER BY clause, the sort order for the result set is undefined, even if the cursor is defined as scrollable. This means that the sort order may change if new indexes are created, if indexes are dropped, if new statistics are gathered, or if a new version of the SQL optimizer is installed. To assure a specific sort order, ORDER BY must be used.

If a data conversion error occurs in assigning a value to a variable, an error code is returned and the execution of the FETCH statement is terminated. All variables successfully assigned before the error occurred retain their assigned values.

Examples

Embedded SQL example:

exec sql FETCH 'Cursor1' INTO SQL DESCRIPTOR 'DescrOut';

if (strcmp(SQLSTATE,"00000") == 0) {

    exec sql GET DESCRIPTOR 'DescrOut' VALUE 1 :ival = DATA;

   …

PSM example:

…

FETCH c_2

  INTO Data;

RETURN (Data.Title, Data.Artist, Data.Format, Data.Price,

                    Data.Item_ID, Data.Artist_ID, '***');

…

 

For more information on how you can use the FETCH statement, see the Mimer SQL Programmer's Manual, Mimer SQL Stored Procedures.

Standard Compliance