Data Type Handling in Distributed Queries

OLE DB providers expose their data in terms of the OLE DB-defined data types (indicated by DBTYPE in OLE DB). SQL Server processes external data inside the server as native SQL Server types; this results in a mapping of OLE DB data types to SQL Server native types and vice versa as data is consumed by SQL Server or exported by SQL Server, respectively. This mapping is done implicitly unless otherwise noted.

Data types in distributed queries are handled by using one of two mapping methods:

• Consumption-side mapping maps types from OLE DB data types to SQL Server native data types on the consuming side, when remote tables appear in SELECT statements and on the input side of INSERT, UPDATE, and DELETE statements.
• Export-side mapping maps types from SQL Server data types to OLE DB data types on the exporting side, when a remote table appears as the destination table of an INSERT or UPDATE statement.

* Indicates some form of translation to the SQL Server type’s representation, as there is no exact equivalent data type in SQL Server. Such conversions could result in loss of precision, overflow, or underflow. The default implicit mappings can be changed in the future if the corresponding data types are supported by future SQL Server releases.

Note numeric(p,s) indicates SQL Server data type numeric with precision p and scale s. The maximum allowed precision for DBTYPE_NUMERIC and DBTYPE_DECIMAL is 38. The provider must support binding to the DBTYPE_BSTR column as DBTYPE_WSTR while creating an accessor. DBTYPE_VARIANT columns are consumed as Unicode character strings nvarchar. This requires support for conversion from DBTYPE_VARIANT to DBTYPE_WSTR from the provider. The provider is expected to implement this conversion as defined in OLE DB. For more information, see “OLE DB Interfaces Consumed by SQL Server” later in this chapter.

How to Interpret the Table

The mapping to a SQL Server type is determined by the OLE DB data type and the DBCOLUMNFLAGS values that describe the column or scalar value. In the case of the COLUMNS schema rowset, the DATA_TYPE and COLUMN_FLAGS columns represent these values. In the case of the IColumnsInfo::GetColumnInfo interface, the wType and dwFlags members of the DBCOLUMNINFO structure represent this information.

To use consumption-side mapping for a given column with a specific DBTYPE and DBCOLUMNFLAG value, look for the corresponding SQL Server type in the table. The type rules for columns from remote tables in expressions can be described by the following simple rule:

A given remote column value is legal in a Transact-SQL expression if the corresponding mapped SQL Server type in the table is legal in the same context.

The table and the rule define:

• Comparisons and expressions.

  In general, X <op> <remote-column> is a valid expression if <op> is a valid operator on the data type of X and the data type that <remote-column> maps to.

• Explicit conversions.

  Convert(X, <remote-column>) is allowed if the DBTYPE of <remote-column> maps to native data type Y (as per table above) and explicit conversion from Y to X is allowed.

If users want remote data to be converted to a nondefault native data type, they must use an explicit conversion.

To use export-side mapping in the case of UPDATE and INSERT statements against remote tables, map native SQL Server data types to OLE DB data types using the same table. A mapping from a SQL Server type S1 to a given OLE DB type T is allowed if either of these exist:

• The corresponding mapping can be found in Table 2 directly.
• There is an allowed implicit conversion of S1 to another SQL Server type S2 such that S2 maps to type T in Table 2.

Large Object Handling

As indicated in the table, if columns of the type DBTYPE_STR, DBTYPE_WSTR, or DBTYPE_BSTR also report DBCOLUMNFLAGS_ISLONG, or if their maximum length exceeds 4,000 characters, SQL Server treats them as a text or ntext column as appropriate. Similarly, for DBTYPE_BYTES columns, if DBCOLUMNFLAGS_ISLONG is set or if the maximum length is higher than 8,000 bytes, the columns are treated as image columns.

SQL Server does not expose the full text and image functionality on large objects from an OLE DB provider. TEXTPTRS are not supported on large objects from an OLE DB provider; hence, none of the related functionality is supported, for example, the TEXTPTR system function and READTEXT, WRITETEXT, and UPDATETEXT statements. SELECT statements that retrieve entire large object columns are supported, as are UPDATE and INSERT statements for entire large object columns in remote tables.

For SQL Server to access large object columns through a distributed query, the provider must support at least one of the following structured storage interfaces on the large object in increasing order of preference and functionality: ISequentialStream, Istream, or ILockBytes. Accordingly, the provider must return DBPROPVAL_OO_BLOB as the value of the DBPROP_OLEOBJECTS property when it is queried through the IDBProperties interface. Also, the provider must indicate support for at least one of these interfaces in the DBPROP_STRUCTUREDSTORAGE property.

Accessing Large Object Columns

At query execution, SQL Server performs the following steps to retrieve large object columns:

Before opening the rowset through IOpenRowset::OpenRowset, SQL Server requests support for one or more of the structured storage interfaces (ISequentialStream, Istream, and ILockBytes) on the large object columns. The first interface supported by the provider is required; additional interfaces are requested as “set if cheap” by setting the dwOptions element of the corresponding DBPROP structure to DBPROPOPTIONS_SETIFCHEAP. For example, if a provider supports both ISequentialStream and ILockBytes, ISequentialStream is required and ILockBytes is requested as “set if cheap.”

After the rowset is opened, SQL Server uses IRowsetInfo::GetProperties to identify the actual interfaces available in the rowset. The last or most preferable interface that the provider returned is used. When SQL Server creates an accessor against the large object column, the column is bound as DBTYPE_IUNKNOWN with the iid element of the DBOBJECT structure in the binding set to the interface.

Reading from Large Object Columns

Use the interface pointer for the requested structured storage interface returned in the row buffer from IRowset::GetData to read from the large object column. If the provider does not support multiple open large objects at the same time (that is, if it does not support DBPROP_MULTIPLE_STORAGEOBJECTS) and if the row has multiple large object columns, SQL Server copies the large object columns into a local work table.

UPDATE and INSERT Statements on Large Object Columns

SQL Server passes to the provider a pointer to a new storage object rather than using the provider-supplied interface to modify the storage object. For each large object column, the value that is updated or inserted on a storage object is created with the chosen structured storage interface. Depending on whether it is an UPDATE or an INSERT operation, a pointer to the storage object is passed to the provider through IRowsetChange::SetData or IRowsetChange::InsertRow, respectively.