The Database (SQL) Processor in the eiConsole translates incoming XML data into SQL statements which are used to populate/update a specified database.
Processor (Adapter) Configuration Drop-Down List
Select the Database (SQL) tab and click on Add Processor.
Click on Add Processor
Basic Database (SQL) Processor Configuration Options
On 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 the 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.
- Input file – if specified, a SQLXML input file is executed
- 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
Database (SQL) Processor Basic Configuration Options
Connection Database (SQL) Processor Configuration Options
On the Connection tab, you can specify the JDBC connection to the database.
- User Name – the user name that you would typically use to connect to the database
- Password – the password for the user
- Type – 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 JDBC URL.
Once the database connections are configured, we can check the settings (use the Test Connection button).
Database (SQL) Processor Connection Configuration Options
Advanced Database (SQL) Processor Configuration Options
On the Advanced tab you can set:
- Keep Connection – specifies whether the JDBC connection should be held between invocations of this Transport
- Autocommit Transactions – enables an autocommit. If disabled, each SQLXML document is a transaction.
- Enable Timeout for Queries – check to enable timeout for queries
- 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.
- Disable Metadata – disables the polling of metadata when running queries which may improve performance depending on database implementation
- Restrict Metadata to Catalog – restricts the amount of metadata retrieved to a given catalog pattern to improve performance
- Restrict Metadata to Schema – restricts the amount of metadata retrieved to a given schema to improve performance
- Restrict Metadata to Table(s) – restricts the amount of metadata retrieved to a given table to improve performance
- CallBack Listener – if specified, a programmatically-invoked Listener is invoked with the SOLXML query results
- Use Single Output Stream – when enabled, query responses are executed as a single transaction
- Error on Unknown Elements – specifies whether or not to error when an unknown SQLXML function is encountered.
- Use JDBC Identity for Inserts – 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.
Database (SQL) Processor Advanced Configuration Options
JDBC Properties Database (SQL) Processor Configuration Options
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.
Database (SQL) Processor JDBC Properties Configuration Options
Polling Database (SQL) Processor Configuration Options
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
Database (SQL) Processor Polling Configuration Options
Debug Database (SQL) Processor Configuration Options
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
Database (SQL) Processor Debug Configuration Options
Compatibility Database (SQL) Processor Configuration Options
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
Database (SQL) Processor Compatibility Configuration Options
Transaction Isolation Database (SQL) Processor Configuration Options
The Transaction Isolation tab allows you to specify:
- Transaction Isolation – sets the Transaction Isolation level for the JDBC connection. Behavior is dependent on the driver. Default, None, Read Uncommitted, Read Committed, Serializable, Driver Specific.
- Driver Specific Level – sets the Isolation Level to a specific driver-unique value. Isolation levels are Integer values and are usually constants. Please refer to driver documentation or code to determine the correct values.
Database (SQL) Processor Transaction Isolation Configuration Options