Null Indicators and Data Retrieval
When OpenSQL retrieves a null for a host variable that has an associated indicator variable, it sets the indicator variable to ‑1 and does not change the value of the host variable. If the value retrieved is not a null, then the indicator variable is set to 0 and the value is assigned to the host 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 variables
• Data handlers for long varchar and long byte values
The following example illustrates the use of a null indicator when retrieving data from a database. This program retrieves employee information, then updates a roster. If a null phone number is detected (using the indicator, variable phone_null), the program places the string, N/A, in the roster's phone column.
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;