If null values (undefined values) are to be processed or truncations are to be recognized when assigning column values to host variables, it is necessary to specify an indicator variable in addition to each host variable. These indicator variables must be declared as (long or short) int in the declare section.
An indicator variable is specified in an SQL statement after the pertinent host variable. It begins, like the host variable, with a colon.
Possible Indicator Values:
When values are transferred from optional columns (i.e., columns where null values may occur) to host variables by a select or fetch statement, an indicator variable must be specified, otherwise a negative sqlcode will be issued and the condition sqlerror will be set when a null value is selected. When precompiling with the option check, the message will appear as a warning at precompilation time.
The indicator variable shows whether a null value has been selected (indicator value is -1). It is also possible to enter a null value into a column by means of the indicator variable. The indicator variable must be set to the value -1 before calling it within an SQL statement. The value in the corresponding host variable will be ignored.
An indicator value > 0 is only set for result host variables. It defines the original column length in the database. Example:
