Data Type Mapping in Rowsets and Parameters
In rowsets and as parameter values, SQLOLEDB represents Microsoft® SQL Server™ 2000 data by using the following OLE DB defined data types, reported in the functions IColumnsInfo::GetColumnInfo and ICommandWithParameters::GetParameterInfo.
| SQL Server data type | SQLOLEDB data type |
|---|---|
| bigint | DBTYPE_I8 |
| binary | DBTYPE_BYTES |
| bit | DBTYPE_BOOL |
| char | DBTYPE_STR |
| datetime | DBTYPE_DBTIMESTAMP |
| decimal | DBTYPE_NUMERIC |
| float | DBTYPE_R8 |
| image | DBTYPE_BYTES |
| int | DBTYPE_I4 |
| money | DBTYPE_CY |
| nchar | DBTYPE_WSTR |
| ntext | DBTYPE_WSTR |
| numeric | DBTYPE_NUMERIC |
| nvarchar | DBTYPE_WSTR |
| real | DBTYPE_R4 |
| smalldatetime | DBTYPE_DBTIMESTAMP |
| smallint | DBTYPE_I2 |
| smallmoney | DBTYPE_CY |
| sql_variant | DBTYPE_VARIANT, DBTYPE_SQLVARIANT* |
| sysname | DBTYPE_WSTR |
| text | DBTYPE_STR |
| timestamp | DBTYPE_BYTES |
| tinyint | DBTYPE_UI1 |
| uniqueidentifier | DBTYPE_GUID |
| varbinary | DBTYPE_BYTES |
| varchar | DBTYPE_STR |
SQLOLEDB supports consumer-requested data conversions as shown in the illustration.
sql_variant (OLE DB)
The sql_variant data type column can contain any of the data types in SQL Server except large objects (LOBs), such as text, ntext, and image. For example, the column can contain smallint values for some rows, float values for other rows, and char/nchar values in the remainder.
The sql_variant data type is similar to the variant data type in Microsoft Visual Basic® and the DBTYPE_VARIANT in OLE DB.
When sql_variant data is fetched as DBTYPE_VARIANT (defined in Oledb.h), it is put in a VARIANT structure (defined in Oaidl.h) in the buffer. But the subtypes in the VARIANT structure may not map to subtypes defined in the sql_variant data type. The sql_variant data must then be fetched as DBTYPE_SQLVARIANT in order for all the subtypes to match.
DBTYPE_SQLVARIANT Data Type
To support the sql_variant data type, the Microsoft OLE DB Provider for SQL Server (SQLOLEDB) exposes a provider-specific data type called DBTYPE_SQLVARIANT. When sql_variant data is fetched in as DBTYPE_SQLVARIANT (defined in Sqloleb.h), it is stored in a provider-specific SSVARIANT structure (defined in Sqloledb.h). The SSVARIANT structure contains all of the subtypes that match the subtypes of the sql_variant data type.
The session property SSPROP_ALLOWNATIVEVARIANT must also be set to TRUE.
Provider-Specific Property SSPROP_ALLOWNATIVEVARIANT
In fetching data, you can specify explicitly what kind of data type should be returned for a column or for a parameter. IColumnInfo can also be used to get the column information and use that to do the binding. When IColumnInfo is used to obtain column information for binding purposes, if the SSPROP_ALLOWNATIVEVARIANT session property is FALSE (default value), DBTYPE_VARIANT is returned for sql_variant columns. If SSPROP_ALLOWNATIVEVARIANT property is FALSE DBTYPE_SQLVARIANT is not supported. If SSPROP_ALLOWNATIVEVARIANT property is set to TRUE, the column type is returned as DBTYPE_SQLVARIANT, in which case the buffer will hold the SSVARIANT structure. In fetching sql_variant data as DBTYPE_SQLVARIANT, the session property SSPROP_ALLOWNATIVEVARIANT must be set to TRUE.
SSPROP_ALLOWNATIVEVARIANT property is part of the provider-specific DBPROPSET_SQLSERVERSESSION property set, and is a session property.
DBTYPE_VARIANT applies to all other OLE DB providers. DBTYPE_VARIANT is defined in Oledb.h, whereas DBTYPE_SQLVARIANT is specific to SQL Server and is defined in Sqloledb.h.
SSPROP_ALLOWNATIVEVARIANT
SSPROP_ALLOWNATIVEVARIANT is a session property and is part of DBPROPSET, SQLServer Session property set.
| SSPROP_ALLOWNATIVEVARIANT | Type: VT_BOOL R/W: Read/Write Default: VARIANT_FALSE Description: Determines if the data fetched in is as DBTYPE_VARIANT or DBTYPE_SQLVARIANT. VARIANT_TRUE: Column type is returned as DBTYPE_SQLVARIANT in which case the buffer will hold SSVARIANT structure. VARIANT_FALSE: Column type is returned as DBTYPE_VARIANT and the buffer will have VARIANT structure. |