Column data types and host variables in embedded SQL applications

Supported embedded SQL host languages have data types that correspond to the majority of the database manager data types. You can use only these host language data types in host variable declarations.

Each table column is given an SQL data type when the column is created. For information about how these types are assigned to columns, see the CREATE TABLE statement.

Note:
  1. Every supported data type can have the NOT NULL attribute. This is treated as another type.
  2. Data types can be extended by defining user-defined distinct types (UDT). UDTs are separate data types that use the representation of one of the built-in SQL types.

Supported embedded SQL 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 this 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, CHAR(SALARY+BONUS) FROM EMPLOYEE
The CAST function used in the preceding example returns a character-string representation of a number.

To convert data within your application, contact your compiler vendor for additional routines, classes, built-in types, or APIs that support this conversion.

If your application code page is not the same as your database code page, character data types can also be subject to character conversion.