Call Level Interface Guide and Reference

User Defined Types in Predicates

Existing applications may be affected if tables are modified to make use of User Defined Types.

If a parameter marker is used in a predicate of a query statement, and the parameter is a user defined type, the statement must use a CAST function to cast either the parameter marker or the UDT.

For example, if the following type and table is defined:

 CREATE DISTINCT TYPE CNUM AS INTEGER WITH COMPARISONS
 
 CREATE TABLE CUSTOMER (
         Cust_Num     CNUM NOT NULL,
         First_Name   CHAR(30) NOT NULL,
         Last_Name    CHAR(30) NOT NULL,
         Phone_Num    CHAR(20) WITH DEFAULT,
         PRIMARY KEY  (Cust_Num) )

The following statement would fail since the parameter marker cannot be of type CNUM and thus the comparison fails due to incompatible types:

  SELECT  first_name, last_name, phone_num FROM customer
  where cust_num = ?

Casting the column to integer (its base SQL type), allows the comparison to work since a parameter can be provided for type integer:

  SELECT  first_name, last_name, phone_num from customer
  where cast( cust_num as integer ) = ?

Alternatively the parameter marker can be cast to INTEGER. This informs the server of the parameter marker's type and allows the default INTEGER to CNUM conversion to be applied:

  SELECT  first_name, last_name, phone_num FROM customer
  where cust_num = cast( ? as integer )

Refer to the custrep.c sample file for a full working example.

Refer to the SQL Reference for more information about:

• UDT, refer to CREATE DISTINCT TYPE
• Parameter markers, refer to the PREPARE statement
• casting, refer to the CAST function.

[ DB2 List of Books | Search the DB2 Books ]