When the event is triggered, the DB Execute action executes the action's SQL statement on the data source selected in the Connection combo box. This combo box lists all the data sources of the project, and also offers the option of setting up an additional database connection specifically for use with the DB Execute action. If the Store results in $MT_DBExecute_Result check box is selected, then the results are stored in the $MT_DBExecute_Result variable. This variable can then be used in XPath expressions elsewhere on the page to provide the result of the DB Execute action.
The On Error option lets you define what should be done if an error occurs:
•Abort Script: After an error occurs, all subsequent actions of the triggered event are terminated. This is the default action if an error occurs. If you wish to continue despite an error, select either the Continue or Throw option.
•Continue: Actions are not terminated. Instead, you can select what to do in either event: when there is no error (On Success), or when there is an error (On Error). For example, you might want to display a message box saying whether a page load was successful or not.
•Throw: If an error is detected, this option throws an exception that is stored in the Try/Catch action's variable. The Catch part of the Try/Catch action is used to specify what action to take if an error occurs. If no error occurs, then the next action is processed. See the section Try/Catch action for details.
For more information about using the action, see the section Page Design | Database | The DB Execute Action.
|Note:||The DB Execute action is used to interact with data in DB page sources. It is not the ideal mechanism for displaying data. If you wish to display data from a DB page source node, then insert (into the design) a control that is linked to a page source node. For information, see the sections about controls and page sources. The tutorials give you hands-on instructions about how to display page source data.|
The SQL statement
To enter or edit the SQL statement, click the Additional Dialog button. This displays the Edit SQL Statement dialog (screenshot below). The Root Object at the bottom of the dialog is selected automatically and is based on the selection in the Connection combo box. The Root Object field cannot be edited. Before proceeding, make sure that this is the root object you want.
Fixed statement with optional parameters
To enter an SQL statement, select Fixed statement with optional parameters, and enter the SQL statement. The use of parameters in the SQL statement provides more flexibility. For example, in the screenshot above, instead of entering a fixed value for the WHERE clause, a parameter name Maker is used to supply the value of a node in an XML page source. In the first line below, a fixed value is used; in the second line, the parameter Maker is used.
WHERE Manufacturer= 'BMW'
WHERE Manufacturer= :Maker
To use a parameter, write the parameter name prefixed by a colon (:)in the SQL statement where you want to use it. As soon as you enter the first character after the colon, an entry is created for the parameter in the Parameters pane. Next, in the Parameters pane, enter an XPath expression to provide the value of the parameter. You can enter as many parameters as you like.
|Note:||In the SQL statement, column and table names from the source database are used, since the SQL statement directly queries the DB. In the XPath expression of parameters, however, you must use the names of nodes in page source trees (Row, RowSet, etc), since it is these trees in which values related to the design are stored.|
Statement built with XPath
You can also use XPath to build an SQL statement. Select Statement built with XPath, and enter the XPath expression that generates the required SQL statement. The advantage of this is that it provides greater flexibility in creating the SQL statement. For example, you can include design tree nodes, other XPath constructs, and end user input to calculate and generate parts of the SQL statement.
To build an SQL statement using XPath, select Statement built with XPath. In the Edit XPath/XQuery Expression dialog that appears, enter the XPath expression and click OK.
Execute once or for every node
The SQL statement can be executed once on the data source, or it can be executed on all the nodes of a custom defined nodeset. If you select the latter option, you need to enter an XPath expression that generates the nodeset. The SQL statement will then be executed for each node in this nodeset. Additionally, you can query the value of the current node of the nodeset by using the variable $MT_TargetNode. This variable can be used, for example, in the definition of a parameter used in the SQL statement (see "SQL statements with parameters" above).
The $MT_DBExecute_Result variable
The nodeset or other value returned by (the SQL statement of) the DB Execute action is stored in MobileTogether Designer's built-in variable $MT_DBExecute_Result. This variable stores the result of the last DB Execute action of the project, and can be used in XPath expressions in other locations in the project.
If the DB Execute action returns a nodeset, you can construct an element and insert the nodeset as a child of the constructed element. Alternatively, you can serialize the nodeset by using the serialize() function, like this: serialize($MT_DBExecute_Result). The two XPath expressions are shown underlined in red in the screenshots below.
If you want to access a specific node in the nodeset, you can access it with an XPath expression. For example, consider the DB Execute action in the screenshot above. Let us say that the SELECT statement returns the following nodeset:
<Row Model="Z3 COUPE 2014"/>
<Row Model="Z3 COUPE 2015"/>
<Row Model="Z3 COUPE 2016"/>
<Row Model="Z4 3.0 SI COUPE 2014"/>
<Row Model="Z4 COUPE 2015"/>
•To access the model name of the first car in the returned nodeset, the XPath expression would be: $MT_DBExecute_Result/DB/RowSet/Row/@Model.
•To access the row of the car with the year 2016 in its model name, the XPath expression would be: $MT_DBExecute_Result/DB/RowSet/Row[@Model[contains(. , '2016')]].