Optimizing Your Data Storage: The Ultimate Guide to Databases Servers

Table of Contents

Disclaimer: This post includes affiliate links

If you click on a link and make a purchase, I may receive a commission at no extra cost to you.

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 [Test ] 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 [Next ] 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 [New Predefined SQL Connection ] 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 [New Custom SQL Connection ] toolbar button, the “New Custom SQL Connection” context menu item.

Add a New SQLite 3 Connection

Use the [New SQLite Connection ] toolbar button, the “New SQLite Connection” context menu item.

Add a New SQL Query

Use the [New SQL Query ] toolbar button or the “New SQL Query” context menu item while the “Database server” item is focused.

Add a New SQL Script

Use the [New SQL Script ] 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 [Transaction ] toolbar button or the “New Transaction” context menu item.

By default an ODBC driver is inauto-commit mode; that means each statement is committed immediately after it is executed. Advanced Installer allows you to configuretransaction 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 [Folder ] 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 theF2 key while an SQL connection, script or query is selected.

Deleting an SQL Connection, Script or Query

Use the [Delete ] toolbar button, the “Delete” context menu item or press theDelete 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

Did you find this page useful?

Please give it a rating:

Thanks!

Report a problem on this page

Information is incorrect or missing

Information is unclear or confusing

Something else

Can you tell us what’s wrong?

Send message

Fox Within

Optimizing Your Data Storage: The Ultimate Guide to Databases Servers