Information for: DEVELOPERS   PARTNERS

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, 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 the adaptor

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, complete the following actions:

  1. Sign in to Acquia Journey.
  2. Click the Acquia Journey logo in the top menu to view the main project page.
  3. Identify the project that you want to modify, and then click its Project Editor icon.
  4. Select the graph to which you want to add a listener, and then click Open at the bottom of the panel.
  5. In the top right of the graph editing canvas, click Add Listener. Acquia Journey will display the Listener dialog box.
  6. In the Adaptor Connection (Environment) list, either click the connection that you want to use, or edit it directly by clicking the Pencil icon next to the connection name.
  7. In the Listen Query area, specify a SELECT statement without joins or nested queries that will evaluate a timestamp or flag on the table to determine which records to return for processing. The SELECT statement must be of the form SELECT ... FROM ... WHERE ... with the WHERE clause selecting only unprocessed records. See the following section for additional information about creating queries.
  8. In the Set Statement field, enter a SET statement that updates the selected records to prevent reprocessing. See the following section for additional information about creating queries. Acquia Journey returns the records that match each query to the Records Selected output destination.
  9. Optionally, set the values for the following items:
    • Listen Interval (seconds) – The whole number of seconds between SELECT statements to the database. (Default: 1 second)
    • Max Record Count – The maximum number of records the listener will return in one query, ensuring multiple threads may run simultaneously without a single request attempting to return all records.
  10. Click Save.

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

Writing queries

Regardless of the query that you create for listen queries and set statements, you can omit the semi-colon ( ; ) from the end of each statement.

A listen query may specify fields as part of the SELECT clause (SELECT field1, field2 FROM ...) or include all fields in a record by specifying the asterisk ( * – for example, SELECT * FROM ...). Each field is mapped to your data schema or public variable. If a field in a retrieved record does not exist in your data schema or public variable, Acquia Journey will create the field for you.

A set statement can set only a constant value. Acquia Journey sets the WHERE portion of the statement for you based on the records retrieved from the listen query. You cannot use any database stored procedures in your query.

Example queries

The following example evaluates for a processed flag to identify new, unprocessed, records:

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

To set the processed flag on records, use a query similar to the following:

SET processed = 'Y';

Validation warnings

Attempting to save a database listen adaptor without completing all of its necessary values will return one or more of the following notifications:

Warning Description
Database listener must have an Output It is necessary to select an output location for the Listen adaptor
Database listener must have an Update Query A listen and update query must be provided
Database listener must have a Connection set No connection has been selected