Database server
This section allows you to configure SQL scripts and queries that will be executed during installation.
Test SQL Connections and Queries
Use the toolbar 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 button
and 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 toolbar button, the
“New Predefined SQL Connection” context menu item or press
the Insert key while the “Database
server” item is focused.
Add a New Custom SQL Connection
Use the
toolbar button, the “New Custom SQL Connection” context menu
item.
Add a New SQLite 3 Connection
Use
the toolbar button, the “New
SQLite Connection” context menu item.
Add a New SQL Query
Use the toolbar button or the “New SQL Query”
context menu item while the “Database server” item is
focused.
Add a New SQL Script
Use the toolbar button, the “New SQL Script”
context menu item or press 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.
Only the following formats are supported:
- ASCII
- 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.
SQL Transactions
To create
a new transaction use the toolbar 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.
Folders
To easily organize
scripts use the toolbar 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
toolbar button, the “Delete” context menu item or press the
Delete key while a SQL connection, script or
query is selected.
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.
Topics
- 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