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] , ...} ;

Parameters

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.

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.

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) ; 
           }