The Database (SQL) Processor translates incoming XML data into SQL statements which are used to populate/update a specified database.
Select the Processor Configuration tab and click on Add Processor.
Select the Database (SQL) Processor from the drop-down.
Select the Basic tab.
You will be asked to select an Input file. This input file is an XML file that describes the queries we would like to execute against the relational database. The input file can be created or selected from an existing file on disk. Choose the three dots on the right of the Input file to show the window below. Once specified, you can choose to edit this file in the eiConsole’s internal editor or in another editor associated with the XML extension.
Also, you can specify if you want to write simple SQL queries. The SQL field allows you to put the SQL query to be executed. The Query Params fields allow you to specify parameters used (in order) with the query.
Select the Connection tab.
This tab allows us to specify the JDBC connection to the database. The User name is the user name that you would typically use to connect to the database. The Password is the password for the user. The Type is the type of connection that you will be connecting to. In a J2E environment, you can use a DataSource. As a result, you will specify the name as it appears in the application container. In most other cases you will create a JDBC Connection which will allow you to specify the JDBC driver name and URL.
Once the database connections are configured, we can check the settings (use the Test Connection button).
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: if specified, a programmatically-invoked listener is invoked with the SOLXML query results.
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.