Information for: DEVELOPERS   PARTNERS

Database: Write adaptor

The database write adaptor allows SQL INSERT or UPDATE queries to run against a database.

Creating the adaptor

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

After you have created the adaptor, create the database query of one of the two following forms:

  • INSERT INTO [(field1,field2, ...)] VALUES(field1, field2, ...)
  • UPDATE  SET field1 = val1, field2 = val2, ... WHERE condition

The SQL syntax must be generic. Acquia Journey uses SQL Alchemy to translate to the specific database so it is not necessary to use database specific syntax. Any parameters in the query are given in the form %%paramName%%. The closing semicolon is optional. You can split queries over several lines to increase readability.

To insert new records into a subscriber table, refer to the following example query:

INSERT INTO customer(firstName,lastName,email) VALUES (%%firstName%%,%%lastName%%,%%email%%)

It is not necessary to put quotation marks around the values in the VALUE statement or in a SET value if the values are string values. If the value is a JavaScript object or parent schema location, then the quotation marks are required.

After you have edited the SQL, or added a new parameter, it is necessary to click Save Query / Update parameters.

When the query is successful, the adaptor will return the number of change records in the optional Records Changed output location.

The database adaptor supports returning the value of an auto-increment field in the Records Changed field. The auto-increment field will be returned in a primaryKey attribute as an array. If your table has a single auto-increment field, then it will be the zeroth element in the array.

The only method to access the value is by having a JavaScript Node return the new value:

newSchemaLocation = primaryKeys[0];

Validation warnings

Warning Description
Adaptor does not have connection set It is necessary to create a connection and choose it from the Adaptor Connection list.
Database query cannot be empty The database query must have a valid SQL INSERT or UPDATE query.
Parameters each need a data source Every parameter in the query of the form %%param%% must be mapped to an input data source.