MDAC 2.5 SDK - OLE DB Providers
OLE DB Provider for ODBC
This section describes data types in the OLE DB Provider for ODBC.
The ODBC Provider binds to the ODBC data source object using the types in the following table. The SQL type is queried using SQLDescribeCol. The sign of the data type (signed/unsigned) is determined using SQLColAttributes. It is used in deciding which C type to use in internal buffers and which type indicator to return through IColumnsInfo::GetColumnInfo.
Note that the GUID mapping is for an OBDC 3.x driver only.
SQL type indicator | Indicator of C type used for internal buffers | OLE DB type indicator |
SQL_CHAR, SQL_VARCHAR, SQL_LONGVARCHAR, SQL_DECIMAL, SQL_NUMERIC | SQL_C_CHAR | DBTYPE_STR |
SQL_GUID | SQL_C_GUID[1] | DBTYPE_GUID |
SQL_BIT | SQL_C_BIT | DBTYPE_BOOL |
SQL_TINYINT, SQL_SMALLINT | SQL_C_USHORT, SQL_C_SSHORT | DBTYPE_I2 |
SQL_INTEGER | SQL_C_ULONG, SQL_C_SLONG | DBTYPE_I4 |
SQL_BIGINT | SQL_C_STR[2]
SQL_C_BIGINT[3] |
DBTYPE_STR |
SQL_REAL | SQL_C_FLOAT | DBTYPE_R4 |
SQL_FLOAT, SQL_DOUBLE | SQL_C_DOUBLE | DBTYPE_R8 |
SQL_BINARY, SQL_VARBINARY, SQL_LONGVARBINARY | SQL_C_BINARY | DBTYPE_BYTES |
SQL_DATE | SQL_C_DATE | DBTYPE_DATE |
SQL_TIME | SQL_C_TIME | DBTYPE_DATE |
SQL_TIMESTAMP | SQL_C_TIMESTAMP | DBTYPE_DBTIMESTAMP |
[1] For ODBC 3.5 drivers only.
[2] This is due to lack of support in the ODBC specification for an SQL_C type for 8-byte integers. For ODBC 3.x drivers, the indicator is SQL_C_BIGINT.
[3] For ODBC 3.x drivers only.
For types DBTYPE_UI1, DBTYPE_I2, DBTYPE_I4, DBTYPE_I8, DBTYPE_R4, DBTYPE_R8, DBTYPE_CY, DBTYPE_NUMERIC, the length binding is always set to the fixed size of the destination binding type, rather than to the internal source type.
Strings are truncated without warning when the destination length is smaller than the source length.
Consumer applications can bind to long data columns by binding to IUnknown and querying for the ISequentialStream interface on the data or by binding to _BYTES or _STR (depending on the underlying data type) to get the data in line. If the consumer chooses to use the ISequentialStream approach, support is limited to one BLOB per accessor.
The ODBC Provider supports STGM_READ on IRowset::GetData, IRowsetChange::InsertRow or IRowsetChange::SetData; STGM_WRITE is not supported for any one of these methods.
To write long data using IRowsetChange::InsertRow or IRowsetChange::SetData, follow these steps:
IRowsetChange::SetData reads the data by calling ISequentialStream::Read on the object until it reaches the end of the stream.
To read long data using IRowset::GetData, follow these steps:
To support long data types, the consumer application must meet at least one of the following conditions:
Long data in a newly inserted row that contains BLOB data columns cannot be read immediately after the row is inserted if the insertion occurred in immediate update mode or if the BLOB data was provided through an ISequentialStream object. The consumer must use IRowset::GetNextRows, IRowsetLocate::GetRowsAt, or IRowsetLocate::GetRowsByBookmark to reposition on the newly inserted row and gain access to the long data columns.
Note If you are using Query-By-Example, you cannot retrieve BLOB data.
Note Attempting to use a storage object on a non-BLOB column can result in a crash or memory corruption; no error will be returned.
The KAGPROP_BLOBSONFOCURSOR property enables users to access BLOB data in a forward-only rowset regardless of the position of the column containing the BLOB data in the column list. If this property is set to VARIANT_TRUE, users can access BLOB data in columns other than the last column in forward-only rowsets; if it set to VARIANT_FALSE, users can access BLOB data only if there are no additional BLOB columns following a BLOB column in the forward-only rowset. If any non-BLOB columns follow a BLOB column in this case, those columns will be inaccessible.
BLOBs can be accessed only from the most recent row. Users must access the BLOB data columns, as well as all other columns, in the ascending column order; data in any columns skipped in the process of traversing the row will be lost. There is no change to the behavior of the columns preceding the first BLOB column; these columns are buffered in the usual manner.
The ODBC Provider supports limited conversions. Only the default types returned in IColumnsInfo are supported, and DBTYPE_BYREF is supported only on variable-length types. Also, some types returned by IColumnsInfo currently cannot be converted to or from DBTYPE_WSTR, which should be valid for all types.
Note Supported data conversions can be determined through the IConvertType interface.
In the following list, click the data type you want to convert from to view the supported conversions:
I1 | R4 | DBTIMESTAMP | IUNKNOWN |
I2 | R8 | BOOL | NULL |
I4 | CY | BSTR | STR |
I8 | DECIMAL | BYTES | VARIANT |
UI1 | NUMERIC | Empty | WSTR |
UI2 | DATE | ERROR | |
UI4 | DBDATE | GUID | |
UI8 | DBTIME | IDISPATCH |
Note When converting DBTYPE_NUMERIC types that correspond to ODBC types SQL_NUMERIC and SQL_DECIMAL, the ODBC Provider exposes additional conversions that are not listed as supported. This occurs because these types are internally bound to SQL_CHAR. Therefore, any conversions supported for CHAR data types in ODBC can be attempted for DBTYPE_NUMERIC types. Some conversions will fail, due to incompatibility, and some will succeed, depending on the data.