Retrieving Indicator Status

Go Up to Fetching Rows with a Cursor

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.