This section allows you to configure SQL scripts and queries that will be executed during installation.
Test SQL Connections and Queries
Use thetoolbar button or the “Test” context menu item while the connection/query you want to test is selected.
The test is done by running a temporary built MSI, that is not installed on your machine, it is used just to launch the custom action and have access to the installer session so it can set the result back in the properties you selected when testing a query, or simply establishing a connection with the specified database when testing a connection.
The test is started by default using the connection details specified in the project, you can change these when the MSI dialog will appear. After you are sure the connection details are correct press the buttonand the test will start. If you are testing a query you will see the data retrieved in the next dialog. If you have multiple result set columns, you can select them one by one from the correspondent combo, to see their values.
Only predefined and custom connections can be tested.
Add a New Predefined SQL Connection
Use the Insert key while the “Database server” item is focused.toolbar button, the “New Predefined SQL Connection” context menu item or press the
Add a New Custom SQL Connection
Use thetoolbar button, the “New Custom SQL Connection” context menu item.
Add a New SQLite 3 Connection
Use thetoolbar button, the “New SQLite Connection” context menu item.
Add a New SQL Query
Use thetoolbar button or the “New SQL Query” context menu item while the “Database server” item is focused.
Add a New SQL Script
Use the + key while the “Database server” item is focused. You can add an existent script file from your hard drive or a new inline script.toolbar button, the “New SQL Script” context menu item or press the
Only the following formats are supported:
- UTF8 with BOM
- UTF16 (UCS-2 Little Endian and UCS-2 Big Endian)
If no SQL Scripts or Queries are added under a connection, Advanced Installer will not establish that connection.
You can select multiple files to be added in a single operation, avoiding repetitive and time consuming steps, for projects that contain a large number of scripts.
To create a new transaction use thetoolbar button or the “New Transaction” context menu item.
By default an ODBC driver is in auto-commit mode; that means each statement is committed immediately after it is executed. Advanced Installer allows you to configure transaction isolation units. You can group one or several SQL script files in a single transaction unit, hence ensuring the query changes will be atomically committed. At the beginning of each transaction unit, the ODBC driver is put in manual commit mode, and at the end of the unit the changes are committed (if everything went OK), otherwise all changes from the isolation unit are rolled back. After exiting a transaction unit, the ODBC driver is put back in auto-commit mode, so that your following, non-isolated, batch files will execute/commit automatically.
For details about SQL transactions and Advanced Installer implementation behaviour, please see this article.
To easily organize scripts use thetoolbar button or the “New Folder” context menu item.
Organizing scripts into folders allows you to easily track and maintain projects containing a large number of scripts. Scenarios that you could implement are to have folders grouping scripts in the following categories:
- install, upgrade, modify, remove
- per-version folders, containing scripts that should be executed only if a specific version of your application is installed
- special operations scripts, like folders containing scripts for backups, DB schema updates, data insertion, etc...
You can edit the execution options for multiple scripts at once by selecting the transaction or folder under which the scripts are present. The only setting which must be set separately for each script is the source script itself.
Renaming an SQL Connection, Script or Query
Use the “Rename” context menu item or press the F2 key while an SQL connection, script or query is selected.
Deleting an SQL Connection, Script or Query
Use the Delete key while a SQL connection, script or query is selected.toolbar button, the “Delete” context menu item or press the
Impersonate installing user
Use the “Impersonate installing user” context menu option. You can check this option if you want to run all SQL scripts and queries as the user installing the application, not under the Local System account (this option is useful when using Trusted SQL Connections with Windows integrated authentication).
You can set the order of execution for SQL connections. scripts and queries by drag & drop in the tree.
- Predefined SQL Connection
Configure a predefined SQL connection.
- Custom SQL Connection
Configure a custom SQL connection.
- SQLite 3 Connection
Configure an SQLite database connection.
- SQL Query Tab
Configure an SQL Query
- SQL Script Tab
Configure an SQL Script
- Script Replacements Tab
Handle an SQL script's replacements