What if the program retrieves a NULL value? NULL values can be stored in the database, but the data types that programming languages support do not recognize a NULL state. A program must have some way to recognize a NULL item to avoid processing it as data.
Indicator variables meet this need in SQL APIs. An indicator variable is an additional variable that is associated with a host variable that might receive a NULL item. When the database server puts data in the main variable, it also puts a special value in the indicator variable to show whether the data is NULL. In the following IBM Informix ESQL/C example, a single row is selected, and a single value is retrieved into the host variable op_date:
EXEC SQL SELECT paid_date INTO :op_date:op_d_ind FROM orders WHERE order_num = $the_order; if (op_d_ind < 0) /* data was null */ rstrdate ('01/01/1900', :op_date);
Because the value might be NULL, an indicator variable named op_d_ind is associated with the host variable. (It must be declared as a short integer elsewhere in the program.)
Following execution of the SELECT statement, the program tests the indicator variable for a negative value. A negative number (usually -1) means that the value retrieved into the main variable is NULL. If the variable is NULL, this program uses an ESQL/C library function to assign a default value to the host variable. (The function rstrdate is part of the IBM Informix ESQL/C product.)
The syntax that you use to associate an indicator variable with a host variable differs with the language you are using, but the principle is the same in all languages.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]