Adding an Execute SQL statement event action

Use the Execute SQL statement action to run an SQL statement after a specified event occurs.

1. Select the object in the editor window. The [objectname] Behavior window opens.

   From the Events view, double-click the event that you want to trigger the Execute SQL statement action.

2. Click the Add icon. The Add New Action window opens.
3. Select Execute SQL statement in the actions tree and click Next.
   Note: To quickly find the required event action, type the name of the action in the filter text field.
4. Enter the SQL statement in the SQL statement field.

   The SQL statement can contain parameters. If you want to include global or scene parameters in your SQL statement, you must enter the SQL statement as an expression, much as you would enter an expression into a property value. For example, to run the following statement:

   Update MyTable Set Shipped = 1
   Where CustomerID = [value of g_nCustID global variable]
   

   You would enter the following text in the SQL Statement field:

   ="Update MyTable Set Shipped = 1 Where CustomerID = " + g_nCustID
   

5. Optional: If the SQL statement affects the content of a scene viewed by your user, be sure to select the Refresh the display on completion check box.
6. Optional: Select Display a message box indicating the result of the operation if you want to display a message about the success or failure of the execute SQL statement.

   If you decide to display a message box indicating the result of the operation, enter the text of the message that you want to display in both the Success field and the Failure field.

   Note: Use $ (1) to include the system output message as part of the success or failure message text.
7. Select Save the success or failure (and any return value) in a specified result parameter to save the result of the executed SQL statement in either a local or global parameter.

   When you select this check box the Result parameter lists become available.

   The local and global parameters that have been created for your dashboard are listed in the Result parameter drop-down lists.

   Select the local or global parameter that you want to use to store the result information.

   The results will be stored as follows:

   • If the SQL statement returns one or more values, the first column in the first row is placed in your selected global or scene parameter. For example, the statement select count(*) from MyTable will place the count in your selected parameter. The statement select A, B, C from MyTable will place the value of A from the first row in your selected parameter.
   • If the statement does not produce results, the success or failure of the statement execution is placed in your selected parameter. For example, the statement update MyTable Set A = 3 where CustomerID = 1 will place true or 1 in your selected parameter if the statement executed successfully, or false or 0 if the statement failed to execute. When this parameter is initially created, it has a data type of Boolean. You can modify the name and data type of the global parameter.
8. Click Finish.
   The Execute SQL statement action is added. The Add New Action window closes.
9. From the [objectname] Behavior window, click Apply to apply all actions that have been added for the event. Click OK to close the [objectname] Behavior window.