Data Type Mapping - Access Module

Teradata® Tools and Utilities Access Module Reference

Product
Access Module
Release Number
17.10
Published
October 2021
Language
English (United States)
Last Update
2021-11-02
dita:mapPath
uur1608578381725.ditamap
dita:ditavalPath
obe1474387269547.ditaval
dita:id
B035-2425
lifecycle
previous
Product Category
Teradata Tools and Utilities

OLE DB uses standard OLE and Windows data types. To describe a data type, an OLE DB type indicator is used, which is a variable of the enumerated type DBTYPE. The Teradata Access Module for OLE DB retrieves the C/C++ data type indicated by the OLE DB type indicator and converts the data type to a Teradata data type. The Teradata data type is based on the DBTYPE and the DBCOLUMNFLAGS values.

Data type mapping is required for transferring data from the Teradata OLE DB providers to the database. The following table lists the mapping of OLE DB type indicators to Teradata data types.

DBTYPE DBCOLUMNFLAG Teradata Data Type
DBTYPE_11   BYTE(n)1
DBTYPE_I2   SMALLINT
DBTYPE_I4   INTEGER
DBTYPE_I8   BIGINT
DBTYPE_UI1   SMALLINT
DBTYPE_UI2   INTEGER
DBTYPE_UI4   DECIMAL(10,0)
DBTYPE_UI8   BIGINT
DBTPYE_R4   FLOAT
DBTYPE_R8   FLOAT
DBTYPE_NUMERIC   DECIMAL(p,s)1

NUMBER(p,s)1

DBTYPE_DECIMAL   DECIMAL(p,s)1
DBTYPE_CY   DECIMAL(19,4) for Precision > 18

DECIMAL(18,4) for Precision ≤ 18

DBTYPE_BSTR   VARCHAR(64000)
DBTYPE_IDISPATCH   BYTE(n)
DBTYPE_ERROR   DECIMAL(10,0)
DBTYPE_BOOL   BYTEINT
DBTYPE_VARIANT   VARCHAR (8000), SMALLINT 4
DBTYPE_IUKNOWN   BYTE(n)2
DBTYPE_GUID   BYTE(n)2
DBTYPE_BYTES DBCOLUMNFLAGS_ISFIXEDLENGTH BYTE(n)2
DBTYPE_BYTES   VARBYTE(n)2
DBTYPE_STR DBCOLUMNFLAGS_ISFIXEDLENGTH PERIOD(DATE)
DBTYPE_STR DBCOLUMNFLAGS_ISFIXEDLENGTH PERIOD[TIME(p)]
DBTYPE_STR DBCOLUMNFLAGS_ISFIXEDLENGTH PERIOD[TIMESTAMP(p)]
DBTYPE_STR DBCOLUMNFLAGS_ISFIXEDLENGTH CHAR(n)2
DBTYPE_STR   VARCHAR(n)2
DBTYPE_WSTR DBCOLUMNFLAGS_ISFIXEDLENGTH CHAR(n)2
DBTYPE_WSTR   VARCHAR(n)2
DBTYPE_UDT   Unsupported
DBTYPE_DATE   TIMESTAMP(p), DATE, & TIME 3
DBTYPE_DBDATE   DATE
DBTYPE_DBTIMESTAMP   TIMESTAMP(p), DATE, & TIME 3
DBTYPE_ARRAY   Unsupported
DBTYPE_BYREF   Indicates the data points to the real data value. For example, DBTYPE_I2 | DBTYPE_BYREF means that the data contains the address of a two-byte integer. All supported types can be referenced by DBTYPE_BYREF.
DBTYPE_VECTOR   Unsupported
DBTYPE_RESERVED   Unsupported
DBTYPE_NULL   Unsupported
DBTYPE_EMPTY   Unsupported
DBTYPE_DBTIME   TIME
DBTYPE_FILETIME   TIMESTAMP(p), DATE, & FLOAT 3
DBTYPE_PROPVARIANT   Unsupported
DBTYPE_HCHAPTER   DECIMAL(10,0)
DBTYPE_VARNUMERIC   VARCHAR(n)2
1precision (p) and scale (s).

2n represents the number of bytes for BYTE(n) and VARBYTE(n), and the number of characters for CHAR(n) and VARCHAR(n).

3All date and time data types are split into a Teradata DATE type for the date portion of the data type, and a Teradata TIME for the time portion [in addition to being available as a Teradata TIMESTAMP(p)].

4All DBTYPE_VARIANT types are split into a Teradata VARCHAR(8000) type for the Value portion of the VARIANT structure, and a Teradata SMALLINT for the VT portion.