ODBC Data Types

The following tables show the correspondences of ODBC to HCL Enterprise Integrator data types for various types of activities.

  • (p) -- Indicates that if Allow Precision Loss Only is not enabled, then an error will be generated on the type match. Allowing precision loss is the default.
  • (o) -- Indicates that overflow checking will be performed when data is being transferred. If an overflow occurs and Truncate Data When Necessary is enabled, then the data is truncated; if not, an error is generated.

For more information on the organization of these tables, see the section "Notes on Connector Data Type Conversion Tables" in Chapter 3.

Note: When using the HCL Client Access ODBC driver to insert data into the DB2 UDB for IBM i multi-byte data types ETYPE and JTYPE, DBCS data may need to be the exact length of the external type. When storing double-byte character set data to columns of these types, if the error "SQL0406 -- Conversion error on assignment to column" is incorrectly generated, then the data being written should first be padded to the exact length of the DB2 UDB for iSeries column.
Note: Only listed data types are supported.

Execute

Since ODBC data sources may not support all ODBC data types in a table, the Connector for ODBC will begin with the most likely type from the relevant row in the table below and continue until it finds a type supported in the current data source.

ODBC

Connector API

BIT

Int

TINYINT

Int

SMALLINT

Int

INTEGER

Int

BIGINT

Int

REAL

Float

FLOAT

Float

DOUBLE

Float

DECIMAL

prec-scale<=9, scale<=0

Int

DECIMAL

prec <= 15

Float

DECIMAL

Other

Numeric

NUMERIC

prec-scale<=9, scale<=0

Int

NUMERIC

prec <= 15

Float

NUMERIC

other

Numeric

DATE

Datetime

TIME

Datetime

TIMESTAMP

Datetime

CHAR

Text (fixed length, bound <= 254)

VARCHAR

Text (variable length, bound <= 254)

LONGVARCHAR

Text (variable length, unbounded)

BINARY

Binary (fixed length, bound <= 255)

VARBINARY

Binary (variable length, bound <= 255)

LONGVARBINARY

Binary (variable length, unbounded)

Fetch

Note that since ODBC data sources may not support all ODBC data types in a table, the Connector for ODBC will begin with the most likely type from the relevant row in the table below and continue until it finds a type supported in the current data source.

Connector API

ODBC

Int

BIT, TINYINT, SMALLINT, INTEGER, BIGINT (o), REAL (o), FLOAT (p, o), DOUBLE (p, o), DECIMAL (p, o), NUMERIC (p, o)

Float

BIT, TINYINT, SMALLINT, INTEGER, BIGINT (p), REAL, FLOAT, DOUBLE, DECIMAL (p, o), NUMERIC (p, o)

Currency

BIT, TINYINT, SMALLINT, INTEGER, BIGINT (p, o), REAL (o), FLOAT (o), DOUBLE (o), DECIMAL (p, o), NUMERIC (p, o)

Numeric

BIT, TINYINT (p, o), SMALLINT (p, o), INTEGER (p, o), BIGINT (p, o), REAL (p, o), FLOAT (p, o), DOUBLE (p, o), DECIMAL (p, o), NUMERIC (p, o)

Datetime

DATE, TIME, TIMESTAMP

Text (o)

CHAR, VARCHAR, LONGVARCHAR, BINARY, VARBINARY, LONGVARBINARY

Binary (o)

BLOB

CHAR, VARCHAR, LONGVARCHAR, BINARY, VARBINARY, LONGVARBINARY

non-BLOB

Invalid

Insert/Update

Note that since ODBC data sources may not support all ODBC data types in a table, the Connector for ODBC will begin with the most likely type from the relevant row in the table below and continue until it finds a type supported in the current data source.

Connector API

ODBC

Int

BIT (p), TINYINT (p, o), SMALLINT (p, o), INTEGER, BIGINT, REAL (p), FLOAT, DOUBLE, DECIMAL (p), NUMERIC (p)

Float

BIT (p), TINYINT (p, o), SMALLINT (p, o), INTEGER(p, o), BIGINT (p, o), REAL (p, o), FLOAT, DOUBLE, DECIMAL (p), NUMERIC (p)

Currency

BIT (p), TINYINT (p, o), SMALLINT (p, o), INTEGER(p, o), BIGINT (p, o), REAL (p), FLOAT (p, o), DOUBLE (p, o), DECIMAL (p), NUMERIC (p)

Numeric

BIT, TINYINT (p, o), SMALLINT (p, o), INTEGER (p, o), BIGINT (p, o), REAL (p), FLOAT (p), DOUBLE (p), DECIMAL (p, o), NUMERIC (p, o)

Datetime

DATE (p), TIME (o), TIMESTAMP. For Insert/Update into a TIME col, an overflow check is performed, not a precision check.

Text

CHAR (o), VARCHAR (o), LONGVARCHAR, BINARY (o), VARBINARY (o), LONGVARBINARY

Binary

any

CHAR (o), VARCHAR (o), LONGVARCHAR, BINARY (o), VARBINARY (o), LONGVARBINARY

number list

BIT (p), TINYINT (p, o), SMALLINT (p, o), INTEGER(p, o), BIGINT (p, o), REAL (p, o), FLOAT, DOUBLE, DECIMAL (p), NUMERIC (p)

datetime list

DATE (p), TIME (p), TIMESTAMP

Create

Since ODBC data sources may not support all ODBC data types in a table, the Connector for ODBC will begin with the most likely type from the relevant row in the table below and continue until it finds a type supported in the current data source.

Connector API

ODBC

Int

INTEGER, SMALLINT, TINYINT, BIGINT, DECIMAL, or NUMERIC

Float

FLOAT, DOUBLE, REAL, DECIMAL, NUMERIC, or INTEGER

Currency

NUMERIC, DECIMAL, FLOAT, DOUBLE, or INTEGER

Numeric

NUMERIC, DECIMAL, FLOAT, DOUBLE, or INTEGER

Datetime

TIMESTAMP, DATE, or TIME

Text

CHAR (fixed, len<=254), VARCHAR (variable, len<=254), or LONGVARCHAR (len>254)

Binary

BLOB

BINARY(fixed, len<=255), VARBINARY (variable, len<=255), or LONGVARBINARY (len>255)

composite

CHAR (fixed, len<=254), VARCHAR (variable, len<=254), or LONGVARCHAR (len>254)

number list

FLOAT, DOUBLE, REAL, DECIMAL, NUMERIC, or INTEGER

datetime list

TIMESTAMP, DATE, or TIME

text list

CHAR (fixed, len<=254), VARCHAR (variable, len<=254), or LONGVARCHAR (len>254)