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
:
- Retrieves the next available row from the results table.
- Copies those rows into the host variables specified in the
INTO
clause of theFETCH
statement. - 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>] ...];
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;
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;