Data types of values from external databases
How database data types are implicitly cast to ESQL data types.
The ESQL data type of message fields depends on the type of the message (for example, XML), and the parser that is used to parse it. The ESQL data type of the value returned by a database column reference depends on the data type of the column in the database.
The following table shows how the various built-in database data types are cast to ESQL data types, when they are accessed by message flows that are running in an integration node.
The versions that are supported for the database products shown in this table are listed in IBM® Integration Bus system requirements.
DB2® | SQL Server and Sybase | Oracle | Informix® | ESQL data type |
---|---|---|---|---|
N/A | BIT | N/A | N/A | BOOLEAN |
SMALLINT, INTEGER, BIGINT | INT, SMALLINT, TINYINT | N/A | INT, SMALLINT | INTEGER |
REAL, DOUBLE | FLOAT, REAL | NUMBER()1 | FLOAT, SMALLFLOAT, DOUBLE | FLOAT |
DECIMAL | DECIMAL, NUMERIC, MONEY, SMALLMONEY | NUMBER(P)1, NUMBER(P,S)1 | DECIMAL, MONEY | DECIMAL |
CHAR, VARCHAR, CLOB, GRAPHIC, VARGRAPHIC, LONGVARGRAPHIC, DBCLOB | CHAR, VARCHAR, TEXT | CHAR, VARCHAR2, ROWID, UROWID, LONG, CLOB | CHAR, VARCHAR, CHAR VARYING | CHARACTER |
TIME | N/A | N/A | N/A | TIME |
N/A | N/A | N/A | N/A | GMTTIME |
DATE | N/A | N/A | DATE | DATE |
TIMESTAMP | DATETIME, SMALLDATETIME, TIMESTAMP | DATE | DATETIME | TIMESTAMP |
N/A | N/A | N/A | N/A | GMTTIMESTAMP |
N/A | N/A | N/A | INTERVAL | INTERVAL |
BLOB | BINARY, VARBINARY, IMAGE, UNIQUEIDENTIFIER | RAW LONG, RAW BLOB | N/A | BLOB |
N/A | N/A | N/A | N/A | BIT |
XML | N/A | XML | N/A | BLOB |
The table shows all data types that are supported for each database.
SET OutputRoot.xxx[]
= (SELECT T.department FROM Database.personnel AS T);
where Database.personnel
resolves
to a TINYINT column in an SQL Server database table, results in a
list of ESQL INTEGER values being assigned to OutputRoot.xxx
.
By
contrast, an identical query, where Database.personnel
resolves
to a NUMBER() column in an Oracle database, results in a list of ESQL
FLOAT values being assigned to OutputRoot.xxx
.
Note, that data types of stored procedure parameters are cast using the definition of that external procedure in the CREATE PROCEDURE statement.