The examples above do not handle null values. In fact, the retrieval examples will raise an error if they fetch a null value from the database. To be able to pass null values to the database or retrieve null values from the database, you need to append a second host variable specification to each host variable that contains data. This second host variable is called the indicator and contains a flag that tells whether the datum is null, in which case the value of the real host variable is ignored. Here is an example that handles the retrieval of null values correctly:
EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 VAL PIC X(50) VARYING. 01 VAL_IND PIC S9(9) COMP-5. EXEC SQL END DECLARE SECTION END-EXEC. ... EXEC SQL SELECT b INTO :VAL :VAL_IND FROM test1 END-EXEC.
The indicator variable val_ind will be zero if the value was not null, and it will be negative if the value was null.
The indicator has another function: if the indicator value is positive, it means that the value is not null, but it was truncated when it was stored in the host variable.
