Determining whether a retrieved value in a host variable is null or truncated

Before your application manipulates the data that was retrieved from Db2 into a host variable, determine if the value is null. Also determine if it was truncated when assigned to the variable. You can use indicator variables to obtain this information.

Before you begin

Before you determine whether a retrieved column value is null or truncated, you must have defined the appropriate indicator variables, arrays, and structures.

About this task

An error occurs if you do not use an indicator variable and Db2 retrieves a null value.

Procedure

To determine whether a retrieved value in a host variable is null or truncated:

Determine the value of the indicator variable, array, or structure that is associated with the host variable, array, or structure.

Those values have the following meanings:

Table 1. Meanings of values in indicator variables
Value of indicator variable	Meaning
Less than zero	The column value is null. The value of the host variable does not change from its previous value. If the indicator variable value is -2, the column value is null because of a numeric or character conversion error,
Zero	The column value is nonnull. If the column value is a character string, the retrieved value is not truncated.
Positive integer	The retrieved value is truncated. The integer is the original length of the string.

Examples

Example of testing an indicator variable

Assume that you have defined the following indicator variable INDNULL for the host variable CBLPHONE.

EXEC SQL
  SELECT PHONENO
    INTO :CBLPHONE:INDNULL
    FROM DSN8C10.EMP
    WHERE EMPNO = :EMPID
END-EXEC.

You can then test INDNULL for a negative value. If the value is negative, the corresponding value of PHONENO is null, and you can disregard the contents of CBLPHONE.

Example of testing an indicator variable array

Suppose that you declare the following indicator array INDNULL for the host-variable array CBLPHONE.

EXEC SQL
  FETCH NEXT ROWSET CURS1
    FOR 10 ROWS 
    INTO :CBLPHONE :INDNULL
END-EXEC.

After the multiple-row FETCH statement, you can test each element of the INDNULL array for a negative value. If an element is negative, you can disregard the contents of the corresponding element in the CBLPHONE host-variable array.

Example of testing an indicator structure in COBOL

The following example defines the indicator structure EMP-IND as an array that contains six values and corresponds to the PEMP-ROW host structure.

01 PEMP-ROW.
    10 EMPNO              PIC X(6).
    10 FIRSTNME.
       49 FIRSTNME-LEN    PIC S9(4) USAGE COMP.
       49 FIRSTNME-TEXT   PIC X(12).
    10 MIDINIT            PIC X(1).
    10 LASTNAME.
       49 LASTNAME-LEN    PIC S9(4) USAGE COMP.
       49 LASTNAME-TEXT   PIC X(15).
    10 WORKDEPT           PIC X(3).
    10 EMP-BIRTHDATE      PIC X(10).
01 INDICATOR-TABLE.
    02 EMP-IND            PIC S9(4) COMP OCCURS 6 TIMES.
⋮
MOVE '000230' TO EMPNO.
⋮
EXEC SQL
  SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, BIRTHDATE
    INTO :PEMP-ROW:EMP-IND
    FROM DSN8C10.EMP
    WHERE EMPNO = :EMPNO
END-EXEC.

You can test the indicator structure EMP-IND for negative values. If, for example, EMP-IND(6) contains a negative value, the corresponding host variable in the host structure (EMP-BIRTHDATE) contains a null value.