Information for: DEVELOPERS   PARTNERS

Database: Read adaptor

The database read adaptor allows SQL Select statements to run. The records returned by the SELECT statement will go into the output destination schema or public variable location.

Creating the adaptor

Create a Database adaptor, and when it appears for configuration, in the Adaptor Action list, be sure to click Read. For more information about creating or configuring adaptors, see Acquia Journey adaptors.

After you have created the adaptor, the Read Query must be specified as a standard SQL expression. Acquia Journey uses the Python SQL Alchemy library to convert the SQL to the specific dialect of the database type eliminating the use of database specific encoding such as backticks (`identifier`) for MySQL or square brackets ([identifier]) for Microsoft SQL. It is good practice to develop your query and ensure it returns the correct data in your usual database development tool before copying to Acquia Journey and parameterizing.

Final semi-colons are optional. You can split the query over several lines for readability purposes.

You can parameterize the query using %%paramName%% anywhere within the query. The following example displays an entry in the Read Query area to select a subscriber record:

select firstName, lastName, email from customer where IdCustomer = %%customerId%%

Anything used in a WHERE clause for a SELECT statement must use single or double quotes if the parameter is a string. If the parameter is numeric, as displayed in the preceding query, quotation marks are not required.

select * from appParams where paramKey like '%%paramKey%%';

Whenever you change the query or add or remove a parameter, it is necessary to save the query by clicking Save Query / Update Parameters. Saving the query will identify all of the parameters and create a slot in the Parameters list in the lower-left panel.

After you have specified all the parameter locations, the adaptor becomes valid.

If the adaptor runs as expected, it will populate the schema as follows:

  • Data Store Values
    • Schema
    • Public Variables

Validation warnings

Warning Description
Parameters each need a data source Every parameter in the query of the form %%param%% must map to an input data source.