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

Creating the adaptor

To create a Database Listen adaptor, it’s 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 you want to change, 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 upper-right of the graph, click Add Listener. Acquia Journey will display the Listener dialog box.
  6. In the Adaptor Connection (Environment) list, either click the connection 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 more information about creating queries.
  8. In the Set Statement field, enter a SET statement updating the selected records to prevent reprocessing. See the following section for more information about creating queries. Acquia Journey returns the records matching 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 several threads may run simultaneously without a single request attempting to return all records.
  10. Click Save.

The listen operation is thread-safe and can execute in various instances without each operation returning the same records.

Writing queries

Regardless of the query 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 maps to your data schema or public variable. If a field in a retrieved record doesn’t 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 part of the statement for you based on the records retrieved from the listen query. You can’t 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 like the following:

SET processed = 'Y';

It’s 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.

SET clauses work with setting to constants only. It’s 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 to ensure an element name exists in a schema or public variable. It’s impossible to use a SELECT * FROM ... WHERE ... query format to retrieve all the fields in the record. If all fields are needed, you must specify each column name.

Validation warnings

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

Warning Description
Database Listener must have an Output It’s 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 is selected