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 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 the library function to assign a default value to the host variable. (The function rstrdate is part of the 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.