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.
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) |