Data Types

This section describes data types in the ODBC Provider.

Default Data Type Mappings

The ODBC Provider binds to the ODBC data source using the types in the table below. 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_DATE
1For ODBC 3.5 drivers only.

2This is due to lack of support in the ODBC specification for a 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.

Length Binding for Numerics

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 the internal source type.

String Truncation

Strings are truncated without warning when the destination length is smaller than the source length.

Support for Long Data Types

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:

  1. Set pObject->dwFlags == STGM_READ;
    set pObject->iid==IID_ISequentialStream.
    (Only IID_ISequentialStream is supported; IStream, IStorage, and ILockBytes are not.)

  2. Bind the storage columns. Both IRowsetChange::InsertRow and IRowsetChange::SetData allow binding more than one storage column at a time.

  3. Pass the instantiated ISequentialStream pointer in the pData buffer at the correct offset (specified by obValue).

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:

  1. Set pObject->dwFlags == STGM_READ;
    set pObject->iid==IID_ISequentialStream.
    (Only IID_ISequentialStream is supported; IStream, IStorage, and ILockBytes are not.)

  2. Bind the storage columns. IRowset::GetData permits only one storage column to be bound at a time.  If there is a storage object bound, no other storage objects or long BLOBs can be bound.

  3. The pData buffer would then contain ISequentialStream pointer.

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 may result in a crash or memory corruption; no error will be returned.

Using the KAGPROP_BLOBSONFOCURSOR Property

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.

Data Type Conversion

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 table, click on the data type you want to convert from to view the supported conversions. In the conversion tables, an X indicates that the conversion is supported in the ODBC Provider.

Data types to convert from

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. There are some conversions that will fail, due to incompatibility, and some that will succeed, depending on the data.