Alpha

These pages are built from main, turborel 0.0.0. Anything may change from one build to the next: SQL, API, protocol and files alike.

FETCH

Statements

NAME

FETCH: move a cursor and read its row (in a program)

SYNOPSIS

<fetch statement> ::=
    FETCH [ [ <fetch orientation> ] FROM ] <cursor name>
    INTO <target specification> [ { , <target specification> }... ]

<fetch orientation> ::=
      NEXT | PRIOR | FIRST | LAST
    | { ABSOLUTE | RELATIVE } <simple value specification>

<target specification> ::= <parameter name> [ [ INDICATOR ] <parameter name> ]

<dynamic fetch statement> ::=
    FETCH [ [ <fetch orientation> ] FROM ] <dynamic cursor name>
    { INTO <target specification> [ { , <target specification> }... ]
    | INTO SQL DESCRIPTOR <descriptor name> }

The <dynamic cursor name> is OPEN's, the <descriptor name> ALLOCATE DESCRIPTOR's. In a polymorphic table function's fulfill component, PTF and a name are of its cursors and its areas (CREATE FUNCTION).

DESCRIPTION

FETCH moves an open cursor to a row and assigns the row's values to the targets, in order: NEXT, the default, to the row after; PRIOR, the row before; FIRST and LAST; ABSOLUTE n, the nth row, counting from the last if n is negative; RELATIVE n, n rows on, or back if negative. Past the last row, or before the first, the cursor is on no row and FETCH completes with no data, SQLSTATE 02000, the targets unchanged.

A cursor a compound statement declares (BEGIN) is fetched by this statement within it, in a routine's body, a trigger's action or a statement run directly. Its name is the compound statement's own, in scope there alone, and the cursor is destroyed as the compound statement ends. Its targets are SQL variables and SQL parameters, and a NOT FOUND handler takes its no data.

A fulfill component fetches, NEXT alone, from the cursor of each table argument it is passed the name of, FETCH FROM PTF and that name. Its rows are the partition's, in the argument's ORDER BY, each the columns the describe component requested, then of PASS THROUGH a BIGINT that PIPE ROW gives back to name the row.

A null value sets the target's indicator to -1. A character string longer than its target is cut to fit, and the indicator holds its length in characters; FETCH then completes with a warning, SQLSTATE 01004, SQLCODE 0. A binary string is cut so, its length in octets in the indicator; one shorter than a BINARY target is padded with X'00' octets, with no warning. Any other value is assigned to a target as to a column, by its type.

INTO SQL DESCRIPTOR puts the row into the items of a descriptor area, as EXECUTE puts a single SELECT's: each value cast to its item's type, INDICATOR 0 and DATA the value, or INDICATOR -1 of the null value. A cursor of an extended name is fetched from as one of a cursor name. The diagnostics area names a dynamic FETCH DYNAMIC FETCH.

FETCH runs only in a program that embeds turborel: see DECLARE CURSOR.

RULES

  1. The cursor is open.
  2. An orientation other than NEXT is of a SCROLL cursor.
  3. The targets are as many as the cursor's columns, each a parameter of a type its column's value may be assigned to; a null value has a target with an indicator.
  4. A descriptor area's COUNT is the number of columns, each item of a type its column's values are cast to; the clause is of INTO, not USING.

PRIVILEGES

None.

ERRORS

SQLSTATE SQLCODE When
2A000 -103 A rule above is broken
24000 -501 The cursor is not open
34000 -921 No cursor of the extended name is allocated
07002, 07006, 07008 -921 The descriptor area does not match the columns, as of EXECUTE (rule 4)
22002 -301 A null value, and its target has no indicator
02000 100 Not an error: no data, the cursor past its last row or before its first
01004 0 Not an error: a warning, a string cut to fit its target

EXAMPLES

Of the table STAFF of DECLARE CURSOR, its third row's NAME null, C a cursor of SELECT ID, NAME FROM STAFF ORDER BY ID and S a SCROLL cursor of the same, each open; the targets MY_ID and MY_NAME, NAME_IND MY_NAME's indicator:

FETCH C INTO MY_ID, MY_NAME INDICATOR NAME_IND               1, Ada
FETCH C INTO MY_ID, MY_NAME INDICATOR NAME_IND               2, Grace
FETCH C INTO MY_ID, MY_NAME INDICATOR NAME_IND               3, NAME_IND = -1
FETCH C INTO MY_ID, MY_NAME INDICATOR NAME_IND               no data (02000)
FETCH PRIOR FROM C INTO MY_ID, MY_NAME INDICATOR NAME_IND    -103 (2A000): the cursor is not SCROLL: it fetches NEXT alone [ISO/IEC 9075:1992 §13.3 SR3]
FETCH LAST FROM S INTO MY_ID, MY_NAME INDICATOR NAME_IND     3, NAME_IND = -1
FETCH ABSOLUTE 1 FROM S INTO MY_ID, MY_NAME INDICATOR NAME_IND   1, Ada
FETCH RELATIVE 1 FROM S INTO MY_ID, MY_NAME INDICATOR NAME_IND   2, Grace
FETCH PRIOR FROM S INTO MY_ID, MY_NAME INDICATOR NAME_IND    1, Ada

Of a cursor not open:

FETCH C INTO MY_ID, MY_NAME INDICATOR NAME_IND   -501 (24000): cursor C is not open [X3.135-1986 §8.6 GR1]

Of S, of a target SHORT, CHAR(2), and one with no indicator:

FETCH ABSOLUTE -1 FROM S INTO MY_ID, MY_NAME INDICATOR NAME_IND   3, NAME_IND = -1
FETCH ABSOLUTE 1 FROM S INTO MY_ID, SHORT INDICATOR NAME_IND      1, SHORT = 'Ad', NAME_IND = 3; warning 01004
FETCH LAST FROM S INTO MY_ID, MY_NAME                             -301 (22002): the value is null, and target MY_NAME has no indicator parameter [X3.135-1986 §8.6 GR8]

Of the dynamic cursor D of DECLARE CURSOR, open:

FETCH D USING MY_ID, MY_NAME    -103 (2A000): the <using clause> of a FETCH is of INTO [ISO/IEC 9075:1992 §17.15 SR2]

STANDARD

ISO/IEC 9075:1992 §13.3, and of a dynamic cursor §17.15; X3.135-1986 §8.6, whose FETCH moves to the next row alone. A local cursor's is ISO/IEC 9075-4:2006 draft §12.3, Feature P002.

SEE ALSO

DECLARE CURSOR, OPEN, CLOSE, SELECT