Release 10.1A: OpenEdge Data Management:
SQL Reference

> ## FETCH

> Moves the position of the cursor to the next row of the active set and fetches the column values of the current row into the specified host variables.

> #### Syntax

>     FETCH cursor_name
>       { USING SQL DESCRIPTOR structure_name
>         | INTO :host_var_ref [ [ INDICATOR ] :ind_var_ref ] , ...
>       } ;

> `cursor_name`

> A name identified in an earlier `DECLARE` `CURSOR` statement and an `OPEN` `CURSOR` statement.

> `USING SQL DESCRIPTOR structure_name`

> Directs the SQL engine to `FETCH` data into storage addressed by an SQLDA structure.

> `INTO :host_var_ref [ [ INDICATOR ] :ind_var_ref ]`

> Directs the SQL engine to `FETCH` data into the identified host variables, and to set values in the identified indicator variables.

> Example

>     /*
>     **  One way to limit the number of rows returned is to
>     **  set a new value for "j" here. As supplied in the SPORTS2000 database,
>     **  the PUB.InventoryTrans table contains 75 rows.
>     */
>           j = 100;
>           for (i = 0; i < j; i++)
>                {
>                EXEC SQL FETCH dyncur INTO
>                  :int_p1_v, :int_p2_v, :char_p_v ;
>                if (i == 0)
>                    {
>                    printf (" 1st col  2nd col  3rd col");
>                    printf (" -------  -------  --------");
>                    }
>                printf (" %d  %d  %s ",
>                        int_p1_v, int_p2_v, char_p_v) ;
>                }

> #### Notes

> - A `FETCH` operation requires that the cursor be open.

> - The positioning of the cursor for each `FETCH` operation is as follows:

> - The first time you execute a `FETCH` statement after opening the cursor, the cursor is positioned to the first row of the active set.
> - Subsequent `FETCH` operations advance the cursor position in the active set. The next row becomes the current row.
> - When the current row is deleted using a positioned `DELETE` statement, the cursor is positioned before the row after the deleted row in the active set.
> - The cursor can only be moved forward in the active set by executing `FETCH` statements. To move the cursor to the beginning of the active set, you must `CLOSE` the cursor and `OPEN` it again.

> - If the cursor is positioned on the last row of the active set or if the active set does not contain any rows, executing a `FETCH` will return the status code `SQL_NOT_FOUND` in the SQLDA.

> - After a successful `FETCH`, the total row count fetched so far for this cursor is returned in `sqlca.sqlerrd[2]`. The count is set to zero after an `OPEN` cursor operation.

> - You can `FETCH` multiple rows in one `FETCH` operation by using array variables in the `INTO` clause. The `SQL_NOT_FOUND` status code is returned in the `SQLCA` when the end of the active set is reached, even if the current `FETCH` statement returns one or more rows.

> - If you use array variables in a `FETCH` statement, the array sizes are set to the number of rows fetched after the `FETCH` statement is executed.

> ##### Authorization

> None

> ##### Related statements

> DECLARE CURSOR, OPEN, CLOSE

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095