WebSphere Message Brokers
File: ak05810_
Writer: Bill Oppenheimer

Task topic

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

Selecting data from database columns

You can configure a Compute, Filter, or Database node to select data from database columns and include it in an output message. The following example assumes that you have a database table called USERTABLE with two char(6) data type columns (or equivalent), called Column1 and Column2. The table contains two rows:

  Column1 Column2
Row 1 value1 value2
Row 2 value3 value4

Configure the Compute, Filter, or Database node to identify the database in which you have defined the table. For example, if you are using the default database (specified on the data source property of the node), right-click the node, select Open ESQL, and code the following ESQL statements in the module for this node:

SET OutputRoot = InputRoot;
DELETE FIELD OutputRoot.*[<];
SET OutputRoot.XML.Test.Result[] =
  (SELECT T.Column1, T.Column2 FROM Database.USERTABLE AS T);

This produces the following output message:

<Test>
   <Result>
      <Column1>value1</Column1>
      <Column2>value2</Column2>
   </Result>
   <Result>
      <Column1>value3</Column1>
      <Column2>value4</Column2>
   </Result>
</Test>
To trigger the SELECT, send a trigger message with an XML body that is of the following form:
<Test>
   <Result>
      <Column1></Column1>
      <Column2></Column2>
   </Result>
   <Result>
      <Column1></Column1>
      <Column2></Column2>
   </Result>
</Test>

The exact structure of the XML is not important, but the enclosing tag must be <Test> to match the reference in the ESQL. If it is not, the ESQL statements result in top-level enclosing tags being formed, which is not valid XML.

If you want to create an output message that includes all the columns of all the rows that meet a particular condition, use the SELECT statement with a WHERE clause:

-- Declare and initialize a variable to hold the 
--      test vaue (in this case the surname Smith)
DECLARE CurrentCustomer STRING 'Smith';

-- Loop through table records to extract matching information
SET OutputRoot.XML.Invoice[] = 
    (SELECT R FROM Database.USERTABLE AS R
              WHERE R.Customer.LastName = CurrentCustomer
    );

The message fields are created in the same order as the columns appear in the table.

If you are familiar with SQL in a database environment, you might expect to code SELECT *. This is not accepted by the broker because you must start all references to columns with a correlation name. This avoids ambiguities with declared variables. Also, if you code SELECT I.*, this is accepted by the broker but the * is interpreted as the first child element, not all elements, as you might expect from other database SQL.

Assignment of database result sets into a parser-owned message tree requires the result set to exactly match the message definition. Since the Generic XML parser is self-defining, the above example will create a new subtree off the Invoice folder, and the parser will be able to parse these new elements. If the result set structure exactly matches the message definition, then the result set can be assigned directly into the OutputRoot message body tree.

If the result set structure does not exactly match the MRM message definition, then you must first assign the result set into a ROW data type, or an Environment tree that doesn't have any parsers associated with it.

The required data can then be assigned to OutputRoot to build a message tree that conforms to the message definition.

Selecting data from a table in a case sensitive database system

If the database system is case sensitive, you must use an alternative approach. This approach is also necessary if you want to change the name of the generated field to something different:

SET OutputRoot = InputRoot;
SET OutputRoot.XML.Test.Result[] =
  (SELECT T.Column1 AS Column1, T.Column2 AS Column2
  FROM Database.USERTABLE AS T);

This example produces the same message as the example above. Ensure that references to the database columns (in this example, T.Column1 and T.Column2) are specified in the correct case to match the database definitions exactly. If you do not match the database definitions exactly, for example if you specify T.COLUMN1, the broker generates a runtime error. Note the use of Column1 and Column2 in the SELECT statement. You can use any values here, they do not have to match the names of the columns that you have defined in the database as they do in this example.

Selecting bitstream data from a database

These samples show how to retrieve XML bitstream data from a database and include it in an output message. See INSERT statement for examples on how you can insert bitstream data into a database.

In the following example bitstream data is held in a database column with a BLOB data type. If the bitstream from the database does not need to be interrogated or manipulated by the message flow, then the output message can be constructed in the BLOB domain as shown in the following ESQL code:

CALL CopyMessageHeaders();
  SET Environment.Variables.DBResult = THE( SELECT T.* FROM Database.TABLE1 AS T);
  DECLARE resultRef REFERENCE TO Environment.Variables.DBResult;
  IF LASTMOVE(resultRef) THEN
    SET OutputRoot.Properties.CodedCharSetId = resultRef.MSGCCSID;
    SET OutputRoot.Properties.Encoding = resultRef.MSGENCODING;
    SET OutputRoot.BLOB.BLOB = resultRef.MSGDATA;
  END IF;

If you want to interrogate or manipulate a bitstream extracted from a database, you must recreate the original message tree. To recreate the XML message tree from the bitstream you can use a CREATE statement with a PARSE clause. The output message can then be modified by the message flow. The following example shows how to recreate the message tree in the XMLNS domain:

CALL CopyMessageHeaders();
  SET Environment.Variables.DBResult = THE( SELECT T.* FROM Database.TABLE1 AS T);
  DECLARE resultRef REFERENCE TO Environment.Variables.DBResult;
  IF LASTMOVE(resultRef) THEN
    DECLARE outCCSID INT resultRef.MSGCCSID;
    DECLARE outEncoding INT resultRef.MSGENCODING;
    DECLARE outMsg BLOB resultRef.MSGDATA;
    SET OutputRoot.Properties.CodedCharSetId = outCCSID;
    SET OutputRoot.Properties.Encoding = outEncoding;
    CREATE LASTCHILD OF OutputRoot DOMAIN('XMLNS') PARSE(outMsg, outEncoding, outCCSID);
    -- Now modify the message tree fields 
    SET OutputRoot.XMLNS.A.B = 4;
    SET OutputRoot.XMLNS.A.E = 5;
  END IF;

In the following example the data is held in a database column with a character data type, such as CHAR or VARCHAR. A cast is used to convert the data extracted from the database into BLOB format. If the bitstream data from the database does not need to be interrogated or manipulated by the message flow, then the output message can be constructed in the BLOB domain, without any alteration.

CALL CopyMessageHeaders();
  SET Environment.Variables.DBResult = THE( SELECT T.* FROM Database.TABLE1 AS T);
  DECLARE resultRef REFERENCE TO Environment.Variables.DBResult;
  IF LASTMOVE(resultRef) THEN
    DECLARE outCCSID INT resultRef.MSGCCSID;
    DECLARE outMsg BLOB CAST(resultRef.MSGDATA AS BLOB CCSID outCCSID);
    SET OutputRoot.Properties.CodedCharSetId = outCCSID;
    SET OutputRoot.Properties.Encoding = resultRef.MSGENCODING;
    SET OutputRoot.BLOB.BLOB = outMsg;
  END IF;

In the following example the data is held in a database column with a character data type, such as CHAR or VARCHAR. A cast is used to convert the data extracted from the database into BLOB format. In order to manipulate or interrogate this data within the message flow, you must recreate the original message tree. In this example, a CREATE statement with a PARSE clause is used to recreate the XML message tree in the XMLNS domain.

CALL CopyMessageHeaders();
  SET Environment.Variables.DBResult = THE( SELECT T.* FROM Database.TABLE1 AS T);
  DECLARE resultRef REFERENCE TO Environment.Variables.DBResult;
  IF LASTMOVE(resultRef) THEN
    DECLARE outCCSID INT resultRef.MSGCCSID;
    DECLARE outEncoding INT resultRef.MSGENCODING;
    DECLARE outMsg BLOB CAST(resultRef.MSGDATA AS BLOB CCSID outCCSID);
    SET OutputRoot.Properties.CodedCharSetId = outCCSID;
    SET OutputRoot.Properties.Encoding = outEncoding;
    CREATE LASTCHILD OF OutputRoot DOMAIN('XMLNS') PARSE(outMsg, outEncoding, outCCSID);
    -- Now modify the message tree fields 
    SET OutputRoot.XMLNS.A.B = 4;
    SET OutputRoot.XMLNS.A.E = 5;
  END IF;

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:31

ak05810_ This topic's URL is: