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.

If an Oracle database column with NUMBER data type is defined with an explicit precision (P) and scale (S), it is cast to an ESQL DECIMAL value; otherwise it is cast to a FLOAT. For example, an ESQL statement like this:
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.