Database Write Adaptor

Updated on 11 Nov 2024
2 Minutes to read
Contributors

Print
Share
Dark
Light
PDF

Article summary

Did you find this summary helpful?

Thank you for your feedback

Creating a Database Write Adaptor

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

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 from the right-hand side column and give the adaptor a name.

Choose the connection from the dropdown 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 dropdown.

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 unnecessary to put quotation marks around the values in the VALUE statement or a SET value if the values are simple string values. However, if the value is a JavaScript object or parent schema location,, 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 changed 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, 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

: All the invalid notifications will be displayed after expanding the Invalid Drop-down.

Warning	Note
Adaptor(s) do not have connection set	Creating a connection and choosing it from the Adaptor Connection dropdown is necessary.
Database query can't be empty	The database query must have a valid SQL INSERT or UPDATE query
Parameter(s) 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>

Is it helpful? React and share your comment

What's Next

Database Delete Adaptor

Table of contents

Creating a Database Write Adaptor
Validation Warnings

Database Write Adaptor

Creating a Database Write Adaptor

Validation Warnings

Related Pages:

What's Next