Indicator Variables in SELECT or FETCH
When a column value is retrieved into a host variable that has an associated indicator variable, the DBMS assigns a value to the indicator variable:
Indicator variable value |
Meaning |
---|---|
-1 |
The value assigned to the host variable was null. The actual content of the host variable is unpredictable. |
0 |
The host variable contains a non-null value that has not been truncated. |
1 or greater |
The host variable contains a truncated value. The value in the indicator variable is the length in bytes of the original untruncated value. |
Retrieving a Null Value
Since a null value is not valid in the program language, the program must test for -1 in the indicator variable and direct processing to handle null value retrieval as needed if the test is true.
Null Retrieval Example
In the following example, the program initializes two numeric host variables to zero:
MOVE ZERO TO VAC-TAKEN. MOVE ZERO TO SICK-TAKEN.
If the next statement now retrieves null values from the VAC_TAKEN and SICK_TAKEN columns, the value of VAC-TAKEN and SICK-TAKEN are still zero because the actual content of the host variables is unchanged when nulls are retrieved:
EXEC SQL SELECT VAC_TAKEN, SICK_TAKEN INTO :VAC-TAKEN INDICATOR :VAC-TAKEN-I, :SICK-TAKEN INDICATOR :SICK-TAKEN-I FROM BENEFITS WHERE EMP_ID = :EMP-ID END-EXEC.
Indicator Variables in Inserts and Updates
When the program issues a statement to store a value contained in a host variable, the statement optionally can name the associated indicator variable.
If the statement names the indicator and the indicator variable value is 0, the DBMS stores the actual content of the host variable. If the indicator variable value is -1, the DBMS stores a null value instead of the actual content of the host variable.
Update Examples With Indicator Variables
In the next example, the program assigns 0 to the indicator variable after changing the value of the host variable VAC-TAKEN. CA IDMS stores the actual content of VAC-TAKEN on the subsequent update:
ADD INPUT-VAC-TAKEN TO VAC-TAKEN. MOVE ZERO TO VAC-TAKEN-I. . . . EXEC SQL UPDATE BENEFITS SET VAC_TAKEN = :VAC-TAKEN INDICATOR :VAC-TAKEN-I WHERE EMP_ID = :EMP-ID END-EXEC.
By omitting reference to the indicator variable in the UPDATE statement, the program can achieve the same result of storing the actual content of the host variable:
ADD INPUT-VAC-TAKEN TO VAC-TAKEN. . . . EXEC SQL UPDATE BENEFITS SET VAC_TAKEN = :VAC-TAKEN WHERE EMP_ID = :EMP-ID END-EXEC.
Similarly, the program can store a null value without naming the indicator variable:
EXEC SQL UPDATE BENEFITS SET VAC_TAKEN = NULL WHERE EMP_ID = :EMP-ID END-EXEC.
Copyright © 2013 CA.
All rights reserved.
|
|