Effective Database Management Solutions

Effective Database Management Solutions

Michael Lv8

Effective Database Management Solutions

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

Test SQL Connection or QueryUse 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.

NoteOnly predefined and custom connections can be tested.

https://techidaily.com

Add a New Predefined SQL Connection

Add ConnectionUse 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

Add Custom ConnectionUse the [New Custom SQL Connection ] toolbar button, the “New Custom SQL Connection” context menu item.

Add a New SQLite 3 Connection

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

https://techidaily.com

Add a New SQL Query

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

https://techidaily.com

Add a New SQL Script

Add ScriptUse 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)

NoteIf no SQL Scripts or Queries are added under a connection, Advanced Installer will not establish that connection.

https://techidaily.com

TipYou 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

New TransactionTo create a new transaction use the [Transaction ] toolbar button or the “New Transaction” context menu item.

https://techidaily.com

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.

NoteFor details about SQL transactions and Advanced Installer implementation behaviour, please see this article .

Folders

New FolderTo 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…

ImportantYou 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

DeleteUse the [Delete ] toolbar button, the “Delete” context menu item or press theDelete key while a SQL connection, script or query is selected.

https://techidaily.com

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).

TipYou can set the order of execution for SQL connections. scripts and queries by drag & drop in the tree.

https://techidaily.com

Topics

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

Also read:

  • Title: Effective Database Management Solutions
  • Author: Michael
  • Created at : 2024-10-09 19:27:58
  • Updated at : 2024-10-11 07:27:00
  • Link: https://fox-making.techidaily.com/effective-database-management-solutions/
  • License: This work is licensed under CC BY-NC-SA 4.0.