Applications written in languages other than Java must prepare for receiving null values by associating an indicator variable with any host variable that can receive a null. Java applications compare the value of the host variable with Java null to determine whether the received value is null. An indicator variable is shared by both the database manager and the host application; therefore, the indicator variable must be declared in the application as a host variable. This host variable corresponds to the SQL data type SMALLINT.
An indicator variable is placed in an SQL statement immediately after the host variable, and is prefixed with a colon. A space can separate the indicator variable from the host variable, but is not required. However, do not put a comma between the host variable and the indicator variable. You can also specify an indicator variable by using the optional INDICATOR keyword, which you place between the host variable and its indicator.
Indicator Variables shows indicator variable usage in the supported host languages using the INDICATOR keyword.
EXEC SQL FETCH C1 INTO :cm INDICATOR :cmind; if ( cmind < 0 ) printf( "Commission is NULL\n" );
#SQL { FETCH :c1 INTO :cm };
if ( cm == null )
System.out.println( "Commission is NULL\n" );
EXEC SQL FETCH C1 INTO :cm INDICATOR :cmind END-EXEC IF cmind LESS THAN 0 DISPLAY 'Commission is NULL'
EXEC SQL FETCH C1 INTO :cm INDICATOR :cmind IF ( cmind .LT. 0 ) THEN WRITE(*,*) 'Commission is NULL' ENDIF
In the figure, cmind is examined for a negative value. If it is not negative, the application can use the returned value of cm. If it is negative, the fetched value is NULL and cm 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 value of cmind may be -2. This indicates a NULL that was 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 indicator variables can be examined for truncation. If an indicator variable has a positive value, a truncation occurred.
When processing INSERT or UPDATE statements, the database manager checks the 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 indicator variable is zero or positive, the database manager uses the value of the associated host variable.
The SQLWARN1 field in the SQLCA structure may contain an 'X' or 'W' if the value of a string column is truncated when it is assigned to a host variable. It 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:
The value returned in the 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 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 data base code page, or nothing). For related information, refer to the SQL Reference.
Each column of every DB2 table is given an SQL data type when the column is created. For information about how these types are assigned to columns, refer to the CREATE TABLE statement in the SQL Reference. The database manager supports the following column data types:
The following data types are supported only in double-byte character set (DBCS) and Extended UNIX Code (EUC) character set environments:
Notes:
Supported host languages have data types that correspond to the majority of the database manager data types. Only these host language data types can be used in host variable declarations. When the precompiler finds a host variable declaration, it determines the appropriate SQL data type value. The database manager uses this value to convert the data exchanged between itself and the application.
As the application programmer, it is important for you to understand how the database manager handles comparisons and assignments between different data types. Simply put, data types must be compatible with each other during assignment and comparison operations, whether the database manager is working with two SQL column data types, two host-language data types, or one of each.
The general rule for data type compatibility is that all supported host-language numeric data types are comparable and assignable with all database manager numeric data types, and all host-language character types are compatible with all database manager character types; numeric types are incompatible with character types. However, there are also some exceptions to this general rule depending on host language idiosyncrasies and limitations imposed when working with large objects.
Within SQL statements, DB2 provides conversions between compatible data types. For example, in the following SELECT statement, SALARY and BONUS are DECIMAL columns; however, each employee's total compensation is returned as DOUBLE data:
SELECT EMPNO, DOUBLE(SALARY+BONUS) FROM EMPLOYEE
Note that the execution of the above statement includes conversion between DECIMAL and DOUBLE data types. To make the query results more readable on your screen, you could use the following SELECT statement:
SELECT EMPNO, DIGIT(SALARY+BONUS) FROM EMPLOYEE
To convert data within your application, contact your compiler vendor for additional routines, classes, built-in types, or APIs that supports this conversion.
Character data types may also be subject to character conversion. If your application code page is not the same as your database code page, see Conversion Between Different Code Pages.
For the list of supported SQL data types and the corresponding host language data types, see the following:
For more information about SQL data types, the rules of assignments and comparisons, and data conversion and conversion errors, refer to the SQL Reference.
The following code segments show the modification to the corresponding segments in the C version of the sample STATIC program, listed in C Example: STATIC.SQC. They show the implementation of indicator variables on data columns that are nullable. In this example, the STATIC program is extended to select another column, WORKDEPT. This column can have a null value. An indicator variable needs to be declared as a host variable before being used.
·
·
·
EXEC SQL BEGIN DECLARE SECTION; char wd[3]; short wd_ind; char firstname[13];
·
·
·
EXEC SQL END DECLARE SECTION;
·
·
·
/* CONNECT TO SAMPLE DATABASE */
·
·
·
EXEC SQL SELECT FIRSTNME, WORKDEPT INTO :firstname, :wd:wdind FROM EMPLOYEE WHERE LASTNAME = 'JOHNSON';
·
·
·