Supported SQL data types in OLE DB
The database data types map to the OLE DB data types.
The following table shows how the database data types map to the OLE DB data types as described in Microsoft OLE DB 2.0 Programmer's Reference and Data Access SDK, Microsoft Press, 1998. Use the mapping table to define the appropriate RETURNS TABLE columns in your OLE DB table functions.
For mappings of OLE DB provider source data types to OLE DB data types, refer to the OLE DB provider documentation. For examples of how the ANSI SQL, Microsoft Access, and Microsoft SQL Server providers might map their respective data types to OLE DB data types, refer to the Microsoft OLE DB 2.0 Programmer's Reference and Data Access SDK, Microsoft Press, 1998.
| Database Data Type | OLE DB Data Type |
|---|---|
| SMALLINT | DBTYPE_I2 |
| INTEGER | DBTYPE_I4 |
| BIGINT | DBTYPE_I8 |
| REAL | DBTYPE_R4 |
| FLOAT/DOUBLE | DBTYPE_R8 |
| DEC (p, s) | DBTYPE_NUMERIC (p, s) |
| DATE | DBTYPE_DBDATE |
| TIME | DBTYPE_DBTIME |
| TIMESTAMP | DBTYPE_DBTIMESTAMP |
| CHAR(N) | DBTYPE_STR |
| VARCHAR(N) | DBTYPE_STR |
| LONG VARCHAR | DBTYPE_STR |
| CLOB(N) | DBTYPE_STR |
| CHAR(N) FOR BIT DATA | DBTYPE_BYTES |
| VARCHAR(N) FOR BIT DATA | DBTYPE_BYTES |
| LONG VARCHAR FOR BIT DATA | DBTYPE_BYTES |
| BLOB(N) | DBTYPE_BYTES |
| GRAPHIC(N) | DBTYPE_WSTR |
| VARGRAPHIC(N) | DBTYPE_WSTR |
| LONG GRAPHIC | DBTYPE_WSTR |
| DBCLOB(N) | DBTYPE_WSTR |
Note: OLE DB data type conversion rules are defined in the Microsoft OLE DB 2.0 Programmer's
Reference and Data Access SDK, Microsoft Press, 1998. For example:
- To retrieve the OLE DB data type DBTYPE_CY, the data can get converted to OLE DB data type DBTYPE_NUMERIC(19,4), which maps to the database data type DEC(19,4).
- To retrieve the OLE DB data type DBTYPE_I1, the data can get converted to OLE DB data type DBTYPE_I2, which maps to the database data type SMALLINT.
- To retrieve the OLE DB data type DBTYPE_GUID, the data can get converted to OLE DB data type DBTYPE_BYTES, which maps to the database data type CHAR(12) FOR BIT DATA.