The DB Execute Action
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.
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.
Use INSERT INTO to insert a single row into a table. The SQL syntax is:
INSERT INTO DestinationTable (ID, City) VALUES ('ID-Value', 'City-Value');
The statement above inserts a row containing two columns (ID and City) into the DestinationTable table. Note the following points:
•Only those columns that are specified in the SQL statement are inserted in the new row (ID, City in the example above). •To insert a complete row (containing all table columns), specify all table columns in the SQL statement . •The column names and column values in the SQL statement must correspond to each other by position. This column order does not need to correspond to the column order in the DB table. This means that if the layout of the DB table changes subsequently, the SQL statement will still be correct and does not need to be updated to reflect the changed layout. •A column value must exist for each column name. Otherwise an error is generated and the row is not inserted. •If a column is omitted in the SQL statement, then that column must be defined in the DB to allow NULL values (be empty) or to have a default value; otherwise an error is generated and the row is not inserted. •To insert multiple rows, specify multiple INSERT INTO statements.
|
Use INSERT SELECT to insert the result of a SELECT statement into a table. Typically, INSERT SELECT is used to copy a set of rows from one table to another. The SQL syntax is:
INSERT SELECT Offices (ID, City, Country) SELECT ('ID', 'Stadt', 'Land') FROM Offices_DE ;
The statement above inserts all the rows of the Offices_DE table into the Offices table. Note the following points:
•Only those columns that are specified in the SQL statement are inserted in the new row (ID, City, Country in the example above). •The columns returned by the SELECT statement will be inserted into the corresponding columns of the destination table. The correspondence of columns is determined by position. In the example above, for instance, the column Stadt at position 2 in the SELECT statement corresponds to the column City at position 2 in the definition of the destination table. The names of columns in the two definitions do not need to match; correspondence is fixed by position. •The SELECT statement can use a WHERE clause to filter the data that is inserted.
|
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.
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.
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.