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 not NULL, the value of the indicator variable specifies the maximum length of the host variable data.

Place indicator variables immediately after the corresponding host variable that is 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;

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;

In this example, 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;
}