Indicator Variables
An indicator variable can be associated with a host variable for the following purposes:
• To indicate if a null was retrieved from a column
• To assign a null to a column.
• To indicate if a string retrieved from a column was truncated.
Use the following syntax to associate an indicator variable with a host variable:
host_variable:indicator_variable
If your application program retrieves a null into a host variable, and an indicator variable is not associated with the host variable, the DBMS Server issues a runtime error.
Retrieving Data Using Null Indicators
After you retrieve data into a host variable that is associated with an indicator variable, the indicator variable contains one of the following values:
-1
Value was null. The contents of the host variable are unchanged.
0
Value was not null. The host variable contains the retrieved value.
The following example illustrates the use of an indicator variable. In this example the indicator value is used to detect missing phone numbers, which are listed in a roster as "n/a":
##retrieve cursor emp_cursor (name, phone:phone_null, id)
if (phone_null = -1) then
update_roster(name, "n/a", id)
else
update_roster(name, phone, id)
end if
The following EQUEL statements can include indicator variables in their output target lists:
• retrieve
• retrieve cursor
Setting Values Using Null Indicators
To assign null to a database column, set the indicator variable (associated with the host variable you are writing) to -1 and execute the assignment statement. You can also assign null using the keyword null.
You can use the following statements in conjunction with indicator variables to assign null values:
• append
• replace
• replace cursor
If you attempt to assign a null to an object that is not nullable, the DBMS Server issues a runtime error.
A null indicator variable can accompany a variable used in the where clause of the retrieve, append and replace statements, if you are comparing with nullable columns or expressions.
The following example demonstrates the use of both an indicator variable and the null constant: an indicator variable is used to set "phone" to null (if no phone number was entered), and the null constant is used to set the "comment" field before it is written to the new employee database.
read ename, eno, phone from terminal
if phone = "" then
phone_null = -1
else
phone_null = 0
end if
##append to newemp (empname = ename,
## #phone = phone:phone_null,
## empnum = eno, comment = null)
Detecting String Truncation Using Indicator Variables
If you application retrieves a character string into a host variable that is too small to hold the string, the DBMS Server truncates the string to fit into the host variable. If you specify an indicator variable with the host variable, the indicator variable is set to the original length of the data. You can detect truncation by comparing the value of the indicator variable with the length of the string that was retrieved: if the indicator variable is greater than the length, the string was truncated.
Last modified date: 11/28/2023