PASSTHRU function

The PASSTHRU function evaluates an expression and executes the resulting character string as a database statement, returning a result set.

Syntax

WHERE

Notes:

¹ The lower half of the main syntax diagram describes syntax retained for backward compatability.

The PASSTHRU function is similar to the PASSTHRU statement, which is described in PASSTHRU statement.

Usage

The main use of the PASSTHRU function is to issue complex SELECTs, not currently supported by the integration node, to databases. (Examples of complex SELECTs not currently supported by the integration node are those containing GROUP BY or HAVING clauses.)

The first expression is evaluated and the resulting character string is passed to the database pointed to by DatabaseReference (in the TO clause) for execution. If the TO clause is not specified, the database pointed to by the node's data source attribute is used.

Use question marks (?) in the database string to denote parameters. The parameter values are supplied by the VALUES clause.

If the VALUES clause is specified, its expressions are evaluated and passed to the database as parameters; (that is, the expressions' values are substituted for the question marks in the database statement).

If only one VALUE expression exists, the result might or might not be a list. If it is a list, the list's scalar values are substituted sequentially for the question marks. If it is not a list, the single scalar value is substituted for the (single) question mark in the database statement. If more than one VALUE expression exists, none of the expressions evaluate to a list; their scalar values are substituted sequentially for the question marks instead.

Because the database statement is constructed by the user program, it is not essential to use parameter markers (that is, the question marks) or the VALUES clause, because the whole of the database statement could be supplied, as a literal string, by the program. However, use parameter markers whenever possible because this reduces the number of different statements that need to be prepared and stored in the database and the integration node.

Database reference

A database reference is a special instance of the field references that is used to refer to message trees. It consists of the word Database followed by the name of a data source (that is, the name of a database instance).

You can specify the data source name directly or by an expression enclosed in braces ({...}). A directly-specified data source name is subject to name substitution. That is, if the name used has been declared to be a known name, the value of the declared name is used rather than the name itself (see DECLARE statement).

If you have created a message flow that contains one of the following nodes, and the ESQL that is associated with this node includes a PASSTHRU statement and a database reference, you must specify a value for the Data source property of the relevant node:

Compute
Database
Filter

Handling errors

It is possible for errors to occur during PASSTHRU operations. For example, the database might not be operational or the statement might be invalid. In these cases, an exception is thrown (unless the node has its Throw exception on database error property cleared). These exceptions set appropriate SQL code, state, native error, and error text values and can be dealt with by error handlers (see the DECLARE HANDLER statement).

For further information about handling database errors, see Capturing database state.

Example

The following example performs a SELECT on table Table1 in schema Schema1 in database DSN1, passing two parameters to the WHERE clause and asking for the result set to be ordered in ascending name order. The result set is assigned to the SelectResult folder:

SET OutputRoot.XML.Data.SelectResult.Row[] = 
  PASSTHRU('SELECT R.* FROM Schema1.Table1 AS R WHERE R.Name = ? OR R.Name = 
           ? ORDER BY Name'
   TO Database.DSN1
   VALUES ('Name1', 'Name4'));

The above example assigns the result set to the OutputRoot message body tree that is owned by the Generic XML parser, which allows self-defining messages.

If assigning the result set into a message tree owned by one of the MRM parsers, and the result set structure exactly matches the MRM message definition, 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, you must first assign the result set into a ROW data type, or an Environment tree that does not 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.