Call Level Interface Guide and Reference

Using User Defined Types (UDT)

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

UDTs help provide the strong typing control needed in object oriented programming by ensuring that only those functions and operators explicitly defined on a distinct type can be applied to its instances. Applications continue to work with C data types for application variables, and only need to consider the UDT types when constructing SQL statements.

This means:

• All SQL to C data type conversion rules that apply to the built-in type apply to the UDT.
• The UDT 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 UDT. 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 UDT; otherwise an error will occur when the statement is prepared. Refer to User Defined Types in Predicates for more information.

For complete rules and a description of user defined types(UDT) refer to the SQL Reference.

User Defined Type Example

This example shows some UDTs and UDFs being defined, as well as some tables with UDT columns. For an example that inserts rows into a table with UDT columns, refer to Array Input Example.

/* ... */
    /* Initialize SQL statement strings */
    SQLCHAR * stmt[] = {
 
        "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 )",
 
        ( char * ) 0,
 
    } ;
 
/* ... */
 
    /* Execute Direct statements */
    i = 0 ;
    while ( stmt[i] != ( char * ) 0 ) {
       printf( ">Executing Statement %ld\n", ( i + 1 ) ) ;
       rc = SQLExecDirect( hstmt, stmt[i], SQL_NTS ) ;
       CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
       i++ ;
    }