Data types of values from external sources

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 Start of changeDATETIME, SMALLDATETIME, TIMESTAMPEnd of change DATE DATETIME
GMTTIMESTAMP        
INTERVAL       INTERVAL
BLOB BLOB Start of changeBINARY, VARBINARY, IMAGE, UNIQUEIDENTIFIEREnd of change RAW LONG, RAW BLOB  
BIT        
Note:
  1. 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.

Related concepts
ESQL overview
Related tasks
Developing ESQL
Related reference
Support for Unicode and DBCS data in databases
Complex ESQL functions
CAST function
Supported casts
Implicit casts
Supported databases
Notices | Trademarks | Downloads | Library | Support | Feedback

Copyright IBM Corporation 1999, 2009Copyright IBM Corporation 1999, 2009.
Last updated : 2009-01-07 15:22:23

ak05730_