Database Listener

Creating a Database Listen Adaptor

The Database Listen Adaptor allows a database table to act as a queue of items to be processed. The Listen Adaptor repeatedly queries the database with a SELECT statement at specified intervals and then returns up to a maximum number of records to be processed by the graph.

Following a successful query, a SET statement is issued to stop the process of repeatedly retrieving the same records.

To create the Database Listen Adaptor on any graph, click on the Add Listener button in the top right of the graph window.
To create the Database Listen Adaptor on any graph, click on the Add Listener button in the top right of the graph window.

This will bring up the Listener configuration window.

Choose the connection from the drop down list in the Listener screen or edit it directly by clicking the Pencil icon next to the connection name.

• Specify a SELECT statement that will return the records to be processed. This could be based on a flag on the table or could be based on a timestamp. 
• The SELECT statement must be of the form SELECT ... FROM ... WHERE ... with the WHERE clause selecting just unprocessed records. This is a simple query with no joins or nested queries.
  • FROM clause cannot be on a separate line and cannot have an extra space at the end. Otherwise, the query will fail.
• Create a SET statement that will update the selected records so they are not processed again. 

The records that match at each query are returned to the Records Selected output destination. 

It is also possible to specify the following: 

• Listen to Interval (seconds) - this is the whole number of seconds between SELECTs to the database - default 1 second
• Max Record Count is the maximum number of records the listener will return in one query. This allows multiple threads to run simultaneously and not act in a greedy fashion by one thread taking all of the records. 

The Listen operation is thread-safe and is able to execute in multiple instances without each returning the same records. 

Example Queries 

Where a flag is used on the database to identify new, unprocessed records:

SELECT id,firstname,lastname,email FROM customer WHERE processed = 'N';

And the corresponding SET query:

SET processed = 'Y';

It is unnecessary to specify the WHERE clause for the SET. The Listen Adaptor will do that automatically. In both cases, the trailing semi-colon on the SQL is optional. 

At present, SET clauses work with setting to constants only. It is impossible to run a database function or any other node or adaptor in the SET clause.

For Example:

SET status='runningLogic'

The named fields in the SELECT statement will be mapped with the same name into the location specified. No checking is performed that an element name exists in a schema or public variable. It is impossible to use a SELECT * FROM ... WHERE ... query format to retrieve all the fields in the record. If all fields are needed, you will need to specify each column name. 

Validation Warnings

Trying to save the Database Listen Adaptor without completing all of the necessary parts will create one or more notifications.

 New Notification : All the warning notifications will be displayed in the Notification Panel on the right side of the page.

Related Pages:

• Database Adaptor
• Database Read Adaptor
• Database Write Adaptor
• Database Delete Adaptor