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 the 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)

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
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 that 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