Database Write Adaptor

The database Write Adaptor allows SQL INSERT or UPDATE to be executed against a database. 

Creating a Database Write Adaptor

In order to create a Database Write Adaptor it is first necessary to create a Database Connection. 

Create a new item via the create button in the graphs tab of the project home page select the Database Adaptor icon and give the adaptor a name. 

Choose the connection from the drop down list in the Adaptor screen or edit it directly by clicking the pencil icon next to the connection name .  Select Write from the Database Action drop down. 

Create the database query of one of the two following forms: 

• INSERT INTO <tablename>[(field1,field2, ...)] VALUES(field1, field2, ...) 
  • In an INSERT statement, you will not be able to apply a function to a value
• UPDATE <tablename> SET field1 = val1, field2 = val2, ... WHERE condition 
  • In an UPDATE statement a WHERE clause is required. Otherwise, the statement will fail when testing the execution.

The SQL syntax should be kept generic. Xponent uses SQL Alchemy to translate to the specific database so there is no need to use database specific syntax. Any parameters in the query are given in the form %%paramName%%. The closing semicolon is optional. Queries can be split over multiple lines to increase readability. 

For example to insert new records into a customer table the query could be as follows: 

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

Once the SQL has been edited, or a new parameter has been added, it is necessary to save the query.

On Success the adaptor will return the number of changes 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 and it 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. Currently the only way to access that value is through a JavaScript Node that returns the new value: 

newSchemaLocation = primaryKeys[0];

Validation Warnings

	Warning	Note
	Adaptor does not have connection set	It is necessary to create a connection and choose it from the Adaptor Connection drop down
	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
	DATABASE NAME: Neither properly formatted INSERT or UPDATE statement found	It is necessary to include a WHERE clause when using an UPDATE statement. Sample correct query: UPDATE <tablename> SET field1 = val1, field2 = val2, ... WHERE <condition>