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 ESQL 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 the indicator valuable is checked to determine if the value of the field is NULL or some other value. Note that there are no commas between the variable and the indicator.
For input variables, such as those found in an INSERT or UPDATE statement:
| If the value of the indicator variable is ... | Then ... |
|---|---|
| -1 | AcuSQL attempts to set the value of the column to NULL. |
| >=0 | AcuSQL attempts to set the value of the column to that of the host variable, or to the number that appears. This behavior is database dependent. It is recommend that you test your data source to understand the values that are returned. |
For output variables, such as those found in SELECT statements:
| If the value of the indicator variable is ... | Then ... |
|---|---|
| -1 | The selected column’s value is NULL. |
| 0 | The column’s value is assigned to the host variable. |
| >0 | The column’s value is assigned to the host variable, but that value is truncated. The indicator variable is set to the number of characters read by the program. |
Consult the ESQL documentation of your SQL reference manual for more information about the use of the indicator variable and the handling of NULL values.