Editing DB Data
This section:
•Editing DB data in tables and other controls
•Updating, inserting, appending, and deleting nodes
•The DB Execute action and $MT_DBExecute_Result variable
•Primary Keys in MobileTogether Designer
About OriginalRowSet
In order for data to be edited and saved, the tree of the page source must also include an OriginalRowSet element, which is a copy of the RowSet element. The original data is saved in the OriginalRowSet element, while edited data is saved in the RowSet element. When the page source is saved, the difference between the two trees, OriginalRowSet and RowSet, is calculated, and the page source is updated on the basis of the difference. If the modification is successful, then the modified data is copied to OriginalRowSet so that OriginalRowSet contains the newly saved DB data, and the modification process can be repeated.
Note the following points:
•The OriginalRowSet element is not created by default in the tree of the DB page source. To create it, right-click the root node of the DB page source and toggle on the command Create OriginalRowSet.
•The Create OriginalRowSet.command is enabled for database type ($DB) root nodes. It is a toggle command that creates/removes an OriginalRowSet data structure that contains the original data of the page source.
•Till the time modified data is saved to the DB, the original DB data is retained in the OriginalRowSet structure. This ensures that the original DB data is still available in the tree.
•To retrieve the original data of a DB row that has been modified but not yet saved, use the XPath function mt-db-original-row.
Editing DB data that is in tables and in other controls
To create a control in which DB data can be edited, do the following:
•Use a control that is editable by the end user, such as a combo box or edit field. A label control, for instance, is not editable. If a table is used to generate repeating rows, add the editable controls within the cells of the table. See the sections Edit Offices Table in the Database-And-Charts tutorial for an example of how to do this. Also see the description of how to work with tables.
•On the control, create a page source link to the page source node that is to be edited. Do this by dragging the page source node onto the control.
•If you use a table with repeating rows, use the option to automatically include Append/Delete controls when the table is created (see screenshot below).
The advantage of using a table with repeating rows that are linked to the repeating Row elements of a DB page source is that when a table row is added, a DB row is also automatically added. For more information, see the description of how to work with tables. For examples, see the Database-And-Charts tutorial and the MobileTogether Designer DB examples files.
Updating, inserting, appending, and deleting nodes
The Update Data actions enable nodes in DBs to be edited when a page or control event is triggered.
•Update Node(s): Updates one or more nodes, such as DB column, with value/s generated or obtained by the action's XPath expression.
•Insert Node(s): Inserts one or more nodes, such as DB rows, before a node selected by the action's XPath expression. The structure and contents of the inserted node can also be defined.
•Append Node(s): Appends one or more nodes, such as DB rows, as the first or last child of a node selected by the action's XPath expression. The structure and contents of the appended node can also be defined.
•Delete Node(s): Deletes one or more nodes, such as a DB rows, specified by the action's XPath expression.
| Note: | These actions are carried out on local data tree. The modified data tree must still be saved back to the DB for the end user modifications to be passed to the DB. |
The DB Execute action and $MT_DBExecute_Result variable
The DB Execute action enables you to use powerful SQL statements, including INSERT, APPEND, UPDATE, and DELETE, to modify a DB. It is different from the actions listed in the previous section in one important way: The modifications created by DB Execute's SQL statements are immediately saved to the DB. In the case of the actions listed in the previous section a Save mechanism must be used to save the modifications to the DB.
After the DB Execute action executes an SQL statement, it stores the result in a variable named $MT_DBExecute_Result. This variable can then be used in XPath expressions anywhere within the project. Consequently, structures and data from one DB can be selected (optionally based on parameters), then held in storage, modified, and inserted at other locations.
Primary keys in MobileTogether Designer
Primary keys in DBs typically are auto-incrementing. When this is the case and a new row is added to a table, the primary key column of the added row is automatically incremented. In MobileTogether Designer, when a table is retrieved the primary key and auto-increment information is automatically retrieved and displayed in the Page Sources Pane (see screenshot below).
If auto-retrieval of this information was not successful, the context menu of tree nodes contains toggle commands that enable you to correctly annotate nodes (see screenshot below).
If the primary key column is not auto-incrementing, new primary key values for appended rows must be automatically generated using an XQuery expression. This is because primary key columns cannot be edited. The XQuery expression is inserted by using the primary-key node's context menu command, Ensure Exists before Page Load (XPath Value). In the example below, a new value is generated for the primary key @id by using the following XQuery expression:
let $all := $DB1/DB/RowSet/Row/@id
let $ids := remove($all, index-of($all, ""))
let $id := if (empty($ids)) then 1 else max($ids) + 1
return $id