ESQL can extract data from two external sources: message fields and database columns.
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 which ESQL data types the various built-in database data types are cast to, when they are accessed by message flows that are running in a broker.
The versions that are supported for the database products shown in this table are listed in Supported databases.
Data type | DB2® | SQL Server and Sybase | Oracle | Informix® |
---|---|---|---|---|
BOOLEAN | BIT | |||
INTEGER | SMALLINT, INTEGER, BIGINT | INT, SMALLINT, TINYINT | INT, SMALLINT | |
FLOAT | REAL, DOUBLE | FLOAT, REAL | NUMBER()1 | FLOAT, SMALLFLOAT, DOUBLE |
DECIMAL | DECIMAL | DECIMAL, NUMERIC, MONEY, SMALLMONEY | NUMBER(P)1, NUMBER(P,S)1 | DECIMAL, MONEY |
CHARACTER | CHAR, VARCHAR, CLOB | CHAR, VARCHAR, TEXT | CHAR, VARCHAR2, ROWID, UROWID, LONG, CLOB | CHAR, VARCHAR, CHAR VARYING |
TIME | TIME | |||
GMTTIME | ||||
DATE | DATE | DATE | ||
TIMESTAMP | TIMESTAMP | ![]() ![]() |
DATE | DATETIME |
GMTTIMESTAMP | ||||
INTERVAL | INTERVAL | |||
BLOB | BLOB | ![]() ![]() |
RAW LONG, RAW BLOB | |
BIT |
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.