DB2 Version 9.7 for Linux, UNIX, and Windows

User-defined type (UDT) usage in CLI applications

User-defined types (UDTs) are database types defined by the user to provide structure or strong typing not available with conventional SQL types. There are three varieties of UDT: distinct types, structured types, and reference types.

Note: User-defined types (UDTs) are not currently supported by CLI when running with an IDS data server. Using a UDT with an IDS data server will return CLI Error -999 [IBM][CLI Driver][IDS] Not implemented yet.

A CLI application may want to determine whether a given database column is a UDT, and if so, the variety of UDT. The descriptor field SQL_DESC_USER_DEFINED_TYPE_CODE may be used to obtain this information. When SQL_DESC_USER_DEFINED_TYPE_CODE is retrieved using SQLColAttribute() or directly from the IPD using SQLGetDescField(), it will have one of the following numeric values:

   SQL_TYPE_BASE  (this is a regular SQL type, not a UDT)
   SQL_TYPE_DISTINCT  (this value indicates that the column
                             is a distinct type)	 
   SQL_TYPE_STRUCTURED  (this value indicates that the column
                               is a structured type)
   SQL_TYPE_REFERENCE  (this value indicates that the column
                              is a reference type)

Additionally, the following descriptor fields may be used to obtain the type names:

SQL_DESC_REFERENCE_TYPE contains the name of the reference type, or an empty string if the column is not a reference type.
SQL_DESC_STRUCTURED_TYPE contains the name of the structured type, or an empty string if the column is not a structured type.
SQL_DESC_USER_TYPE or SQL_DESC_DISTINCT_TYPE contains the name of the distinct type, or an empty string if the column is not a distinct type.

The descriptor fields listed above return the schema as part of the name. If the schema is less than 8 letters, it is padded with blanks.

The connection attribute SQL_ATTR_TRANSFORM_GROUP allows an application to set the transform group, and is an alternative to the SQL statement SET CURRENT DEFAULT TRANSFORM GROUP.

A CLI application may not wish to repeatedly obtain the value of the SQL_DESC_USER_DEFINED_TYPE_CODE descriptor field to determine if columns contain UDTs. For this reason, there is an attribute called SQL_ATTR_RETURN_USER_DEFINED_TYPES at both the connection and the statement handle level. When set to SQL_TRUE using SQLSetConnectAttr(), CLI returns SQL_DESC_USER_DEFINED_TYPE where you would normally find SQL types in results from calls to SQLColAttribute(), SQLDescribeCol() and SQLGetDescField(). This allows the application to check for this special type, and then do special processing for UDTs. The default value for this attribute is SQL_FALSE.

When the SQL_ATTR_RETURN_USER_DEFINED_TYPES attribute is set to SQL_TRUE, the descriptor field SQL_DESC_TYPE will no longer return the "base" SQL type of the UDT, that is, the SQL type that the UDT is based on or transforms to. For this reason, the descriptor field SQL_DESC_BASE_TYPE will always return the base type of UDTs, and the SQL type of normal columns. This field simplifies modules of a program that do not deal specifically with UDTs that would otherwise have to change the connection attribute.

Note that SQLBindParameter() will not allow you to bind a parameter of the type SQL_USER_DEFINED_TYPE. You must still bind parameters using the base SQL type, which you can obtain using the descriptor field SQL_DESC_BASE_TYPE. For example, here is the SQLBindParameter() call used when binding to a column with a distinct type based on SQL_VARCHAR:

   sqlrc = SQLBindParameter (hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
               SQL_VARCHAR, 30, 0, &c2, 30, NULL);