Using Indicator Variables

An indicator variable is a 2-byte integer variable that can be used for either of the following purposes:

• To indicate whether its associated host variable has been assigned a null value. If the value for the column you are retrieving is null, a negative value is placed in the indicator variable.
• To verify that a retrieved character string value has not been truncated. If the indicator variable contains a positive integer, it specifies the original length of the string.

When a column's value is retrieved, you can test the indicator variable. If the indicator variable's value is less than zero, you know the retrieved column's value is null. When a null value is retrieved, nothing is put into the associated host variable that is used to contain the column's value; thus, the value of the host variable is unchanged.

Define indicator variables in the same manner as small-integer host variables. Specify an indicator variable, preceded by a colon, immediately after the host variable to which it relates. For example:

 EXEC SQL 
     SELECT PHONENO INTO :CBLPHONE :INDNULL FROM CORPDATA 
       WHERE EMPNO = "EMPID" 
 END-EXEC

You can then test INDNULL to see if it contains a negative value. If it does contain a negative value, then you know that a null value was retrieved for the row's PHONENO (that is, phone number) column, and you can disregard the contents of CBLPHONE.

When a column value is fetched using a cursor, you can use the same technique to determine whether the retrieved value is null or not.

You can also use an indicator variable to set a null value in a column. When processing UPDATE statements, the indicator variable is checked. If the indicator variable contains a negative value, set the column value to "null value." If the indicator variable is greater than -1, the associated host variable contains a value for the column.