WebSphere Message Brokers
File: ak05730_
Writer: Bill Oppenheimer

Reference topic

This build: July 31, 2007 21:32:27

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.

The 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.

  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 DATETIME, SMALLDATETIME DATE DATETIME
GMTTIMESTAMP        
INTERVAL       INTERVAL
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
Notices | Trademarks | Downloads | Library | Support | Feedback

Copyright IBM Corporation 1999, 2007Copyright IBM Corporation 1999, 2007. All Rights Reserved.
This build: July 31, 2007 21:32:27

ak05730_ This topic's URL is: