There are 2 Listener types provided with the eiConsole that will allow you to accept data from a relational database – polling from a table, executing a query or executing a stored procedure. The first is the Database Polling SQL Listener. Choose this Listener.
There are 7 tabs: Basic, Advanced, Transaction Logging, Connection, Scheduling, JDBC Props, and Debug.
1. Basic – This will describe how often you would like to poll the relational database. The choices are seconds, minutes, hours and weeks. Choose seconds.
You will also 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.
Choose the pencil icon to display the window below.
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.
2. Advanced – This tab allows us to set a timeout for the queries. If a Listener times out – after a given time period, it will fail. You can specify information about how many records were picked up in a given query (it is Batch Sensitive). You can choose to trigger this Listener only when it is triggered by an external source (Initialize on trigger only).
You can also set to:
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 stuff enables a “First In, First Out” queuing mechanism between Listeners and Transports. If a “FIFO Queue Name” is provided, that name will be used as a key for a queue Transactions & will be pushed into before reaching a transport. They’ll be ordered in this queue according to when the Listener created them.
FIFO Queue Delay: It is the interval between updates/checks against that queue. Providing a queue name guarantees that a given Transport sends transactions in the same order the Listener created them in.
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.
3. Transaction Logging – This tab allows us to enable transaction events logging. That data can be logged by a TransactionEventListener.
The Transaction Logging Enable checkbox allows transaction events originating from this listener to be logged by a TransactionEventListener.
Log Transaction Attribute – if enabled, logs transaction attributes.
Log Transaction Data – if enabled, logs transaction data body.
Log Transaction Data Base64 – if enabled, logs transaction data body as Base64.
4. Connection – 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). If there is an error, you will receive the following message:
5. Scheduling – This tab allows you to set up a schedule for how often the chosen Listener should be run.
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:
If left blank, the system will defer to the polling interval listed on the Basic tab.
You can also specify days of the week or particular dates to exclude from scheduling. The Time Zone drop-down menu allows you to specify the Time Zone that should be used for scheduling. By default, it is set to the Time Zone of the console during the initial configuration.
5. JDBC Props – This tab allows us to set a JDBC property to be set on the JDBC connection.
6. Debug – This tab allows you to choose what information will be logged for debug 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.