Using Null Indicators to Assign Nulls
An indicator variable can be used with a host variable to assign a null value to a table column. When OpenSQL performs the assignment, it checks the value of the host variable's associated indicator variable. If the indicator variable's value is ‑1, then OpenSQL assigns a null to the column and ignores the value of the host variable. If the indicator variable does not contain ‑1, OpenSQL assigns the value of the host variable to the column. If the indicator value is ‑1 and the column is not nullable, then OpenSQL returns an error.
The following example demonstrates the use of an indicator variable and the null constant with the INSERT statement:
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 insert values list
• The update set list
• Constant expressions in select target lists used in embedded SELECT statements or subselect clauses
All constant expressions in the above list can include the keyword NULL. Specifying the word NULL is equivalent to specifying a null indicator with the value ‑1.
Last modified date: 08/14/2024