Export (0) Print
Expand All

Large CLR User-Defined Types (OLE DB)

This topic discusses the changes to OLE DB in SQL Server Native Client to support large common language runtime (CLR) user-defined types (UDTs).

For more information about support for large CLR UDTs in SQL Server Native Client, see Large CLR User-Defined Types. For a sample, see Use Large CLR UDTs (OLE DB).

SQL Server Native Client uses ~0 to represent the length of values that are of unlimited size for large object (LOB) types. ~0 also represents the size of CLR UDTs that are larger than 8,000 bytes.

The following table shows data type mapping in parameters and rowsets:

SQL Server data type

OLE DB data type

Memory layout

Value

CLR UDT

DBTYPE_UDT

BYTE[](byte array)

132 (oledb.h)

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". A hex string is the textual representation of binary data in base 16. A example is a conversion from server type varbinary(10) to DBTYPE_STR, which results in hexadecimal representation of 20 characters where every pair of characters represents a single byte.

The DBPROPSET_SQLSERVERPARAMETER property set supports UDTs through OLE DB. For more information, see Using User-Defined Types.

The DBPROPSET_SQLSERVERCOLUMN property set supports the creation of tables through OLE DB. For more information, see Using User-Defined Types.

The following information is used in DBCOLUMNDESC structures used by ITableDefinition::CreateTable when UDT columns are required:

OLE DB data type (wType)

pwszTypeName

SQL Server data type

rgPropertySets

DBTYPE_UDT

Ignored

UDT

Must include a DBPROPSET_SQLSERVERCOLUMN property set.

Information returned in the DBPARAMINFO structure through prgParamInfo is as follows:

Parameter type

wType

ulParamSize

bPrecision

bScale

dwFlags DBPARAMFLAGS_ISLONG

DBTYPE_UDT

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

"DBTYPE_UDT"

n

undefined

undefined

clear

DBTYPE_UDT

(length greater than 8,000 bytes)

"DBTYPE_UDT"

~0

undefined

undefined

set

The information provided in the DBPARAMBINDINFO structure must conform to the following:

Parameter type

pwszDataSourceType

ulParamSize

bPrecision

bScale

dwFlags DBPARAMFLAGS_ISLONG

DBTYPE_UDT

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

DBTYPE_UDT

n

ignored

ignored

Must be set if the parameter is to be passed using DBTYPE_IUNKNOWN.

DBTYPE_UDT

(length greater than 8,000 bytes)

DBTYPE_UDT

~0

ignored

ignored

ignored

Applications use ISSCommandWithParameters to get and set the parameter properties defined in the Parameter Properties section.

Columns returned are as follows:

Column type

DBCOLUMN_TYPE

DBCOLUMN_COLUMNSIZE

DBCOLUMN_PRECISION

DBCOLUMN_SCALE

DBCOLUMN_FLAGS_ISLONG

DBCOLUMNS_ISSEARCHABLE

DBCOLUMN_OCTETLENGTH

DBTYPE_UDT

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

DBTYPE_UDT

n

NULL

NULL

Clear

DB_ALL_EXCEPT_LIKE

n

DBTYPE_UDT

(length greater than 8,000 bytes)

DBTYPE_UDT

~0

NULL

NULL

Set

DB_ALL_EXCEPT_LIKE

0

The following columns are also defined for UDTs:

Column Identifier

Type

Description

DBCOLUMN_UDT_CATALOGNAME

DBTYPE_WSTR

For UDT columns, the name of the catalog where the UDT is defined.

DBCOLUMN_UDT_SCHEMANAME

DBTYPE_WSTR

For UDT columns, the name of the schema where the UDT is defined.

DBCOLUMN_UDT_NAME

DBTYPE_WSTR

For UDT columns, the single part name of the UDT.

DBCOLUMN_ASSEMBLY_TYPENAME

DBTYPE_WSTR

For UDT columns, the full type name of the UDT. The fully-qualified name of the assembly type lets you instantiate an object of that type using Type.GetType method.

The information returned in the DBCOLUMNINFO structure is as follows:

Parameter type

wType

ulColumnSize

bPrecision

bScale

dwFlags

DBCOLUMNFLAGS_ISLONG

DBTYPE_UDT

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

DBTYPE_UDT

n

~0

~0

Clear

DBTYPE_UDT

(length greater than 8,000 bytes)

DBTYPE_UDT

~0

~0

~0

Set

The following column values are returned for UDT types:

Column type

DATA_TYPE

COLUMN_FLAGS, DBCOLUMFLAGS_ISLONG

CHARACTER_OCTET_LENGTH

DBTYPE_UDT

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

DBTYPE_UDT

Clear

n

DBTYPE_UDT

(length greater than 8,000 bytes)

DBTYPE_UDT

Set

0

The following additional columns are defined for UDTs:

Column identifier

Type

Description

SS_UDT_CATALOGNAME

DBTYPE_WSTR

For UDT columns, the name of the catalog where the UDT is defined.

SS_UDT_SCHEMANAME

DBTYPE_WSTR

For UDT columns, the name of the schema where the UDT is defined.

SS_UDT_NAME

DBTYPE_WSTR

For UDT columns, the single part name of the UDT.

SS_ASSEMBLY_TYPENAME

DBTYPE_WSTR

For UDT columns, this is the full type name of the UDT. The fully-qualified name of the assembly type lets you instantiate an object of that type using Type.GetType method.

Regarding the PROCEDURE_PARAMETERS rowset, DATA_TYPE contains the same values as the COLUMNS schema rowset and TYPE_NAME contains UDT. The same additional columns are also defined.

User-defined types will not appear in the PROVIDER_TYPES schema rowset.

Binding data tpe

UDT to server

Non-UDT to server

UDT from server

Non-UDT from server

DBTYPE_UDT

Supported (5)

Error (1)

Supported (5)

Error (4)

DBTYPE_BYTES

Supported (5)

N/A

Supported (5)

N/A

DBTYPE_WSTR

Supported (2), (5)

N/A

Supported (3), (5), (6)

N/A

DBTYPE_BSTR

Supported (2), (5)

N/A

Supported (3), (5)

N/A

DBTYPE_STR

Supported (2), (5)

N/A

Supported (3), (5)

N/A

DBTYPE_IUNKNOWN

Supported (6)

N/A

Supported (6)

N/A

DBTYPE_VARIANT (VT_UI1 | VT_ARRAY)

Supported (5)

N/A

Supported (3), (5)

N/A

DBTYPE_VARIANT (VT_BSTR)

Supported (2), (5)

N/A

N/A

N/A

Key to Symbols

Symbol

Meaning

1

If a server type other than DBTYPE_UDT is specified with ICommandWithParameters::SetParameterInfo and the accessor type is DBTYPE_UDT, an error occurs when the statement is executed. The error will be DB_E_ERRORSOCCURRED and the parameter status will be DBSTATUS_E_BADACCESSOR.

It is an error to specify a parameter of type UDT for a server parameter that is not a UDT.

2

Data is converted from hex string to binary data.

3

Data is converted from binary data to hex string.

4

Validation can happen when using CreateAccessor or GetNextRows. The error is DB_E_ERRORSOCCURRED. Binding status is set to DBBINDSTATUS_UNSUPPORTEDCONVERSION.

5

BY_REF may be used.

6

UDT parameters can be bound as DBTYPE_IUNKNOWN in the DBBINDING. Binding to DBTYPE_IUNKNOWN indicates that the application wants to process the data as a stream using the ISequentialStream interface. When a consumer specifies wType in a binding as type DBTYPE_IUNKNOWN, and the corresponding column or output parameter of the stored procedure is a UDT, SQL Server Native Client will return ISequentialStream. For an input parameter, SQL Server Native Client will query for the for the ISequentialStream interface.

You can choose to not bind the length of UDT data while using DBTYPE_IUNKNOWN binding, in the case of large UDTs. However, the length must be bound for small UDTs. A DBTYPE_UDT parameter can be specified as a large UDT if one or more of the following is true:

  • ulParamParamSize is ~0.

  • DBPARAMFLAGS_ISLONG is set in the DBPARAMBINDINFO struct.

For row data, DBTYPE_IUNKNOWN binding is only allowed for large UDTs. You can find out whether a column is a large UDT type by using the IColumnsInfo::GetColumnInfo method on a Rowset or Command object’s IColumnsInfo interface. A DBTYPE_UDT column is a large UDT column if one or more of the following is true:

  • DBCOLUMNFLAGS_ISLONG flag is set on dwFlags member of DBCOLUMNINFO structure

  • ulColumnSize member of DBCOLUMNINFO is ~0.

DBTYPE_NULL and DBTYPE_EMPTY can be bound for input parameters, but not for output parameters or results. When bound for input parameters, the status must be set to DBSTATUS_S_ISNULL for DBTYPE_NULL or DBSTATUS_S_DEFAULT for DBTYPE_EMPTY. DBTYPE_BYREF cannot be used with DBTYPE_NULL or DBTYPE_EMPTY.

DBTYPE_UDT can also be converted to DBTYPE_EMPTY and DBTYPE_NULL. However, DBTYPE_NULL and DBTYPE_EMPTY cannot be converted to DBTYPE_UDT. This is consistent with DBTYPE_BYTES. ISSCommandWithParameters is used to process UDTs as parameters.

Data conversions supplied by OLE DB core services (IDataConvert) are not applicable to DBTYPE_UDT.

No other bindings are supported.

For UDT types, only the following comparisons are supported:

  • EQ

  • NE

  • IGNORE

If any other comparison is attempted DB_E_BADCOMPAREOP is returned.

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

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

Client version

DBTYPE_UDT

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

DBTYPE_UDT

(length greater than 8,000 bytes)

SQL Server 2005

UDT

varbinary(max)

SQL Server 2008 and later

UDT

UDT

When DataTypeCompatibility (SSPROP_INIT_DATATYPECOMPATIBILITY) is set to "80", large UDT types appear to clients in the same way that they appear for down-level clients.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft