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 in the DECLARE SECTION as follows:
An indicator variable is specified in the SQL statement after the corresponding host variable. It begins with a colon like the host variable.
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 CHECK option, the message will appear as a warning at precompilation time.
The indicator variable shows whether a NULL value has been selected (indicator value < 0). 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 a negative value 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:
