Large CLR User-Defined Types (ODBC)
This topic discusses the changes to ODBC in SQL Server Native Client to support large common language runtime (CLR) user-defined types (UDTs).
For a sample showing ODBC support for large CLR UDTs, see Support for Large UDTs.
For more information about support for large CLR UDTs in SQL Server Native Client, see Large CLR User-Defined Types.
Data Format
SQL Server Native Client uses SQL_SS_LENGTH_UNLIMITED to denote that the size of a column is greater than 8,000 bytes for large object (LOB) types. Beginning with SQL Server 2008, the same value is used for CLR UDTs when their size is greater than 8,000 bytes.
UDT values are represented as byte arrays. Conversions to and from hex strings are supported. Literal values are represented as hex strings with a prefix of "0x".
The following table shows data type mapping in parameters and result sets:
SQL Server data type |
SQL data type |
Value |
|---|---|---|
CLR UDT |
SQL_SS_UDT |
-151 (sqlncli.h) |
The following table discusses the corresponding structure and ODBC C type. Essentially, CLR UDT is a varbinary type with additional metadata.
SQL data type |
Memory layout |
C data type |
Value (sqlext.h) |
|---|---|---|---|
SQL_SS_UDT |
SQLCHAR *(unsigned char *) |
SQL_C_BINARY |
SQL_BINARY (-2) |
Descriptor Fields for Parameters
Information returned in the IPD fields is as follows:
Descriptor field |
SQL_SS_UDT (length less than or equal to 8,000 bytes) |
SQL_SS_UDT (length greater than 8,000 bytes) |
|---|---|---|
SQL_DESC_CASE_SENSITIVE |
SQL_FALSE |
SQL_FALSE |
SQL_DESC_CONCISE_TYPE |
SQL_SS_UDT |
SQL_SS_UDT |
SQL_DESC_DATETIME_INTERVAL_CODE |
0 |
0 |
SQL_DESC_DATETIME_INTERVAL_PRECISION |
n |
SQL_SS_LENGTH_UNLIMITED (0) |
SQL_DESC_FIXED_PREC_SCALE |
SQL_FALSE |
SQL_FALSE |
SQL_DESC_LENGTH |
n |
SQL_SS_LENGTH_UNLIMITED (0) |
SQL_DESC_LOCAL_TYPE_NAME |
"udt" |
"udt" |
SQL_DESC_OCTET_LENGTH |
n |
SQL_SS_LENGTH_UNLIMITED (0) |
SQL_DESC_PRECISION |
n |
SQL_SS_LENGTH_UNLIMITED (0) |
SQL_DESC_SCALE |
0 |
0 |
SQL_DESC_TYPE |
SQL_SS_UDT |
SQL_SS_UDT |
SQL_DESC_TYPE_NAME |
"udt" |
"udt" |
SQL_DESC_UNSIGNED |
SQL_TRUE |
SQL_TRUE |
SQL_CA_SS_UDT_CATALOG_NAME |
The name of the catalog that contains the UDT. |
The name of the catalog that contains the UDT. |
SQL_CA_SS_UDT_SCHEMA_NAME |
The name of the schema that contains the UDT. |
The name of the schema the contains the UDT. |
SQL_CA_SS_UDT_TYPE_NAME |
The name of the UDT. |
The name of the UDT. |
SQL_CA_SS_UDT_ASSEMBLY_TYPE_NAME |
The fully-qualified name of the UDT. |
The fully-qualified name of the UDT. |
For UDT parameters, SQL_CA_SS_UDT_TYPE_NAME must always be set via SQLSetDescField. SQL_CA_SS_UDT_CATALOG_NAME and SQL_CA_SS_UDT_SCHEMA_NAME are optional.
If the UDT is defined in the same database with a different schema than the table, SQL_CA_SS_UDT_SCHEMA_NAME must be set.
If the UDT is defined in a different database than the table, SQL_CA_SS_UDT_CATALOG_NAME and SQL_CA_SS_UDT_SCHEMA_NAME must be set.
If there are any errors or omissions in the settings for SQL_CA_SS_UDT_TYPE_NAME, SQL_CA_SS_UDT_CATALOG_NAME, or SQL_CA_SS_UDT_SCHEMA_NAME, a diagnostic record is generated with SQLSTATE HY000 and server-specific message text.
Descriptor Fields for Results
Information returned in the IRD fields is as follows:
Descriptor field |
SQL_SS_UDT (length less than or equal to 8,000 bytes) |
SQL_SS_UDT (length greater than 8,000 bytes) |
|---|---|---|
SQL_DESC_AUTO_UNIQUE_VALUE |
SQL_FALSE |
SQL_FALSE |
SQL_DESC_CASE_SENSITIVE |
SQL_FALSE |
SQL_FALSE |
SQL_DESC_CONCISE_TYPE |
SQL_SS_UDT |
SQL_SS_UDT |
SQL_DESC_DATETIME_INTERVAL_CODE |
0 |
0 |
SQL_DESC_DATETIME_INTERVAL_PRECISION |
n |
SQL_SS_LENGTH_UNLIMITED (0) |
SQL_DESC_DISPLAY_SIZE |
2n |
SQL_SS_LENGTH_UNLIMITED (0) |
SQL_DESC_FIXED_PREC_SCALE |
SQL_FALSE |
SQL_FALSE |
SQL_DESC_LENGTH |
n |
SQL_SS_LENGTH_UNLIMITED (0) |
SQL_DESC_LITERAL_PREFIX |
"0x" |
"0x" |
SQL_DESC_LITERAL_SUFFIX |
"" |
"" |
SQL_DESC_LOCAL_TYPE_NAME |
"udt" |
"udt" |
SQL_DESC_OCTET_LENGTH |
n |
SQL_SS_LENGTH_UNLIMITED (0) |
SQL_DESC_PRECISION |
n |
SQL_SS_LENGTH_UNLIMITED (0) |
SQL_DESC_SCALE |
0 |
0 |
SQL_DESC_SEARCHABLE |
SQL_PRED_NONE |
SQL_PRED_NONE |
SQL_DESC_TYPE |
SQL_SS_UDT |
SQL_SS_UDT |
SQL_DESC_TYPE_NAME |
"udt" |
"udt" |
SQL_DESC_UNSIGNED |
SQL_TRUE |
SQL_TRUE |
SQL_CA_SS_UDT_CATALOG_NAME |
The name of the catalog that contains the UDT. |
The name of the catalog that contains the UDT. |
SQL_CA_SS_UDT_SCHEMA_NAME |
The name of the schema that contains the UDT. |
The name of the schema that contains the UDT. |
SQL_CA_SS_UDT_TYPE_NAME |
The name of the UDT. |
The name of the UDT. |
SQL_CA_SS_UDT_ASSEMBLY_TYPE_NAME |
The fully-qualified name of the UDT. |
The fully-qualified name of the UDT. |
Column Metadata Returned by SQLColumns and SQLProcedureColumns (Catalog Metadata)
The following column values are returned for UDTs:
Column name |
SQL_SS_UDT (length less than or equal to 8,000 bytes) |
SQL_SS_UDT (length greater than 8,000 bytes) |
|---|---|---|
DATA_TYPE |
SQL_SS_UDT |
SQL_SS_UDT |
TYPE_NAME |
The name of the UDT. |
The name of the UDT. |
COLUMN_SIZE |
n |
SQL_SS_LENGTH_UNLIMITED (0) |
BUFFER_LENGTH |
n |
SQL_SS_LENGTH_UNLIMITED (0) |
DECIMAL_DIGITS |
NULL |
NULL |
SQL_DATA_TYPE |
SQL_SS_UDT |
SQL_SS_UDT |
SQL_DATETIME_SUB |
NULL |
NULL |
CHAR_OCTET_LENGTH |
n |
SQL_SS_LENGTH_UNLIMITED (0) |
SS_UDT_CATALOG_NAME |
The name of the catalog that contains the UDT. |
The name of the catalog that contains the UDT. |
SS_UDT_SCHEMA_NAME |
The name of the schema that contains the UDT. |
The name of the schema that contains the UDT. |
SS_UDT_ASSEMBLY_TYPE_NAME |
The fully-qualified name of the UDT. |
The fully-qualified name of the UDT. |
The last three columns are driver-specific columns. They are added after any ODBC-defined columns, but before any existing driver-specific columns of the result set of SQLColumns or SQLProcedureColumns.
No rows are returned by SQLGetTypeInfo, for individual UDTs or for the generic type "udt".
Bindings and Conversions
The supported conversions from SQL to C datatypes are as follows:
Conversion to and from: |
SQL_SS_UDT |
|---|---|
SQL_C_WCHAR |
Supported * |
SQL_C_BINARY |
Supported |
SQL_C_CHAR |
Supported * |
* Binary data is converted to a hex string.
The supported conversions from C to SQL datatypes are as follows:
Conversion to and from: |
SQL_SS_UDT |
|---|---|
SQL_C_WCHAR |
Supported * |
SQL_C_BINARY |
Supported |
SQL_C_CHAR |
Supported * |
* Hex string to binary data conversion occurs.
SQL_VARIANT Support for UDTs
UDTs are not supported in SQL_VARIANT columns.
BCP Support for UDTs
UDTs values can be imported and exported only as character or binary values.
Downlevel Client Behavior for UDTs
UDTs are subject to type mapping with down-level clients, as follows:
Server version |
SQL_SS_UDT (length less than or equal to 8,000 bytes) |
SQL_SS_UDT (length greater than 8,000 bytes) |
|---|---|---|
SQL Server 2005 |
UDT |
varbinary(max) |
SQL Server 2008 and later |
UDT |
UDT |
ODBC Functions Supporting Large CLR UDTs
This section discusses changes to SQL Server Native Client ODBC functions to support large CLR UDTs.
SQLBindCol
UDT result column values are converted from SQL to C datatypes as as described in the "Bindings and Conversions" section, earlier in this topic.
SQLBindParameter
The values required for UDTs are as follows:
SQL data type |
Parametertype |
ColumnSizePtr |
DecimalDigitsPtr |
|---|---|---|---|
SQL_SS_UDT (length less than or equal to 8,000 bytes) |
SQL_SS_UDT |
n |
0 |
SQL_SS_UDT (length greater than 8,000 bytes) |
SQL_SS_UDT |
SQL_SS_LENGTH_UNLIMITED (0) |
0 |
SQLColAttribute
The values returned for UDTs are as described in the "Descriptor Fields for Results" section, earlier in this topic.
SQLColumns
The values returned for UDTs are as described in the "Column Metadata Returned by SQLColumns and SQLProcedureColumns (Catalog Metadata)" section, earlier in this topic.
SQLDescribeCol
The values returned for UDTs are as follows:
SQL data type |
DataTypePtr |
ColumnSizePtr |
DecimalDigitsPtr |
|---|---|---|---|
SQL_SS_UDT (length less than or equal to 8,000 bytes) |
SQL_SS_UDT |
n |
0 |
SQL_SS_UDT (length greater than 8,000 bytes) |
SQL_SS_UDT |
SQL_SS_LENGTH_UNLIMITED (0) |
0 |
SQLDescribeParam
The values returned for UDTs are as follows:
SQL data type |
DataTypePtr |
ColumnSizePtr |
DecimalDigitsPtr |
|---|---|---|---|
SQL_SS_UDT (length less than or equal to 8,000 bytes) |
SQL_SS_UDT |
n |
0 |
SQL_SS_UDT (length greater than 8,000 bytes) |
SQL_SS_UDT |
SQL_SS_LENGTH_UNLIMITED (0) |
0 |
SQLFetch
UDT result column values are converted from SQL to C datatypes as as described in the "Bindings and Conversions" section, earlier in this topic.
SQLFetchScroll
UDT result column values are converted from SQL to C datatypes as as described in the "Bindings and Conversions" section, earlier in this topic.
SQLGetData
UDT result column values are converted from SQL to C datatypes as as described in the "Bindings and Conversions" section, earlier in this topic.
SQLGetDescField
Descriptor fields available with the new types are described in the "Descriptor Fields for Parameters" and "Descriptor Fields for Results" sections, earlier in this topic.
SQLGetDescRec
The values returned for UDTs are as follows:
SQL data type |
Type |
SubType |
Length |
Precision |
Scale |
|---|---|---|---|---|---|
SQL_SS_UDT (length less than or equal to 8,000 bytes) |
SQL_SS_UDT |
0 |
n |
n |
0 |
SQL_SS_UDT (length greater than 8,000 bytes) |
SQL_SS_UDT |
0 |
SQL_SS_LENGTH_UNLIMITED (0) |
SQL_SS_LENGTH_UNLIMITED (0) |
0 |
SQLGetTypeInfo
The values returned for UDTs are as described in the "Metadata Returned by SQLColumns and SQLProcedureColumns (Catalog Metadata)" section, earlier in this topic.
SQLProcedureColumns
The values returned for UDTs are as described in the "Metadata Returned by SQLColumns and SQLProcedureColumns (Catalog Metadata)" section, earlier in this topic.
SQLPutData
UDT parameter values are converted from C to SQL datatypes as as described in the "Bindings and Conversions" section, earlier in this topic.
SQLSetDescField
Descriptor field available with the new types are described in the "Descriptor Fields for Parameters" and "Descriptor Fields for Results" sections, earlier in this topic.
SQLSetDescRec
The values allowed for UDTs are as follows:
SQL data type |
Type |
SubType |
Length |
Precision |
Scale |
|---|---|---|---|---|---|
SQL_SS_UDT (length less than or equal to 8,000 bytes) |
SQL_SS_UDT |
0 |
n |
n |
0 |
SQL_SS_UDT (length greater than 8,000 bytes) |
SQL_SS_UDT |
0 |
SQL_SS_LENGTH_UNLIMITED (0) |
SQL_SS_LENGTH_UNLIMITED (0) |
0 |
SQLSpecialColumns
The values returned for the columns DATA_TYPE, TYPE_NAME, COLUMN_SIZE, BUFFER_LENGTH, and DECIMAL_DIGTS UDTs are as described in the "Metadata Returned by SQLColumns and SQLProcedureColumns (Catalog Metadata)" section, earlier in this topic.