Identifying null SQL values with null indicator variables

You must prepare embedded SQL applications for receiving null values by associating a null-indicator variable with any host variable that can receive a null value. A null-indicator variable is shared by both the database manager and the host application. Therefore, you must declare this variable in the application as a host variable, which corresponds to the SQL data type SMALLINT.

About this task

A null-indicator variable is placed in an SQL statement immediately after the host variable, and is prefixed with a colon. A space can separate the null-indicator variable from the host variable, but is not required. However, do not put a comma between the host variable and the null-indicator variable. You can also specify a null-indicator variable by using the optional INDICATOR keyword, which you place between the host variable and its null indicator.

The null-indicator variable is examined for a negative value. If the value is not negative, the application can use the returned value of the host variable. If the value is negative, the fetched value is null and the host variable should not be used. The database manager does not change the value of the host variable in this case.

Note: If the database configuration parameter dft_sqlmathwarn is set to 'YES', the null-indicator variable value may be -2. This value indicates a null that was either caused by evaluating an expression with an arithmetic error, or by an overflow while attempting to convert the numeric result value to the host variable.

If the data type can handle nulls, the application must provide a null indicator. Otherwise, an error may occur. If a null indicator is not used, an SQLCODE -305 (SQLSTATE 22002) is returned.

If the SQLCA structure indicates a truncation warning, the null-indicator variables can be examined for truncation. If a null-indicator variable has a positive value, a truncation occurred.
  • If the seconds' portion of a TIME data type is truncated, the null-indicator value contains the seconds portion of the truncated data.
  • For all other string data types, except large objects (LOB), the null-indicator value represents the actual length of the data returned. User-defined distinct types (UDT) are handled in the same way as their base type.

When processing INSERT or UPDATE statements, the database manager checks the null-indicator variable, if one exists. If the indicator variable is negative, the database manager sets the target column value to null, if nulls are allowed.

If the null-indicator variable is zero or positive, the database manager uses the value of the associated host variable.

The unspecified indicator variable error is returned when applications fetch a result-set that contains NULL values but fail to specify a null indicator. You can avoid the unspecified indicator variable error, even when null indicator is not specified, when you use the precompile options UNSAFENULL YES and COMPATIBILITY_MODE ORA.

Application can check the sqlca.sqlerrd[2] field to get the number of rows that are successfully fetched with the same cursor.

The SQLWARN1 field in the SQLCA structure might contain an X or W if the value of a string column is truncated when it is assigned to a host variable. The field contains an N if a null terminator is truncated.

A value of X is returned by the database manager only if all of the following conditions are met:
  • A mixed code page connection exists where conversion of character string data from the database code page to the application code page involves a change in the length of the data.
  • A cursor is blocked.
  • A null-indicator variable is provided by your application.

The value returned in the null-indicator variable will be the length of the resultant character string in the application's code page.

In all other cases involving data truncation (as opposed to null terminator truncation), the database manager returns a W. In this case, the database manager returns a value in the null-indicator variable to the application that is the length of the resultant character string in the code page of the select list item (either the application code page, the database code page, or nothing).

Before you can use null-indicator variables in the host language, declare the null-indicator variables. In the following example, suitable for C and C++ programs, the null-indicator variable cmind can be declared as: 
    EXEC SQL BEGIN DECLARE SECTION;
      char cm[3];
      short cmind;
    EXEC SQL END DECLARE SECTION;
The following table provides examples for the supported host languages:
Table 1. Null-Indicator Variables by Host Language
Language Example Source Code
C and C++ 
EXEC SQL FETCH C1 INTO :cm INDICATOR :cmind;
if ( cmind < 0 )
   printf( "Commission is NULL\n" );
COBOL 
EXEC SQL FETCH C1 INTO :cm INDICATOR :cmind END-EXEC
IF cmind LESS THAN 0
   DISPLAY 'Commission is NULL'
FORTRAN 
EXEC SQL FETCH C1 INTO :cm INDICATOR :cmind
IF ( cmind .LT. 0 ) THEN
   WRITE(*,*) 'Commission is NULL'
ENDIF
REXX 
   CALL SQLEXEC 'FETCH C1 INTO :cm INDICATOR :cmind'
   IF ( cmind < 0 )
      SAY 'Commission is NULL'