Database Polling (SQL) Listener
Easily Accept Data from a Relational Database with the Listener (Adapter) Types Provided in the eiConsole.
There are Listeners (Adapters) provided in the eiConsole that will allow you to accept data from a relational database, poll from a table, execute a query or execute a stored procedure. As with all of the components in the eiConsole, the user is presented with a graphical interface and easy-to-configure screens.
The adapter type we will focus on is the Database Polling (SQL) Listener or Adapter.
Listener (Adapter) Configuration Drop-Down List
The user is presented with 9 tabs: Basic, Advanced, Transaction Logging, Inactivity, Throttling, Connection, Scheduling, JDBC Props, and Debug. In the Basic tab, you describe how often you would like to poll the relational database. A pull-down menu presents you with choices – seconds, minutes, hours, and weeks.
Basic Database Polling (SQL) Listener Configuration Options
You will be asked to select an input file. This input file is an XML file that describes the queries you would like to execute against the relational database. The input file can be created or selected from an existing file on disk. Once specified, you can choose to edit this file in the eiConsole’s internal editor or in another editor associated with the XML extension.
Database Polling Listener Basic Configuration Options
Advanced Database Polling (SQL) Listener Configuration Options
The Advanced tab allows you to set a timeout for the queries. If a Listener times out after a given time period, it will fail. You can specify information on how many records were picked up in a given query (Batch Sensitive?). You can also choose to trigger this Listener only when it is triggered by an external source (Initialize on Trigger Only).
- Allow Command-Line Invocation – if enabled, the listener can be invoked using the CLI client application
- Restart on Listening Error – if enabled, the listener will be restarted after an error occurs
- FIFO Queue Name – the FIFO options enable a “First In, First Out” queuing mechanism between Listeners and Transports. If a FIFO Queue Name is provided, it will be used as a key for a transaction queue. Transactions will be written to this queue before they reach a Transport. The transactions in this queue will be ordered according to when they were created by the Listener.
- FIFO Queue Delay – this is the interval between updates or checks against that queue. Providing a queue name guarantees that a given Transport sends transactions in the same order that the Listener created them in.
- Batch Sensitive? – when this is enabled, outgoing responses are executed in the sequential order they were generated, and are stopped on failure
- 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.
Database Polling Listener Advanced Configuration Options (top half of screen)
- 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
- Use Single Output Stream – when enabled, query responses are executed as a single transaction
- Refresh Handler – when enabled, the SQLXML handler is refreshed each polling cycle
- Error on Unknown Elements – specifies whether or not to error when an unknown SQLXML function is encountered
- Use JDBC Identify 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 Polling Listener Advanced Configuration Options (bottom half of screen)
Transaction Logging Database Polling (SQL) Listener Configuration Options
The Transaction Logging tab allows you to specify:
- Transaction Logging Enabled – if enabled, allows transaction events originating from this Listener to be logged by a TransactionEventListener
- Log Transaction Data – if enabled, logs transaction data body
- Log Transaction Data Base64 – if enabled, logs transaction data body as Base64
- Log Transaction Attributes – if enabled, logs transaction attributes
- Log All Attributes – if enabled, no attributes will be filtered
- Allowed Attributes – attributes which are allowed to be logged
Database Polling Listener Transaction Logging Configuration Options
Inactivity Database Polling (SQL) Listener Configuration Options
The Inactivity tab allows you to specify:
- Enable Inactivity Monitor – check this box to enable inactivity monitoring. This will throw a non-transaction exception if the specified number of transactions haven’t been processed in the specified time interval.
- Min. Transactions to Expect – the number of transactions to expect to be completed per monitoring interval
- Monitoring Interval – how often to check the specified number of transactions that have been processed
- Times to Monitor – if set, monitoring will be done during the defined times of the day. To ignore, set start and end time equally.
- Days to Exclude from Monitoring – inactivity monitoring will not occur on the days specified
- Include Errors in Transaction Count – if checked, transactions that attempted to start, but failed at the Listener stage, will also be counted
Database Polling Listener Inactivity Configuration Options
Throttling Database Polling (SQL) Listener Configuration Options
The Throttling tab allows you to specify:
- Throttling Mode – the throttling mode to use for limiting the number of transactions or messages emitted by this Listener. “Timed” will limit transactions based on time intervals, while “Concurrent” will limit based on a concurrent number of transactions. “Concurrent” mode requires a Throttling Response Processor step later in your interface workflow to acknowledge completion.
Database Polling (SQL) Listener Throttling Mode
- Throttling Mechanism – the mechanism to use for throttling messages. “Blocking” prevents the Listener from continuing to process and emit messages altogether, while “queued” pushes received messages into the interface queue or a default, in-memory queue.
- Max Concurrent Messages – how many messages can be concurrently processed, either by time-based limits (allow X per second) or synchronous (allow X at any time)
- Timed Emission Interval – the interval for time-based limits (allow X per X timed emission interval)
- Synchronous Timeout Interval – the interval to wait for a synchronous response before failing
Database Polling Listener Throttling Configuration Options
Connection Database Polling (SQL) Listener Configuration Options
The Connection tab allows you 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. Type is the type of connection that you will be connecting to. In a J2EE environment, you can use a Data Source. 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.
Database Polling Listener Connection Configuration Options
Once the database connections are configured you can check the settings using the Test Connection button. If there is an error, you will receive an error message.
Database Polling Listener Failed Connection Test Example
Scheduling Database Polling (SQL) Listener Configuration Options
The Scheduling tab allows you to create a schedule for how often the chosen Listener should be run. You can easily modify the start time or end time.
- Scheduled Start Time – specify the scheduled start time. If left blank, the system will defer to the polling interval listed on the Basic tab.
- Scheduled End Time – specify the scheduled end time. If left blank, the system will defer to the polling interval listed on the Basic tab.
- Week Days to Exclude – specify days of the week to exclude from scheduling
- Dates to Exclude – specify specific dates to exclude from scheduling
- Time Zone – specify the Time Zone that should be used for scheduling. By default, it is set to the Time Zone of the eiConsole during the initial configuration.
To modify the scheduled start or end time, choose the three dots next to the corresponding line. You will receive a dialogue box that looks like this:
Database Polling Listener Scheduling Options
JDBC Props Database Polling (SQL) Listener Configuration Options
The JDBC Props tab allows you to set up a JDBC property to be set on the JDBC connection.
JDBC Properties for Database Polling Listener Configuration
Database Polling (SQL) Listener Debug Configuration Options
The Debug tab allows you to choose what information will be logged for debugging purposes.
- 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 Polling Listener Debug Configuration Options
Database Polling (SQL) Listener Transaction Isolation 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 Polling Listener Transaction Isolation Configuration Options