ODBC data types and how they correspond to DB2 for i database types
The IBM® i Access support for the ODBC driver maps data types between ODBC types and DB2® for i data types.
The following table shows the mappings of the supported data types. Choose the related link below, to the DB2 for i database types, for more information on data types.
| 3.x ODBC Data Type | DB2 for i Database Type |
|---|---|
| SQL_BIGINT | BIGINT |
| SQL_BINARY | BINARY or XML CHAR FOR BIT DATA |
| SQL_CHAR | CHAR or GRAPHIC |
| SQL_DECIMAL | DECIMAL |
| SQL_DOUBLE | DOUBLE |
| SQL_FLOAT | FLOAT |
| SQL_INTEGER | INTEGER |
| SQL_LONGVARBINARY | BLOB |
| SQL_LONGVARCHAR | CLOB or DBCLOB |
| SQL_NUMERIC | NUMERIC |
| SQL_REAL | REAL |
| SQL_SMALLINT | SMALLINT |
| SQL_TYPE_DATE | DATE |
| SQL_TYPE_TIME | TIME |
| SQL_TYPE_TIMESTAMP | TIMESTAMP |
| SQL_VARBINARY | VARBINARY VARCHAR FOR BIT DATA LONG VARCHAR FOR BIT DATA ROWID |
| SQL_VARCHAR | VARCHAR VARGRAPHIC LONG VARCHAR LONG VARGRAPHIC DATALINK DECFLOAT |
| SQL_WCHAR | GRAPHIC CCSID 1200 GRAPHIC CCSID 13488 |
| SQL_WLONGVARCHAR | DBCLOB CCSID 1200 DBCLOB CCSID 13488 |
| SQL_WVARCHAR | VARGRAPHIC CCSID 1200 VARGRAPHIC CCSID 13488 LONGVARGRAPHIC CCSID 1200 LONG VARGRAPHIC CCSID 13488 |
Implementation notes:
- All conversions in the Microsoft ODBC Software Development Kit Programmer's Reference Version 3.5 are supported for these ODBC SQL data types.
- Call the ODBC API SQLGetTypeInfo to learn more about each of these data types.
- The database type of VARCHAR will be changed to LONG VARCHAR by the database if the column size that is specified is larger than 255.
- The ODBC driver does not support any of the interval SQL data types.
- 2.x ODBC applications use the SQL_DATE, SQL_TIME, and SQL_TIMESTAMP defines in place of the SQL_TYPE_DATE, SQL_TYPE_TIME, and SQL_TYPE_TIMESTAMP defines.
- Unicode data which are the data types with a CCSID of 1200 (UTF-16), CCSID of 1208 (UTF-8), or 13488 (UCS-2), report to ODBC 2.x applications as SQL_CHAR, SQL_VARCHAR, and SQL_LONGVARCHAR instead of SQL_WCHAR, SQL_WVARCHAR, and SQL_WLONGVARCHAR.
- LOBs (BLOB, CLOB, and DBCLOB) up to 2 GB in size are supported. For more information on LOBs and datalinks choose the related link below, to the Large Objects (LOBs) considerations topic collection.
- Note that to retrieve decimal fields with large precision successfully you must bind the column as SQL_C_CHAR. The structure that stores SQL_C_NUMERIC data can hold up to 38 digits.