IBM Integration Bus, Version 9.0.0.8 Operating Systems: AIX, HP-Itanium, Linux, Solaris, Windows, z/OS

See information about the latest product version

PASSTHRU statement

The PASSTHRU statement evaluates an expression and runs the resulting character string as a database statement.


>>-PASSTHRU----------------------------------------------------->

>--+-Expression--+-----------------------+--+------------------------------+-+-><
   |             '-TO--DatabaseReference-'  |            .-,----------.    | |   
   |                                        |            V            |    | |   
   |                                        '-VALUES--(----Expression-+--)-' |   
   |  (1)                                                                    |   
   '-------(--Expression--+-------------------+--)---------------------------'   
                          |    .-,----------. |                                  
                          |    V            | |                                  
                          '-,----Expression-+-'                                  

WHERE

|--DatabaseReference =  --Database--.--DataSourceClause---------|

Notes:

The lower half of the main syntax diagram (the second of the two ways of coding the Expression to be passed to PASSTHRU) describes syntax retained for backward compatibility.

Usage

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

Note: PASSTHRU can still be used to call stored procedures if:

Only input parameters can be used.
Only single result sets are supported.

If you don't meet these criteria, use the CALL statement because PASSTHRU imposes limitations (you cannot use output parameters, for example).

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

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.

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;

ak05100_.htm |

Last updated Friday, 21 July 2017