Host Variables and Null Values

Unlike SQL, the C language does not support variables with null (unknown or missing) values. Embedded SQL enables you to store and retrieve null values from a database by using host indicator variables. Together, a host variable and its companion indicator variable specify a single SQL value. Each of the variables must be preceded by a colon (:). When a host variable is NULL, its indicator variable has the value -1. When a host variable is nonNULL, the value of the indicator variable specifies the maximum length of the host variable data.

Place indicator variables immediately after the corresponding host variable specified in the Embedded SQL statement. For example, the following embedded UPDATE statement uses a saleprice host variable with a companion saleprice_null indicator variable:

EXEC SQL UPDATE closeoutsale
   SET temp_price = :saleprice :saleprice_null, listprice = :oldprice;

In the following SELECT statement, price nullflag is set to -1 because the price of this book is NULL:

EXEC SQL 
SELECT price INTO :price:price nullflag
FROM titles
WHERE au_id = "mc3026"

Optionally, you can precede an indicator variable with the INDICATOR keyword when using a host variable and its associated indicator variable. For example, the following embedded UPDATE statement uses the INDICATOR keyword to more easily identify the indicator variable saleprice_null:

EXEC SQL UPDATE closeoutsale
   SET temp_price = :saleprice INDICATOR :saleprice_null;

If saleprice_null has a value of -1 when the UPDATE statement executes, Embedded SQL will change the statement to:

EXEC SQL UPDATE closeoutsale
   SET temp_price = null, listprice = :oldprice;

You cannot use indicator variables in a search condition. For example, you cannot use the following Embedded SQL statement:

EXEC SQL DELETE FROM closeoutsale
   WHERE temp_price = :saleprice :saleprice_null;

However, you can use the following technique to search for null values:

if (saleprice_null == -1)
{
   EXEC SQL DELETE FROM closeoutsale
      WHERE temp_price IS null;
}
else
{
   EXEC SQL DELETE FROM closeoutsale
      WHERE temp_price = :saleprice;
}