> # Supported Data Types

> Retrieving and sending certain data types can be expensive. Advances in processor technology brought significant improvements to the way that operations such as floating-point math are handled; however, retrieving and sending certain data types are still expensive when the active portion of your application will not fit into on-chip cache. When you are working with data on a large scale, it is still important to select the data type that can be processed most efficiently.

> For example, integer data is processed faster than floating-point data. Floating-point data is defined according to internal database-specific formats, usually in a compressed format. The data must be decompressed and converted into a different format so that it can be processed by the wire protocol.

> Processing time is shortest for character strings, followed by integers, which usually require some conversion or byte ordering. Processing floating-point data and timestamps is at least twice as slow as integers.

> ### DB2 UDB on z/OS

> The following table lists the data types that the ODBC driver supports for DB2 UDB on z/OS:

> Table B-1. Mapping Data Types for DB2 UDB on z/OS to ODBC Data Types
> DB2 UDB Data Type | ODBC Data Type (ANSI)
> --- | ---
> BIGINT | SQL_BIGINT
> BLOB | SQL_LONGVARBINARY
> CHAR(size) | SQL_CHAR
> CHAR(size) FOR BIT DATA | SQL_BINARY
> CLOB | SQL_LONGVARCHAR
> DATE | SQL_DATE
> DBCLOB | SQL_WLONGVARCHAR
> DECIMAL | SQL_DECIMAL
> FLOAT | SQL_FLOAT SQL_DOUBLE
> INTEGER | SQL_INTEGER
> LONGVARCHAR | SQL_LONGVARCHAR
> LONGVARBINARY | SQL_LONGVARBINARY
> NUMERIC | SQL_NUMERIC
> REAL | SQL_REAL
> SMALLINT | SQL_SMALLINT
> TIME | SQL_TIME
> TIMESTAMP | SQL_TIMESTAMP1 SQL_CHAR2
> VARCHAR(size) | SQL_VARCHAR
> VARCHAR(size) FOR BIT DATA | SQL_VARBINARY

> 1Only present when DataSourceFetchTimestampAsString=False. 2Only present when DataSourceFetchTimestampAsString=True.

> Table B-2. Mapping Data Types for DB2 UDB for z/OS to ADO Data Types
> DB2 UDB Data Type | OLE DB Data Type
> --- | ---
> BIGINT | DBTYPE_I8
> BLOB | DBTYPE_BYTES
> CHAR(size) | DBTYPE_STR
> CHAR(size) FOR BIT DATA | DBTYPE_BYTES
> CLOB | DBTYPE_STR
> DATE | DBTYPE_DBDATE
> DECIMAL | DBTYPE_DECIMAL
> FLOAT | DBTYPE_R8
> INTEGER | DBTYPE_I4
> LONG VARCHAR | DBTYPE_STR
> LONG VARCHAR FOR BIT DATA | DBTYPE_BYTES
> NUMERIC | DBTYPE_NUMERIC
> REAL | DBTYPE_R4
> SMALLINT | DBTYPE_I2
> TIME | DBTYPE_DBTIME
> TIMESTAMP | DBTYPE_DBTIMESTAMP1 DBTYPE_STR2
> VARCHAR(size) | DBTYPE_STR
> VARCHAR(size) FOR BIT DATA | DBTYPE_BYTES

> 1Only present when DataSourceFetchTimestampAsString=False. 2Only present when DataSourceFetchTimestampAsString=True.

> ### DB2 UDB on Linux, UNIX, and Windows

> The following table lists the data types that the ODBC driver DB2 UDB on Linux, UNIX, and Windows :

> Table B-3. Mapping Data Types for DB2 UDB on Linux/UNIX/Windows to ODBC Data Types
> DB2 SQL Data Type | ODBC Data Types (Non-Unicode Service) | ODBC Data Types (Unicode Service)
> --- | --- | ---
> BIGINT | SQL_BIGINT | SQL_BIGINT
> BLOB1 | SQL_LONGVARBINARY | SQL_LONGVARBINARY
> CHAR(size) | SQL_CHAR | SQL_CHAR2 SQL_WCHAR
> CHAR(size) FOR BIT DATA | SQL_BINARY | SQL_BINARY
> CLOB1 | SQL_LONGVARCHAR | SQL_WLONGVARCHAR3
> DATE | SQL_DATE | SQL_DATE
> DBCLOB4 | Not supported | SQL_WLONGVARCHAR
> DECIMAL | SQL_DECIMAL | SQL_DECIMAL
> FLOAT | SQL_DOUBLE SQL_FLOAT | SQL_DOUBLE SQL_FLOAT
> GRAPHIC(size)4 | Not supported | SQL_WCHAR
> INTEGER | SQL_INTEGER | SQL_INTEGER
> LONG VARCHAR1 | SQL_LONGVARCHAR | SQL_LONGVARCHAR2
> LONG VARCHAR FOR BIT DATA1 | SQL_LONGVARBINARY | SQL_LONGVARBINARY
> LONG VARGRAPHIC4 | Not supported | SQL_WLONGVARCHAR
> NUMERIC | SQL_NUMERIC | SQL_NUMERIC
> REAL | SQL_REAL | SQL_REAL
> SMALLINT | SQL_SMALLINT | SQL_SMALLINT
> TIME | SQL_TIME | SQL_TIME
> TIMESTAMP | SQL_TIMESTAMP SQL_CHAR5 | SQL_TIMESTAMP SQL_CHAR5
> VARCHAR(size) | SQL_VARCHAR | SQL_VARCHAR2 SQL_WVARCHAR
> VARCHAR(size) FOR BIT DATA | SQL_VARBINARY | SQL_VARBINARY
> VARGRAPHIC(size)4 | Not supported | SQL_WVARCHAR

> 1Depends on setting of the DataSourceLobAsLongvar service attribute. When set to False, LOB data types will be handled as LOB objects; when set to True, LOB data types are handled as Longvar data types. 2If DataSourceReportChar is enabled, the CHAR, VARCHAR, LONGVARCHAR data types are additionally reported as the non-Unicode data types (see previous table) 3Depending on DataSourceReportLobAsLongVarchar: if set to TRUE: LONGVARCHAR data types are reported; if set to FALSE: LOB data types are reported. 4If DataSourceDB2GraphicSupport is enabled, the GRAPHIC datatypes will be available. 5DataSourceFetchTimestampAsString specifies whether timestamps are fetched as character strings.

> ##

> ### Informix

> The following table lists the data types that the ODBC driver supports for Informix:

> Table B-4. Mapping Informix Data Types to ODBC Data Types
> Informix Data Type | ODBC Data Type
> --- | ---
> BLOB | SQL_LONGVARBINARY
> BOOLEAN | SQL_BIT
> BYTE | SQL_LONGVARBINARY
> CHAR | SQL_CHAR
> CLOB | SQL_LONGVARCHAR
> DATE | SQL__TYPE_DATE
> DATETIME HOUR TO SECOND | SQL__TYPE_TIME
> DATETIME YEAR TO FRACTION(5) | SQL__TYPE_TIMESTAMP
> DECIMAL | SQL_DECIMAL
> FLOAT | SQL_FLOAT
> INTEGER | SQL_INTEGER
> INT8 | SQL_BIGINT
> LVARCHAR | SQL_LONGVARCHAR
> MONEY | SQL_DECIMAL
> NUMERIC | SQL_NUMERIC
> SERIAL | SQL_INTEGER
> SERIAL8 | SQL_BIGINT
> SMALLFLOAT | SQL_REAL
> SMALLINT | SQL_SMALLINT
> TEXT | SQL_LONGVARCHAR
> VARCHAR | SQL_VARCHAR

> ### Microsoft SQL Server

> The following table maps the ODBC data types that the ODBC driver supports to the Microsoft SQL Server:

> Table B-5. Mapping Data Types for Microsoft SQL Server to ODBC Data Types
> Microsoft SQL Server 2000, 2005 Data Type | ODBC Data Type
> --- | ---
> bigint | SQL_BIGINT
> bigint identity | SQL_BIGINT
> binary | SQL_BINARY
> bit | SQL_BIT
> char | SQL_CHAR1 SQL_WCHAR2
> datetime | SQL_DATETIME3 SQL_CHAR4
> decimal | SQL_DECIMAL
> decimal identity | SQL_DECIMAL
> float | SQL_FLOAT
> guid | SQL_BINARY
> image | SQL_LONGVARBINARY
> int | SQL_INTEGER
> int identity | SQL_INTEGER
> money | SQL_DECIMAL
> nchar | SQL_CHAR1 SQL_WCHAR2
> ntext | SQL_LONGVARCHAR1 SQL_WLONGVARCHAR2
> nvarchar(max) | SQL_LONGVARCHAR
> numeric | SQL_NUMERIC
> numeric identity | SQL_NUMERIC
> nvarchar | SQL_VARCHAR1 SQL_WVARCHAR2
> real | SQL_REAL
> smalldatetime | SQL_DATETIME3 SQL_CHAR4
> smallint | SQL_SMALLINT
> smallint identity | SQL_SMALLINT
> smallmoney | SQL_DECIMAL
> SQLVariant | SQL_BINARY
> sysname | SQL_VARCHAR1 SQL_WVARCHAR2
> text | SQL_LONGVARCHAR1 SQL_WVARCHAR2
> timestamp | SQL_BINARY
> tinyint | SQL_TINYINT
> tinyint identity | SQL_TINYINT
> uniqueidentifier | SQL_BINARY
> varbinary | SQL_VARBINARY
> varbinary(max) | SQL_LONGVARBINARY
> varchar | SQL_VARCHAR1 SQL_WVARCHAR2
> varchar(max) | SQL_LONGVARCHAR1

> 1Not present when DataSourceReportChar=False and ServiceCodePage=Database 2Only present whe ServiceCodePage=Database 3Only present when DataSourceFetchTimestampAsString=False 4Only present when DataSourceFetchTimestampAsString=True

> ### Oracle

> The following table lists the data types that the ODBC driver supports for Oracle:

> Table B-6. Mapping the Oracle Data Types to ODBC Data Types
> Oracle 9i Data Type | Oracle10g Data Type | ODBC Data Type
> --- | --- | ---
> BFILE | BFILE | SQL_LONGVARBINARY
> | BINARY_FLOAT | SQL_REAL
> | BINARY_DOUBLE | SQL_DOUBLE
> BLOB | BLOB | SQL_LONGVARBINARY
> CHAR(size) | CHAR(size) | SQL_CHAR1 SQL_WCHAR2
> CLOB | CLOB | SQL_LONGVARCHAR3 SQL_WLONGVARCHAR2
> DATE | DATE | SQL_TYPE_TIMESTAMP
> LONG | LONG | SQL_LONGVARCHAR1 SQL_WLONGVARCHAR2
> LONG RAW | LONG RAW | SQL_LONGVARBINARY
> NCHAR(size) | NCHAR(size) | SQL_CHAR4 SQL_WCHAR5
> NCLOB | NCLOB | SQL_LONGVARCHAR4 SQL_WLONGVARCHAR5
> NUMBER | NUMBER | SQL_FLOAT
> NUMBER(p,s) | NUMBER(p,s) | SQL_DECIMAL
> NVARCHAR2(size) | NVARCHAR2(size) | SQL_VARCHAR4 SQL_WVARCHAR5
> RAW(size) | RAW(size) | SQL_VARBINARY
> ROWID | ROWID | SQL_VARCHAR
> TIMESTAMP | TIMESTAMP | SQL_TIMESTAMP6 SQL_CHAR7
> TIMESTAMP WITH LOCAL TIME ZONE | TIMESTAMP WITH LOCAL TIME ZONE | SQL_TYPE_TIMESTAMP6 SQL_CHAR7
> TIMESTAMP WITH TIME ZONE | TIMESTAMP WITH TIME ZONE | SQL_TYPE_TIMESTAMP6, 8, 9
> TIMESTAMP WITH TIME ZONE | TIMESTAMP WITH TIME ZONE | SQL_CHAR7, 8
> VARCHAR2(size) |  | SQL_VARCHAR1
> VARCHAR2(size) |  | SQL_WVARCHAR2

> 1Not present when DataSourceReportChar=False, ServiceCodePage=Database and database character set is Unicode 2Only present when ServiceCodePage=Database and database character set is Unicode 3Not present when ServiceCodePage=Database and database character set is Unicode 4Not present when ServiceCodePage=Database and database character set is Unicode 5Only present when ServiceCodePage=Database and national character set is Unicode 6Only present when DataSourceFetchTimestampAsString=False 7Only present when DataSourceFetchTimestampAsString=True 8Only present when DataSourceORAMapTSWTZ=True 9The time zone value is not accessible (or can not be set). Instead, the client time zone is used either to pass to Oracle or to convert from the stored time zone value.

> ##

> ### Sybase

> The following table lists the data types that the ODBC driver supports for Sybase:

> Table B-7. Mapping the Data Types for Sybase to ODBC Data Types
> Sybase Data Type | ODBC Data Type
> --- | ---
> BIGINT | SQL_BIGINT
> BINARY | SQL_BINARY
> BIT | SQL_BIT
> CHAR | SQL_CHAR1 SQL_WCHAR2
> DATETIME | SQL_TIMESTAMP3 SQL_CHAR4
> DECIMAL | SQL_DECIMAL
> FLOAT | SQL_FLOAT
> IMAGE | SQL_LONGVARBINARY
> INT | SQL_INTEGER
> MONEY | SQL_DECIMAL
> NUMERIC | SQL_NUMERIC
> REAL | SQL_REAL
> SMALLDATETIME | SQL_TIMESTAMP3 SQL_CHAR4
> SMALLINT | SQL_SMALLINT
> SMALLMONEY | SQL_DECIMAL
> SYSNAME | SQL_VARCHAR5 SQL_WVARCHAR2
> TEXT | SQL_LONGVARCHAR1 SQL_WLONGVARCHAR2
> TIMESTAMP | SQL_BINARY
> TINYINT | SQL_TINYINT
> UBIGINT | SQL_DECIMAL
> UNICHAR | SQL_WCHAR6
> UNIVARCHAR | SQL_WVARCHAR6
> VARBINARY | SQL_VARBINARY
> VARCHAR | SQL_VARCHAR1 SQL_WVARCHAR2

> 1Not present when DataSourceReportChar=False and ServiceCodePage=Database 2Only present when ServiceCodePage=Database 3Only present when DataSourceFetchTimestampAsString=False 4Only present when DataSourceFetchTimestampAsString=True 5Not present when ServiceCodePage=Database 6Only present when ServiceCodePage=Database and database character set is UTF-8