Distinct types in Db2 ODBC applications

You can define your own SQL data type, which is called distinct types. When you create a distinct type, you base it on an existing SQL built-in type. This SQL built-in type is called the source type.

Internally, a distinct type and the source type are equivalent, but for most programming operations a distinct type is incompatible with the source type. You create distinct types with the CREATE DISTINCT TYPE SQL statement.

Distinct types help provide the strong typing control that an object-oriented program requires. When you use distinct types, you ensure that only functions and operators that are explicitly defined on a distinct type can be applied to instances of that type. When you use distinct types, applications continue to work with C data types for application variables. You must consider only the distinct types when you construct SQL statements.

The following guidelines apply to distinct types:

All SQL-to-C data type conversion rules that apply to the source type also apply to the distinct type.
The distinct type has the same default C type as the source type.
SQLDescribeCol() returns the source type for distinct type columns. Call SQLColAttribute() with the input descriptor type set to SQL_DESC_DISTINCT_TYPE to obtain distinct type names.
When you use an SQL predicate that compares a distinct type to a parameter marker, you must either cast the parameter marker to the distinct type or cast the distinct type to a source type. This casting is required because distinct types are not compatible with other data types in comparison operations. Applications use only C data types that represent SQL built-in types. This difference between C types and SQL types requires you to cast from the C built-in type to the SQL distinct type within the SQL statement. Alternatively you can cast the distinct type to a source type, which C types support. If you do not make one of these conversions, an error occurs when you prepare the statement.

The following example shows an application that creates distinct types, user-defined functions, and tables with distinct type columns.

Figure 1. An application that creates distinct types

/* ... */
    /* Initialize SQL statement strings  */
    SQLCHAR         stmt[][MAX_STMT_LEN] = {
        "CREATE DISTINCT TYPE CNUM AS INTEGER WITH COMPARISONS",
        "CREATE DISTINCT TYPE PUNIT AS CHAR(2) WITH COMPARISONS",
        "CREATE DISTINCT TYPE UPRICE AS DECIMAL(10, 2) \
            WITH COMPARISONS",
        "CREATE DISTINCT TYPE PRICE AS DECIMAL(10, 2) \
            WITH COMPARISONS",
        "CREATE FUNCTION PRICE (CHAR(12), PUNIT, char(16) ) \
            returns char(12) \
              NOT FENCED EXTERNAL NAME 'order!price' \
              NOT VARIANT NO SQL  LANGUAGE C PARAMETER STYLE DB2SQL \
              NO EXTERNAL ACTION",
        "CREATE DISTINCT TYPE PNUM AS INTEGER WITH COMPARISONS",
        "CREATE FUNCTION \"+\" (PNUM, INTEGER) RETURNS PNUM \
            source sysibm.\"+\"(integer, integer)",
        "CREATE FUNCTION MAX  (PNUM) RETURNS PNUM \
            source max(integer)",
     "CREATE DISTINCT TYPE ONUM AS INTEGER WITH COMPARISONS",
     "CREATE TABLE CUSTOMER ( \
Cust_Num     CNUM NOT NULL, \
First_Name   CHAR(30) NOT NULL, \
Last_Name    CHAR(30) NOT NULL, \
Street       CHAR(128) WITH DEFAULT, \
City         CHAR(30) WITH DEFAULT, \
Prov_State   CHAR(30) WITH DEFAULT, \
PZ_Code      CHAR(9) WITH DEFAULT, \
Country      CHAR(30) WITH DEFAULT, \
Phone_Num    CHAR(20) WITH DEFAULT, \
PRIMARY KEY  (Cust_Num) )",
        "CREATE TABLE PRODUCT ( \
Prod_Num     PNUM NOT NULL, \
Description  VARCHAR(256) NOT NULL, \
Price        DECIMAL(10,2) WITH DEFAULT , \
Units        PUNIT NOT NULL, \
Combo        CHAR(1) WITH DEFAULT, \
PRIMARY KEY (Prod_Num), \
CHECK (Units in (PUNIT('m'), PUNIT('l'), PUNIT('g'), PUNIT('kg'),  \
PUNIT(' ')))  )",
        "CREATE TABLE PROD_PARTS ( \
Prod_Num     PNUM NOT NULL, \
Part_Num     PNUM NOT NULL, \
Quantity     DECIMAL(14,7), \
PRIMARY KEY (Prod_Num, Part_Num), \
FOREIGN KEY (Prod_Num) REFERENCES Product, \
FOREIGN KEY (Part_Num) REFERENCES Product, \
CHECK (Prod_Num <> Part_Num) )",
        "CREATE TABLE ORD_CUST( \
Ord_Num      ONUM NOT NULL, \
Cust_Num     CNUM NOT NULL, \
Ord_Date     DATE NOT NULL, \
PRIMARY KEY (Ord_Num), \
FOREIGN KEY (Cust_Num) REFERENCES Customer )",
        "CREATE TABLE ORD_LINE( \
Ord_Num      ONUM NOT NULL, \
Prod_Num     PNUM NOT NULL, \
Quantity     DECIMAL(14,7), \
PRIMARY KEY (Ord_Num, Prod_Num), \
FOREIGN KEY (Prod_Num) REFERENCES Product, \
FOREIGN KEY (Ord_Num) REFERENCES Ord_Cust )"
    };
/* ... */
    num_stmts = sizeof(stmt) / MAX_STMT_LEN;
    printf(">Executing stmts);
    /* Execute Direct statements */
    for (i = 0; i < num_stmts; i++) {
        rc = SQLExecDirect(hstmt, stmt[i], SQL_NTS);
    }
/* ... */