DataBase Interaction Uses PASSTHRU statement in Compute Node

The main use of the PASSTHRU statement is to issue administrative commands to databases (for example, to create a table).

Note: Do not use PASSTHRU to call stored procedures; instead, use the CALL statement because PASSTHRU imposes limitations (you cannot use output parameters, for example).Uses specified ODBC data source Name.

- Only DDL Statements (CREATE, DROP , ALTER) requires PASSTHRU in a compute node.

Examples :

The following example creates the table Customers in schema Shop in database DSN1:

PASSTHRU 'CREATE TABLE Shop.Customers (  CustomerNumber INTEGER,  FirstName      VARCHAR(256),  LastName       VARCHAR(256),  Street         VARCHAR(256),  City           VARCHAR(256),  Country        VARCHAR(256) )' TO Database.DSN1;

If, as in the last example, the ESQL statement is specified as a string literal, you must put single quotation marks around it. If, however, it is specified as a variable, omit the quotation marks.

For example:

SET myVar = 'SELECT * FROM user1.stocktable'; SET OutputRoot.XMLNS.Data[] = PASSTHRU(myVar);

The following example "drops" (that is, deletes) the table Customers from schema Shop in database DSN1:

PASSTHRU 'DROP TABLE Shop.Customers' TO Database.DSN1;

LOGICAL TREE STRUCTURE

The logical tree structure is the internal (broker) representation of a message. It is also known as the message assembly.

When a message arrives at a broker, it is received by an input node that you have configured in a message flow. Before the message can be processed by the message flow, the message must be interpreted by one or more parsers that create a logical tree representation from the bit stream of the message data.

                                          

The input node creates this message assembly, which consists of four trees:

1. Message tree structure
2. Environment tree structure
3. Local environment tree structure
4. Exception list tree structure

-The input node passes the message assembly that it has created to subsequent message processing nodes in the message flow:

• All message processing nodes can read the four trees.
• You can code ESQL in the Database and Filter nodes, or use mappings in the nodes that support that interface to modify the Environment and LocalEnvironment trees only.

The Compute node differs from other nodes in that it has both an input message assembly and at least one output message assembly. Configure the Compute node to determine which trees are included in the output message assembly; the Environment tree is an exception in that it is always retained from input message assembly to output message assembly.

To determine which of the other trees are included, you must specify a value for the Compute mode property of the node (displayed on the Advanced tab). The default action is for only the message to be created. You can specify any combination of message, LocalEnvironment, and ExceptionList trees to be created in the output message assembly.

• If you want the output message assembly to contain a complete copy of the input message tree, you can code a single ESQL SET statement to make the copy. If you want the output message to contain a subset of the input message tree, code ESQL to copy those parts that you want. In both cases, your choice of Compute mode must include Message.
• If you want the output message assembly to contain all or part of the input LocalEnvironment or ExceptionList tree contents, code the appropriate ESQL to copy information you want to retain in that tree. Your choice of Compute mode must include LocalEnvironment, or Exception, or both.
• You can also code ESQL to populate the output message, Environment, LocalEnvironment, or ExceptionList tree with information that is not copied from the input tree. For example, you can retrieve data from a database, or calculate content from the input message data.

1. Message tree structure :

The message tree is a part of the logical message tree in which the broker stores its internal representation of the message body.

• Properties :The Properties folder is the first element of the message tree and holds information about the characteristics of the message like;

           -  Message Set

           -  Message Type

           -  Message Format

           -  Transactionality

           -  Persistence

           -  Priority

• MQMD : Defines Message Headers like;

           -  Source Queue

           -  Message Id

           - Correlation Id

           - User Id

           - Reply To Q

           - Reply To QM

           - Put time

           - Put Date

• Body(XMLNSC) :The Body tree is a structure of child elements that represents the message content (data), and reflects the logical structure of that content.

 2. Environment tree structure :

Scope: Single instance of it is maintained throughout the message flow

- The entire contents of the input environment tree are retained in the output environment tree, subject to any modifications that you make in the node. Any changes that you make are available to subsequent nodes in the message flow, and to previous nodes if the message flows back (for example, to a FlowOrder or TryCatch node).

You could use the following ESQL statements to create the content shown above.

SET Environment.Variables =      ROW('granary' AS bread, 'riesling' AS wine, 'stilton' AS cheese); SET Environment.Variables.Colors[] =      LIST{'yellow', 'green', 'blue', 'red', 'black'}; SET Environment.Variables.Country[] = LIST{ROW('UK' AS name, 'pound' AS currency),       ROW('USA' AS name, 'dollar' AS currency)};

3. Local environment tree structure :

Scope : Instance of it is maintained till the next node of the message flow

- The local environment tree to store variables that can be referred to and updated by message processing nodes that occur later(next node) in the message flow.

- You can also use the local environment tree to define destinations (that are internal and external to the message flow) to which a message is sent.

Example:

LocalEnvironment.Destination.MQ LocalEnvironment.Destination.DestinationList LocalEnvironment.Destination.File LocalEnvironment.Destination.Email

4. Exception list tree structure :

 

- The exception list tree is a part of the logical message tree in which the message flow writes information about exceptions that occur when a message is processed.

- If an exception condition occurs, message processing is suspended and an exception is thrown. Control is passed back to a higher level; that is, an enclosing catch block. An exception list is built to describe the failure condition, and the whole message, together with the local environment tree, and the newly-populated exception list, is propagated through an exception-handling message flow path.

-The child of ExceptionList is always RecoverableException. Typically, only one child of the root is created, although more than one might be generated in some circumstances. The child of ExceptionList contains a number of children, the last of which provides further information specific to the type of exception. The following list includes some of the exception types that you might see:

FatalException RecoverableException ConfigurationException SecurityException ParserException ConversionException DatabaseException UserException CastException MessageException SqlException SocketException SocketTimeoutException UnknownException