FETCH (E-SQL)

The FETCH statement retrieves a specific row from the cursor.

Syntax

FETCH [ [ NEXT | PRIOR | FIRST | LAST ] FROM ] cursor_name [USING DESCRIPTOR :sqlda_struct | INTO :hvar [,...]]

Arguments

NEXT

Specifies returning the first row of the result set if this FETCH statement is the first FETCH against the cursor; otherwise, specifies moving the cursor one row in the result set. NEXT is the default method used to move through a result set.

PRIOR

Specifies returning the previous row in the result set.

FIRST

Specifies moving the cursor to the first row in the result set and returning the first row.

LAST

Specifies moving the cursor to the last row in the result set and returning the last row.

cursor_name

Is a previously declared and opened cursor.

sqlda_struct

Is an output SQLDA data structure that was previously populated by the DESCRIBE statement and that contains output value addresses. This option is used only with a cursor declared by prepared SELECT statements. (SELECT statements are prepared by using the PREPARE statement.)

hvar

Is one or more host variables to receive the data.

Remarks

If the NEXT, PRIOR, FIRST, or LAST options are not specified, the FETCH statement retrieves the next n rows from the result set produced by the OPEN statement for this cursor and writes the values of the columns in those rows to the corresponding host variables or to addresses specified in the SQLDA data structure.

An OPEN cursor_name statement must precede a FETCH statement, and the cursor must be open while FETCH runs. Also, the data type of the host variable must be compatible with the data type of the corresponding database column.

If the number of columns is less than the number of host variables, the value of SQLWARN3 is set to W. If an error occurs, no further columns are processed. Processed columns are not undone. The SQLCODE value of 100 indicates that no more rows exist in the result set.

The USING DESCRIPTOR :sqlda_struct option can be used only with a dynamically defined cursor. The INTO :hvar option can be used with either a dynamic or static cursor.

Examples

EXEC SQL DECLARE C1 CURSOR FOR

    SELECT au_fname, au_lname FROM authors FOR BROWSE;

EXEC SQL OPEN C1;

while (SQLCODE == 0)

{

    EXEC SQL FETCH C1 INTO :fname, :lname;
}


See Also