Distinct type usage in CLI applications

Distinct types help provide the strong typing control that is required in object-oriented programming. Distinct types ensure that only functions and operators explicitly defined on a distinct type can be applied to its instances.

In addition to SQL data types (referred to as base SQL data types), new distinct types can be defined by the user. This variety of user defined types (UDTs) shares its internal representation with an existing type, but is considered to be a separate and incompatible type for most operations. Distinct types are created using the CREATE DISTINCT TYPE SQL statement.

Applications continue to work with C data types for application variables, and only need to consider the distinct types when constructing SQL statements.

This means:
  • All SQL to C data type conversion rules that apply to the built-in type apply to distinct types.
  • Distinct types will have the same default C Type as the built-in type.
  • SQLDescribeCol() will return the built-in type information. The user defined type name can be obtained by calling SQLColAttribute() with the input descriptor type set to SQL_DESC_DISTINCT_TYPE.

  • SQL predicates that involve parameter markers must be explicitly cast to the distinct type. This is required since the application can only deal with the built-in types, so before any operation can be performed using the parameter, it must be cast from the C built-in type to the distinct type; otherwise an error will occur when the statement is prepared.