TechNet
Export (0) Print
Expand All

Large CLR User-Defined Types (ODBC)

 

System_CAPS_ICON_warning.jpg Warning

SQL Server Native Client (SNAC) is not supported beyond SQL Server 2012. Avoid using SNAC in new development work, and plan to modify applications that currently use it. The Microsoft ODBC Driver for SQL Server provides native connectivity from Windows to Microsoft SQL Server and Microsoft Azure SQL Database.

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.

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 typeSQL data typeValue
CLR UDTSQL_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 typeMemory layoutC data typeValue (sqlext.h)
SQL_SS_UDTSQLCHAR *(unsigned char *)SQL_C_BINARYSQL_BINARY (-2)

Information returned in the IPD fields is as follows:

Descriptor fieldSQL_SS_UDT

(length less than or equal to 8,000 bytes)
SQL_SS_UDT

(length greater than 8,000 bytes)
SQL_DESC_CASE_SENSITIVESQL_FALSESQL_FALSE
SQL_DESC_CONCISE_TYPESQL_SS_UDTSQL_SS_UDT
SQL_DESC_DATETIME_INTERVAL_CODE00
SQL_DESC_DATETIME_INTERVAL_PRECISIONnSQL_SS_LENGTH_UNLIMITED (0)
SQL_DESC_FIXED_PREC_SCALESQL_FALSESQL_FALSE
SQL_DESC_LENGTHnSQL_SS_LENGTH_UNLIMITED (0)
SQL_DESC_LOCAL_TYPE_NAME"udt""udt"
SQL_DESC_OCTET_LENGTHnSQL_SS_LENGTH_UNLIMITED (0)
SQL_DESC_PRECISIONnSQL_SS_LENGTH_UNLIMITED (0)
SQL_DESC_SCALE00
SQL_DESC_TYPESQL_SS_UDTSQL_SS_UDT
SQL_DESC_TYPE_NAME"udt""udt"
SQL_DESC_UNSIGNEDSQL_TRUESQL_TRUE
SQL_CA_SS_UDT_CATALOG_NAMEThe name of the catalog that contains the UDT.The name of the catalog that contains the UDT.
SQL_CA_SS_UDT_SCHEMA_NAMEThe name of the schema that contains the UDT.The name of the schema the contains the UDT.
SQL_CA_SS_UDT_TYPE_NAMEThe name of the UDT.The name of the UDT.
SQL_CA_SS_UDT_ASSEMBLY_TYPE_NAMEThe 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.

Information returned in the IRD fields is as follows:

Descriptor fieldSQL_SS_UDT

(length less than or equal to 8,000 bytes)
SQL_SS_UDT

(length greater than 8,000 bytes)
SQL_DESC_AUTO_UNIQUE_VALUESQL_FALSESQL_FALSE
SQL_DESC_CASE_SENSITIVESQL_FALSESQL_FALSE
SQL_DESC_CONCISE_TYPESQL_SS_UDTSQL_SS_UDT
SQL_DESC_DATETIME_INTERVAL_CODE00
SQL_DESC_DATETIME_INTERVAL_PRECISIONnSQL_SS_LENGTH_UNLIMITED (0)
SQL_DESC_DISPLAY_SIZE2nSQL_SS_LENGTH_UNLIMITED (0)
SQL_DESC_FIXED_PREC_SCALESQL_FALSESQL_FALSE
SQL_DESC_LENGTHnSQL_SS_LENGTH_UNLIMITED (0)
SQL_DESC_LITERAL_PREFIX"0x""0x"
SQL_DESC_LITERAL_SUFFIX""""
SQL_DESC_LOCAL_TYPE_NAME"udt""udt"
SQL_DESC_OCTET_LENGTHnSQL_SS_LENGTH_UNLIMITED (0)
SQL_DESC_PRECISIONnSQL_SS_LENGTH_UNLIMITED (0)
SQL_DESC_SCALE00
SQL_DESC_SEARCHABLESQL_PRED_NONESQL_PRED_NONE
SQL_DESC_TYPESQL_SS_UDTSQL_SS_UDT
SQL_DESC_TYPE_NAME"udt""udt"
SQL_DESC_UNSIGNEDSQL_TRUESQL_TRUE
SQL_CA_SS_UDT_CATALOG_NAMEThe name of the catalog that contains the UDT.The name of the catalog that contains the UDT.
SQL_CA_SS_UDT_SCHEMA_NAMEThe name of the schema that contains the UDT.The name of the schema that contains the UDT.
SQL_CA_SS_UDT_TYPE_NAMEThe name of the UDT.The name of the UDT.
SQL_CA_SS_UDT_ASSEMBLY_TYPE_NAMEThe fully-qualified name of the UDT.The fully-qualified name of the UDT.

The following column values are returned for UDTs:

Column nameSQL_SS_UDT

(length less than or equal to 8,000 bytes)
SQL_SS_UDT

(length greater than 8,000 bytes)
DATA_TYPESQL_SS_UDTSQL_SS_UDT
TYPE_NAMEThe name of the UDT.The name of the UDT.
COLUMN_SIZEnSQL_SS_LENGTH_UNLIMITED (0)
BUFFER_LENGTHnSQL_SS_LENGTH_UNLIMITED (0)
DECIMAL_DIGITSNULLNULL
SQL_DATA_TYPESQL_SS_UDTSQL_SS_UDT
SQL_DATETIME_SUBNULLNULL
CHAR_OCTET_LENGTHnSQL_SS_LENGTH_UNLIMITED (0)
SS_UDT_CATALOG_NAMEThe name of the catalog that contains the UDT.The name of the catalog that contains the UDT.
SS_UDT_SCHEMA_NAMEThe name of the schema that contains the UDT.The name of the schema that contains the UDT.
SS_UDT_ASSEMBLY_TYPE_NAMEThe 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".

The supported conversions from SQL to C datatypes are as follows:

Conversion to and from:SQL_SS_UDT
SQL_C_WCHARSupported *
SQL_C_BINARYSupported
SQL_C_CHARSupported *

* 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_WCHARSupported *
SQL_C_BINARYSupported
SQL_C_CHARSupported *

* Hex string to binary data conversion occurs.

UDTs are not supported in SQL_VARIANT columns.

UDTs values can be imported and exported only as character or binary values.

UDTs are subject to type mapping with down-level clients, as follows:

Server versionSQL_SS_UDT

(length less than or equal to 8,000 bytes)
SQL_SS_UDT

(length greater than 8,000 bytes)
SQL Server 2005UDTvarbinary(max)
SQL Server 2008 and laterUDTUDT

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 typeParametertypeColumnSizePtrDecimalDigitsPtr
SQL_SS_UDT

(length less than or equal to 8,000 bytes)
SQL_SS_UDTn0
SQL_SS_UDT

(length greater than 8,000 bytes)
SQL_SS_UDTSQL_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 typeDataTypePtrColumnSizePtrDecimalDigitsPtr
SQL_SS_UDT

(length less than or equal to 8,000 bytes)
SQL_SS_UDTn0
SQL_SS_UDT

(length greater than 8,000 bytes)
SQL_SS_UDTSQL_SS_LENGTH_UNLIMITED (0)0

SQLDescribeParam

The values returned for UDTs are as follows:

SQL data typeDataTypePtrColumnSizePtrDecimalDigitsPtr
SQL_SS_UDT

(length less than or equal to 8,000 bytes)
SQL_SS_UDTn0
SQL_SS_UDT

(length greater than 8,000 bytes)
SQL_SS_UDTSQL_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 typeTypeSubTypeLengthPrecisionScale
SQL_SS_UDT

(length less than or equal to 8,000 bytes)
SQL_SS_UDT0nn0
SQL_SS_UDT

(length greater than 8,000 bytes)
SQL_SS_UDT0SQL_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 typeTypeSubTypeLengthPrecisionScale
SQL_SS_UDT

(length less than or equal to 8,000 bytes)
SQL_SS_UDT0nn0
SQL_SS_UDT

(length greater than 8,000 bytes)
SQL_SS_UDT0SQL_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.

Large CLR User-Defined Types

Community Additions

ADD
Show:
© 2016 Microsoft