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


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


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)