Altova MobileTogether Designer

The DB Execute Action

Home Prev Top Next

The DB Execute action (see screenshot below) is a powerful mechanism for modifying DB data. You can insert, delete, update, and save data by using SQL statements. This enables the power of the SQL language to be used whenever an event occurs during the progress of the solution.

MTDDBExecuteInsert01

In this section, we describe how to insert, update, delete, and save data using DB Execute. The command to modify DB data is specified in the SQL statement of the action (see screenshot above). For a detailed description of the settings of the DB Execute action, see the section Actions > Database > DB Execute. Note that the SQL statement of DB Execute provides additional flexibility since it allows the use of parameters. The values of these parameters are generated by XPath expressions. See the DB Execute action section for details.

 

If the DB data is being displayed on the same page as the page on which the action is defined, you should add a Reload action to update the display of the modified DB (see screenshot above). In the screenshot above, the $DB1 tree is the root node of the database table OfficeSales_DB. After OfficeSales_DB has been modified with the INSERT statement, the $DB1 tree on the design page is reloaded, thus immediately reflecting the modification to the DB.

 

 

INSERT: Inserting rows with DB Execute and SQL

The INSERT statement of SQL can be used to insert rows in a database table. The INSERT INTO statement is used to insert rows with specific values, whereas the INSERT SELECT statement is used to insert the result of a SELECT statement into a table. You can also use other SQL statements, such as SELECT INTO, to insert rows in a table.

MTDDBExecuteInsert01

 

UPDATE: Updating rows with DB Execute and SQL

The UPDATE statement of SQL can be used to update rows in a database table. The UPDATE statement has three parts:

 

The name of the DB table to update

The names of the columns to update and their values

A WHERE clause to filter which rows to update

 

Here is an example of an SQL UPDATE statement:

 

UPDATE [AltovaMobile_Offices]

SET    [Office] = 'New York',

      [Contact] = 'Altova Johnson'

WHERE  [id]     = 11;

 

This statement updates the row with id=11 from, say, Office='USA' to Office='New York' and Contact=NULL to Contact='Altova Johnson'. The screenshot below shows this UPDATE statement example in the SQL statement setting of a DB Execute action.

MTDDBExecuteUpdate01

Note the following points:

 

The columns to be updated are given by their name=value combinations, with each name=value combination being separated from the next by a comma. There is no comma after the last name=value combination.

All the columns to be updated are specified within a single SET clause.

A column's value can be deleted by setting it to NULL, assuming that NULL values are allowed for that column. For example: SET [Contact] = NULL.

 

The Reload action reloads the modified DB immediately after the modification has been carried out. Without the Reload action, the modification will not be displayed on the page.

 

 

DELETE: Deleting rows with DB Execute and SQL

The DELETE statement of SQL can be used to delete:

 

specific rows of a table (by specifying a WHERE clause to select the rows to delete)

all rows of a table (by omitting the WHERE clause)

 

Here is an example of an SQL DELETE statement:

 

DELETE FROM [AltovaMobile_Offices]

WHERE [id] = 11;

 

The SQL DELETE statement above deletes the row with id=11. If the WHERE clause is omitted, then all the rows of the AltovaMobile_Offices table will be deleted.

MTDDBExecuteDelete01

An SQL DELETE statement in a DB Execute action.

The Reload action reloads the modified DB immediately after the modification has been carried out. Without the Reload action, the modification will not be displayed on the page.

 

© 2018-2024 Altova GmbH