Data Types (OLE DB Provider for ODBC)

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:

• OLE DB Provider for ODBC