Database (SQL) Transport – Translates incoming XML data into SQL statements which are used to populate or update a specified database.
This Transport is used in the eiConsole when you wish to connect to a relational database.
On the Basic configuration tab, you have an Input file selection. Typically this is unused since the XML used to populate SQL statements is generally provided by one of the transformation Stages.
Write Query: enables writing simple SQL queries (will replace transaction body with SQLXML).
SQL: SQL query to be executed.
Query Params: parameters used (in order) with the query.
On the Connection tab, you will specify connection information for the relational database that you wish to update. Enter the User name and Password that are used to connect to that database. Specify the Type of connection to be used via the Type configuration item. The connection type may either be a raw JDBC connection or a DataSource. When DataSource is selected, you will be prompted to provide the DataSource name. This name is a Java JNDI name where the DataSource is located. Typically, this is configured within an application server. When JDBC Connection is configured you will be prompted to provide the JDBC driver and the JDBC URL. The JDBC driver is the name of the Java class used to connect to the database, whereas the JDBC URL is the JDBC-compliant URL string used to specify the details of that connection. This typically includes the server and database name. Once the connection is fully configured, the Test Connection button can be used to ensure the credentials are correct.
On the Advanced tab, the Keep Connection configuration item allows you to specify whether the JDBC connection should be held between invocations of this Transport. The Autocommit Transactions allows you to enable an autocommit. If disabled, each SQLXML document is a transaction.
The Enable Timeout for Queries: check to enable timeout for queries.
The Timeout for Queries: the amount of time allowed to pass before a query is considered a failure and aborted. If the value is set with enhanced properties, the units are seconds.
The Restrict Metadata To Catalog: restricts the amount of metadata retrieved to a given catalog pattern to improve performance.
The Restrict Metadata To Schema: restricts the amount of metadata retrieved to a given schema to improve performance.
The Restrict Metadata To Table(s): restricts the amount of metadata retrieved to a given table to improve performance.
The CallBack Listener configuration item is used when the Transport will end with some query, and that query will return results which will be passed to another interface for processing. When you configure the CallBack Listener, data input into this text area should match the name of a programmatic or triggerable Listener contained within another route.
The Use Single Output Stream: when enabled, query responses are executed as a single transaction.
The Error on Unknown Elements allows you to specify whether or not to error when an unknown SQLXML function is encountered. The Use JDBC Identity for Inserts checkbox: if enabled, identity queries will use the JDBC’s generated keys method to improve performance. Selecting this option will override any custom identity query specified in the SQLXML ‘IdentityQuery‘ attribute.
The JDBC Properties tab can be used to specify name/value pairs to the JDBC driver. The availability and behavior of these properties vary between drivers, so referring to the documentation for a particular driver is necessary to utilize specific properties.
The Polling tab can be used to specify:
Use Connection Polling: If enabled, multiple connections are permitted and polled.
Connections Allowed: The number of simultaneous database connections permitted.
The Debug tab can be used to specify:
Log Metadata: specifies whether or not to output debug information on database metadata loading.
SQLXML Logging: specifies whether or not to output debug information on SQLXML execution.
The Compatibility tab can be used to specify:
Use JDBC Parameter Typing: if selected, any statement parameters will be type-checked by the JDBC driver only.
When the Database (SQL) Transport is executed it invokes what is called SQL XML. The details of SQL XML are covered in:
It should be noted that this is an XML syntax that is used to wrap SQL inserts, updates and control structures such that you can execute arbitrarily complex updates or queries against a relational database.
Next, a look at the Database Table Transport.