Fetching Rows with a Cursor

From InterBase

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.


Retrieving Indicator Status

Any column can have a NULL value, except those defined with the NOT NULL or UNIQUE integrity constraints. Rather than store a value for the column, InterBase sets a flag indicating the column has no assigned value.

To determine if a value returned for a column is NULL, follow each variable named in the INTO clause with the INDICATOR keyword and the name of a short integer variable, called an indicator variable, where InterBase should store the status of the NULL value flag for the column. If the value retrieved is:

  • NULL, the indicator variable is set to –1.
  • Not NULL, the indicator parameter is set to 0.

For example, the following C code declares three host-language variables, department, manager, and missing_manager, then retrieves column values into
department, manager, and a status flag for the column retrieved into manager, ­missing_manager, with a FETCH from a previously declared cursor, GETCITY:

. . .
char department[26];
char manager[36];
short missing_manager;
. . .
FETCH GETCITY INTO :department, :manager INDICATOR :missing_manager;

The optional INDICATOR keyword can be omitted:

FETCH GETCITY INTO :department, :manager :missing_manager;

Often, the space between the variable that receives the actual contents of a column and the variable that holds the status of the NULL value flag is also omitted:

FETCH GETCITY INTO :department, :manager:missing_manager;
Note:
While InterBase enforces the SQL requirement that the number of host variables in a FETCH must equal the number of columns specified in DECLARE CURSOR, indicator variables in a FETCH statement are not counted toward the column count.

Refetching Rows with a Cursor

The only supported cursor movement is forward in sequential order through the active set.

To revisit previously fetched rows, close the cursor and then reopen it with another OPEN statement. For example, the following statements close the ­DEPT_EMP cursor, then recreate it, effectively repositioning the cursor at the start of the DEPT_EMP results table:

EXEC SQL
CLOSE DEPT_EMP;
EXEC SQL
OPEN DEPT_EMP;

Advance To: