13.4 SQL Script and Program Actions¶
Three action types go beyond navigation: Script, SQL script and Launch application. They can automate tasks – e.g. start an import, update a table or open a file – but they run with real effect on the user's PC and on your database.
[!WARNING] These actions run as soon as a user clicks, without asking. A script or program runs with the user's Windows rights; an SQL script runs on the data warehouse with the project's database connection, so it can change data whatever the user's own rights are. Only add them when you know exactly what they do, and protect the workbooks that contain them.
SQL Script¶
The SQL is run on the project's data warehouse. Choose how:
| Mode | Effect |
|---|---|
| Execute and show message | Runs the SQL and shows SQL script executed successfully or the database's error |
| Execute in background (no message) | Runs it silently |
| Open in SQL Explorer | Opens the SQL in SQL Explorer without running it |
| Execute in SQL Explorer | Opens and runs it in SQL Explorer, showing the result |
SQL editor... opens a larger editor. The clicked value can't be used in the SQL.
Example: a button Update targets that runs a stored procedure with Execute and show message.
Script¶
Choose the Script language – VBScript, JScript or PascalScript – and write the script, or click Open editor for the script editor:
- Save (Ctrl+S), Execute (Ctrl+E) to run it right away, Validate to check it;
- Open (Ctrl+O) loads the script from a text file;
- Insert field ... adds the clicked-value fields, e.g.
{SelectedValueID}– see 13.2 Adding Actions.
Scripts have a System object with, among others, System.SendMail, System.Log, System.URLEncode, System.AppName and System.ComputerName.
Errors in a script aren't shown to the user when the action runs – test it in the script editor with Execute first.
Launch Application¶
Starts a program, a batch file or a document on the user's PC, e.g. C:\Tools\export.bat. Parameters after the path aren't supported – use a script to start a program with parameters. If the field is empty, the user picks the program when clicking. The program must exist on every PC where the workbook is used.
Actions in the Distributions + Actions Folder¶
Older projects can have stand-alone actions in the main window's Distributions + actions folder. Double-click one to view or change it in Edit action, which then also has a General tab with Name, Folder and Description. New items in this folder are distributions – see Chapter 18 Distributions.
Tips – SQL Script and Program Actions¶
- Prefer a stored procedure in the database over long SQL in the action – it's easier to maintain and test.
- Describe in the workbook's description what its buttons do.
Common Issues – SQL Script and Program Actions¶
Problem: Nothing happens when clicking a script button. - Solution: The script probably fails. Open it in the script editor and click Execute to see the error.
Problem: The SQL script fails with a permission error. - Solution: The project's database user may not be allowed to run it. Ask your administrator.