Data Types (OLE DB Provider for ODBC)

  • Article

This section describes data types in the OLE DB Provider for ODBC. For information about data type conversions, see Appendix A.

Default Data Type Mappings

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 ODBC 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 DBTYPE_U2

SQL_INTEGER

SQL_C_ULONG SQL_C_SLONG

DBTYPE_I4 DBTYPE_U4

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.

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 to 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 an ISequentialStream pointer.

To support long data types, the consumer application must meet at least one of the following conditions:

  • The OLE DB IRowsetLocate interface is available.

  • SQLExtendedFetch and SQLSetPos(SQL_POSITION) are available in the underlying ODBC driver, and DBPROP_CANHOLDROWS is set to VARIANT_FALSE.

  • If the underlying ODBC cursor is forward-only, BLOB data is available if there are no non-BLOB columns following a BLOB column; or BLOB data is available regardless of position if the KAGPROP_BLOBSONFOCURSOR property is set to VARIANT_TRUE.

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.

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.

This topic is a part of: