PASSTHRU statement
The PASSTHRU statement evaluates an expression and runs the resulting character string as a database statement.
Usage
- Only input parameters can be used.
- Only single result sets are supported.
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).
- 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
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;
SET myVar = 'SELECT * FROM user1.stocktable';
SET OutputRoot.XMLNS.Data[] = PASSTHRU(myVar);
drops(that is, deletes) the table Customers from schema Shop in database DSN1:
PASSTHRU 'DROP TABLE Shop.Customers' TO Database.DSN1;