Many SQL-conversant data sources allow a NULL (empty) value in a data field. COBOL and ACUCOBOL-GT do not recognize or support the NULL value. When the value of a field returned from a query is NULL, the contents of the bound variable are undefined. As a result, the ESQL programmer must make provisions for detecting and handling NULL when it is retrieved from or must be stored in the database.
To identify a NULL value, ESQL offers the indicator variable. The indicator variable is a host variable that is used for the special purpose of indicating whether the value of a field is NULL or some other value. In the COBOL program, indicator variables are declared in an SQL DECLARE section of Working-Storage (the same as other host variables). Indicator variables take the form:
01 indicator_name pic s9(5) usage comp-5.
where indicator_name is a user-defined name such as "myind" (used in the example below).
In an SQL statement, the indicator variable is paired with the bound variable and together they indicate a value. In the ESQL statement, the indicator variable immediately follows the bound variable and must be preceded with a colon (":"). The ESQL reserved word "indicator" is optional. For example:
exec sql fetch next from mytable into :myfield indicator :myind ...
The value of indicator is checked to determine if the value of the field is NULL or some other value. For example:
if myind = -1 then <action for null> ...
If the value of indicator is zero, the bound variable contains a valid value.
If the value of indicator is negative, the field contains a NULL value.
If the value of indicator is positive, the bound value contains a value that may have been rounded or truncated.
Consult the ESQL chapter of your SQL reference manual for more information about the use of the indicator variable and the handling of NULL values.