Using Null Indicators to Assign Nulls
Use an indicator variable with a host language variable to specify the assignment of a null to a database column. (An indicator variable can also be used to assign a null to a form field or a table field column.) When the DBMS Server assigns the value from a host language variable to one of these objects, it checks the value of the host language variable’s associated indicator variable.
If the indicator variable value is -1, a null is assigned to the object and ignores the value in the host language variable. If the indicator variable is any value other than -1, the value of the host language variable is assigned to the object.
If the indicator value is -1 and the object type is not nullable (such as a column created with the NOT NULL clause), an error results.
The following example demonstrates the use of an indicator variable and the null constant with the INSERT statement. For a description of the null constant, see Nulls in the chapter “SQL Data Types.”
read name, phone number, and id from terminal;
if (phone = ' ') then
phone_null = -1;
else
phone_null = 0;
end if;
exec sql insert into newemp (name, phone, id,
comment)
values (:name, :phone:phone_null, :id, null);
This second example retrieves data from a form and updates the data in the database:
exec frs getform empform (:name:name_null = name,
:id:id_null = id);
exec sql update employee
set name = :name:name_null, id = :id:id_null
where current of emp_cursor;
Use null indicators to assign nulls in:
• The VALUES clause of the INSERT statement
• The SET clause of the UPDATE statement
• EXECUTE PROCEDURE statement parameters
All constant expressions in the above clauses can include the keyword NULL. Wherever an indicator variable can be used to assign a null, you can use the keyword NULL.