Null Indicators and Data Retrieval
When a null value is retrieved into a host language variable that has an associated indicator variable, the indicator variable is set to -1 and the value of the host language variable is not changed. If the value retrieved is not a null, the indicator variable is set to 0 and the value is assigned to the host language variable. If the value retrieved is null and the program does not supply a null indicator, an error results.
Null indicator variables can be associated with the following:
• SELECT INTO and FETCH INTO result variable lists
• Data handlers for long varchar and long byte values
• Database procedure parameters passed by reference
The following example illustrates the use of a null indicator when retrieving data. This example issues a FETCH statement and updates a roster while checking for null phone numbers indicated by the variable, phone_null.
exec sql fetch emp_cursor into :name,
:phone:phone_null, :id;
if (phone_null = -1) then
update_roster(name, 'N/A', id);
else
update_roster(name, phone, id);
end if;