Database Listener

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 repeatedly retrieving the same records. 

Creating a Database Listen Adaptor

In order to create a Database Listen Adaptor it is best practice, but not necessary, to create a Database Connection. 

To create the Database Listen Adaptor on any graph click on the Add Listener to Graph 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 that 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 Interval (seconds) - this is the whole number of seconds between SELECTs to the database - default 1 second
• Max Record Count - the maximum number of records that 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 not necessary 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 not possible 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 not possible to use a SELECT * FROM ... WHERE ... query format to retrieve all of 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.