Progress
Embedded SQL-92
Guide and Reference


FETCH Rows Using a Cursor

Use the FETCH statement to read the rows of the active set and return the values into host variables. The SELECT statement associated with the cursor does not include the INTO clause. With cursor operations, the INTO clause and the list of output host variables are in the FETCH statement.

This is the syntax for a FETCH statement:

SYNTAX
EXEC SQL
  FETCH cursor_name INTO :hostvar1 [ , :hostvar2 ] , ... ; 

EXAMPLE

The following code fragment shows how to fetch rows opened by the cust_cur cursor:

/* Connect to the default database */
EXEC SQL CONNECT TO DEFAULT ;
 
/* Declare a cursor for retrieving columns from the customer table */
EXEC SQL
     DECLARE cust_cur CURSOR FOR
          SELECT last_name, city, state
          FROM customer
          WHERE cust_no = :cust_no_v;
/* Open the cursor */
EXEC SQL OPEN cust_cur ;
 
/* Fetch the query results into host variables */
for (;;)
{
     EXEC SQL
          FETCH cust_cur
          INTO :cust_no_v, :name_v, :city_v ;
     if (sqlca.sqlcode != 0) break ;
/*
** Process the result of a successful fetch here; still in the for loop. 
*/
}
/*
** Process SQL_NOT_FOUND and error conditions. 
*/
EXEC SQL CLOSE cust_cur ; 

You must declare and open a cursor before you can fetch into it. The first time you execute a fetch, the database positions the cursor on the first row of the active set. This row is now the current row. Each subsequent fetch operation advances the cursor to the next row in the active set. The only way to return to a row that you fetched earlier is to close and reopen the cursor. The behavior of cursor operations is also dependent on the isolation level for the current transaction.

When the cursor is positioned on the last row of the active set or if the active set does not contain any rows, then a fetch operation returns the status SQL_NOT_FOUND in the SQLCA. When this is the status, you must first close and reopen the cursor before you attempt any further operations with it.


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