Database (SQL) Transport
The Database (SQL) Transport is used in the eiConsole and eiPlatform to connect to a relational database. As with all the components of the eiConsole, the user is presented with a graphical Interface with easy-to-configure panels.
Transport (Adapter) Configuration Drop-Down List
Basic Database (SQL) Transport Configuration Options
On the Basic configuration tab, you can specify:
- Input File – if specified, an 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 Parameters – parameters used (in order) with the query
Database (SQL) Transport Basic Configuration Options
Connection Database (SQL) Transport Configuration Options
On the Connection tab, specify connection information for the relational database that you wish to update with the following options:
- User Name – use to connect to the database
- Password – used to connect to that database
- Type – 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 Data Source name. This name is a Java JNDI name where the DataSource is located. Typically, this is configured within an application server.
- JDBC Driver – when JDBC Connection is the connection type selected, you will be prompted to provide the JDBC Driver. The JDBC Driver is the name of the Java class used to connect to the database,
- JDBC URL – when JDBC Connection is the connection type selected, you will be prompted to provide the JCBC URL. 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.
- Test Connection – once the connection is fully configured, this button can be used to ensure the credentials are correct
Database (SQL) Transport Connection Configuration Options
Advanced Database (SQL) Transport Configuration Option
On the Advanced tab, the following configuration options are available:
- Keep Connection – allows specifying whether the JDBC Connection should be held between invocations of this Transport
- Autocommit Transactions – allows you to enable an autocommit feature. 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
Database (SQL) Transport Advanced Configuration Options (top half of screen)
- CallBack Listener – this configuration item is used when the Transport will end with a 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.
- Use Single Output Stream – when enabled, query responses are executed as a single transaction
- Error on Unknown Elements – allows you to specify whether or not to flag as an 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) Transport Advanced Configuration Options (bottom half of screen)
When the Database SQL Transport is executed, it executes what is called SQLXML. This is an XML syntax that is used to wrap SQL inserts, updates and control structures so that you can execute arbitrarily complex updates or queries against a relational database.
JDBC Properties Database (SQL) Transport 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 a particular driver’s documentation is necessary to utilize specific properties.
Database (SQL) Transport JDBC Properties Configuration Options
Pooling Database (SQL) Transport Configuration Options
The Polling tab can be used to specify the following options:
- Use Connection Polling – if enabled, multiple connections are permitted and polled
- Connections Allowed – the number of simultaneous database connections permitted
Database (SQL) Transport Polling Configuration Options
Debug Database (SQL) Transport 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) Transport Debug Configuration Options
Compatibility Database (SQL) Transport Configuration Options
The Compatibility tab can be used to specify the following option:
- Use JDBC Parameter Typing – if selected, any statement parameters will be type-checked by the JDBC driver only
Database (SQL) Transport Compatibility Configuration Options
When the Database (SQL) Transport is executed, it invokes what is called SQLXML. The details of SQLXML are covered in:
eiConsole SQLXML – Selecting Rows (Interface & Tutorial)
eiConsole SQLXML – Inserting and Updating Rows (Interface & Tutorial)
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.
Transaction Isolation Database (SQL) Transport 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) Transport Transaction Isolation Configuration Options