Data types of values from external sources

ESQL can extract data from two external sources:
  • Message fields
  • Database columns

The ESQL data type of message fields depends on the type of the message (XML for example), and the parser 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 DBMS data types are cast to, when they are accessed by WebSphere Message Broker.

Start of changeThe DBMS products are DB2 (version shipped with the product), SQL Server Version 7.0, Sybase Version 12.5.3, Oracle Versions 9i Release 2 and 10G, and Informix Dynamic Server Version 9.4.End of change

  DB2 SQL Server and Sybase Oracle Start of changeInformixEnd of change
BOOLEAN   BIT    
INTEGER SMALLINT, INTEGER, BIGINT INT, SMALLINT, TINYINT   Start of changeINT, SMALLINTEnd of change
FLOAT REAL, DOUBLE FLOAT, REAL NUMBER()1 Start of changeFLOAT, SMALLFLOAT, DOUBLEEnd of change
DECIMAL DECIMAL DECIMAL, NUMERIC, MONEY, SMALLMONEY NUMBER(P)1, NUMBER(P,S)1 Start of changeDECIMAL, MONEYEnd of change
CHARACTER CHAR, VARCHAR, CLOB CHAR, VARCHAR, TEXT CHAR, VARCHAR2, ROWID, UROWID, LONG, CLOB Start of changeCHAR, VARCHAR, CHAR VARYINGEnd of change
TIME TIME      
GMTTIME        
DATE DATE     Start of changeDATEEnd of change
TIMESTAMP TIMESTAMP DATETIME, SMALLDATETIME DATE Start of changeDATETIMEEnd of change
GMTTIMESTAMP        
INTERVAL       Start of changeINTERVALEnd of change
BLOB BLOB BINARY, VARBINARY, TIMESTAMP, IMAGE, UNIQUEIDENTIFIER 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