PASSTHRU statement

Use PASSTHRU to write ESQL statements that:
  • Bypass the WebSphere Business Integration Message Broker Parser
  • Go straight to the configured backend database
  • Execute a coded statement
PASSTHRU can be used as both a statement and a function.

The first parameter of PASSTHRU must be a valid ESQL expression containing your database syntax. PASSTHRU allows you to use database syntax not normally supported by ESQL.

The behavior of the PASSTHRU function depends on whether you pass one, two, or more parameters. The first parameter of the PASSTHRU function must always be an ESQL expression that either is, or evaluates to, a string. Use question marks in the string to denote where any parameter substitution is required.

If you pass only one other parameter, that parameter evaluates to one of the following:
  • A single scalar value. If this is the case, it is inserted into the first parameter marker.
  • A list of values. If this is the case, the list items are inserted in order into each of the parameter markers within the string.

If you pass two or more other parameters, each parameter is bound to the corresponding question mark in the statement string: that is, the first parameter is bound to the first question mark, the second parameter is bound to the second question mark, and so on.

The question marks are processed as parameter markers in a prepared SQL statement by the DBMS. If you pass values to functions in the DBMS, you must use appropriately typed parameter markers. For example, in DB2 a parameter marker would be cast as follows:
SET OutputRoot.XML.Result.Data[] =
    PASSTHRU('SELECT * FROM user1.stocktable 
    where time=TIMESTAMP(CAST(? as TIMESTAMP)),'1999-12-31-23.59.59.999999');
Here are some examples that illustrate different ways of using the PASSTHRU statement:
SET OutputRoot.XML.Result.Data[] = 
          PASSTHRU('SELECT * FROM user1.stocktable');
PASSTHRU('DELETE FROM user2.AccountData WHERE AccountId =
           ?', InputBody.Data.Account.Id);
SET OutputRoot.XML.Result.Data
  = PASSTHRU('SELECT AccountNum FROM user2.AccountData
    WHERE AccountId = ?', InputBody.Data.Account.Id);
SET OutputRoot.XML.Result.Data[]
  = PASSTHRU('SELECT AccountNum FROM user2.AccountData
              WHERE AccountId IN (? , ? , ?)',
            InputBody.Data.Account.Id[]);
PASSTHRU('INSERT INTO user1.stocktable (stock_id, quantity)
         values (?, ?)', InputBody.Transaction.Id, 
                           InputBody.Transaction.Quantity);

Using drag and drop with PASSTHRU

If you are using drag and drop to create ESQL, and the PASSTHRU statement contains the whole ESQL statement, you must ensure that you include quotes around the ESQL statement being applied. For example:
PASSTHRU('INSERT INTO user1.stocktable VALUES (1)');
However, if a variable is first set and then referred to in the PASSTHRU statement, omit the quotes. For example:
SET myVar = 'SELECT * FROM user1.stocktable';
SET OutputRoot.XML.Data[] = PASSTHRU(myVar);

Considerations when calling stored procedures

If you use the PASSTHRU statement to call stored procedures, note the following considerations:
  • WebSphere Business Integration Message Broker uses Open Database Connectivity (ODBC) to connect to databases. ODBC Version 1 provides support for stored procedure calls using the following ODBC escape sequence:
    call procedure-name [([parameter][,[parameter]]...)]}

    Only the escape sequence described above is supported for the input parameters of the PASSTHRU function in WebSphere Business Integration Message Broker.

  • Using the ESQL CALL statement, you can call a database stored procedure. This procedure behaves as if a sequence of inline ESQL statements are being executed.
  • Stored procedures can exist either:
    • Individually (supported by both DB2 and Oracle). This can be written as follows:
      PASSTHRU('{call proc_insert_comp(?,?)}',InputBody.Test.Company,InputBody.Test.Price);
    • As part of a collective that is accessed using a Package mechanism (supported by Oracle). This can be written as follows:
      PASSTHRU('{call share_management.add_share(?,?)}',
                         InputBody.Test.Company,InputBody.Test.Price); 
  • Stored procedures can be either:
    Noncommittal
    The procedure logic does not take explicit commit and rollback action (supported by both DB2 and Oracle).

    If a message flow is either committed or rolled back, the database operations are either committed or rolled back. This is consistent with the behavior of the Database and Warehouse nodes, which have a transaction property of automatic.

    Committal
    The procedure logic contains explicit commit and rollback actions (supported by Oracle).

    Even if a message flow is rolled back, the database operations are committed. This is consistent with the behavior of the Database and Warehouse nodes, which have a transaction property of commit.

    For further information about coordinated transactions, see Configuring coordinated message flows.

    Stored procedure calls, whether committal or noncommittal, affect any database operations (and subsequent outcome) if a message flow rolls back.

Limitations

There are some limitations when using PASSTHRU to call stored procedures. To illustrate the limitations, consider the following example:
PASSTHRU('{call proc_delete_comp(?)}',InputBody.Test.Company);
  1. WebSphere Business Integration Message Broker supports only input parameters.
  2. SqlMoreResults cannot be used by WebSphere Business Integration Message Broker to retrieve result sets.
Related concepts
ESQL