Fetching Rows with a Cursor

Go Up to Selecting Multiple Rows

Once a cursor is opened, rows can be retrieved, one at a time, from the results table by using the FETCH statement. FETCH:

1. Retrieves the next available row from the results table.
2. Copies those rows into the host variables specified in the INTO clause of the FETCH statement.
3. Advances the cursor to the start of the next available row or sets
   SQLCODE to 100, indicating the cursor is at the end of the results table and there are no more rows to retrieve.

The complete syntax of the FETCH statement in SQL is:

FETCH <cursorname> INTO :variable [[INDICATOR] :variable]
[, :variable [[INDICATOR] :variable>] ...];

Important: In dynamic SQL (DSQL) multi-row select processing, a different FETCH syntax is used. For more information about retrieving multiple rows in DSQL, see Fetching Rows with a DSQL Cursor.

For example, the following statement retrieves a row from the results table for the DEPT_EMP cursor, and copies its column values into the host-language variables, deptname, lname, and fname:

EXEC SQL
FETCH DEPT_EMP
INTO :deptname, :lname, :fname;

To process each row in a results table in the same manner, enclose the FETCH statement in a host-language looping construct. For example, the following C code fetches and prints each row defined for the DEPT_EMP cursor:

. . .
EXEC SQL
FETCH DEPT_EMP
INTO :deptname, :lname, :fname;
while (!SQLCODE)
{
printf("%s %s works in the %s department.\n", fname,
lname, deptname);
EXEC SQL
FETCH DEPT_EMP
INTO :deptname, :lname, :fname;
}
EXEC SQL
CLOSE DEPT_EMP;
. . .

Every FETCH statement should be tested to see if the end of the active set is reached. The previous example operates in the context of a while loop that continues processing as long as SQLCODE is zero. If SQLCODE is 100, it indicates that there are no more rows to retrieve. If SQLCODE is less than zero, it indicates that an error occurred.

Topics

• Retrieving Indicator Status
• Refetching Rows with a Cursor