Customizable Pre-Configured Database Access Interface

Table of Contents

• Introduction
• Registration
• Using Advanced Installer
  • GUI
  • Working with Projects
  • Installer Project
    * Product Information
    * Resources
    * Package Definition
    * Requirements
    * User Interface
    * System Changes
    * Server
    * Internet Information Services Page”)
    * ODBC
    * SQL Databases
    * Database server
    * Predefined SQL Connection
    * Custom SQL Connection
    * SQLite 3 Connection
    * SQL Query Tab
    * SQL Script Tab
    * Script Replacements Tab
    * SSRS Deployments
    * SQL Operations
    * SharePoint Page
    * Silverlight Page
    * Custom Behavior
  • Patch Project
  • Merge Module Project
  • Updates Configuration Project
  • Windows Store App Project
  • Modification Package Project
  • Optional Package Project
  • Windows Mobile CAB Projects
  • Visual Studio Extension Project
  • Software Installer Wizards - Advanced Installer
  • Visual Studio integration
  • Alternative to AdminStudio/Wise
  • Replace Wise
  • Migrating from Visual Studio Installer
  • Keyboard Shortcuts
  • Shell Integration
  • Command Line
  • Advanced Installer PowerShell Automation Interfaces
• Features and Functionality
• Tutorials
• Samples
• How-tos
• FAQs
• Windows Installer
• Deployment Technologies
• IT Pro
• MSIX
• Video Tutorials
• Advanced Installer Blog
• 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.

Predefined SQL Connection Page

This page allows you to configure an SQL connection.

General

Condition

Condition the execution of all scripts from this connection. Edit this field usingSmart Condition Edit Control .

When no SQL Script or SQL Query is executed during installation the SQL Connection will not be established.

Connection Type

Select the SQL database server type you want to connect to. The following SQL servers are available:

• Microsoft SQL Server / MSDE
• Microsoft SQL Azure
• MySQL Server
• Postgre SQL Server
• Oracle SQL Server

Connection Mode

You can connect to the SQL server through an:

• ODBC Driver
• ODBC Data Source

ODBC Driver/Data Source

Specify the ODBC Driver or Data Source Name. Use the [Edit… ] button to insert a reference to a property.

The ODBC Driver/Data Source combo will be populated with the configured ODBC drivers or Data Sources in the project as well as the available ODBC drivers or Data Sources on your machine.

It is recommended to use an Unicode compatible ODBC driver.

For Microsoft SQL Server LocalDB connections only an SQL ServerClient ODBC driver can be used (e.g. “SQL Server Native Client 11.0”).

Use 64-bit ODBC resource

If this option is enabled, the connection will use the 64-bit DSN or driver.

Server

Specify the server host name. Use the [… ] button to insert reference to a property.

If the database server is located on the target machine on which the package is installed you can use localhost. For a Microsoft SQL Server connection use (local) instead. For a Oracle SQL Server connection use OracleServer instead.

Port

Specify the connection port. Use the [… ] button to insert a reference to a property.

When using a Microsoft SQL Server connection type this field is non-editable. The default SQL Server port number (1433) will be used. To use a custom port number you can specify it in the “Server” field like this:[SERVER_PROP],[PORT_PROP].

Login timeout

Set the login timeout interval (in seconds) for this database connection. A value of “0“ (zero) means the login timeout is disabled, and the connection will wait indefinitely for a server response. The range tolerance for this value is dependent on the specific ODBC driver used and its standard compliance / implementation.

Connection and all its scripts are verbose

When using the verbose mode for an SQL connection, if an error occurs when connecting or during the execution of one of its SQL scripts, a dialog box will be shown containing the ODBC error response.

Your users will never see the error during the installation if you disable this option, so it is very important that you design your application to check for the integrity of your databases or have it correctly designed so it can handle cases of missing or partially populated databases, as this might happen if your SQL scripts fail to execute.

Log information to file

This option is only available for SQL Scripts.

If this option is enabled and an error occurs, a log file(AI_SQLScripts.log) containing the error message will be automatically generated by the installer in the user’s Temp folder, .

Authentication

Trusted Connection

Enable this option if you wish an SQL connection to use Windows integrated authentication system. When using a trusted connection the current user’s credentials are used.

This option can be enabled at install time by setting theSQL_TRUSTED_CONNECTION property to 1.

In case of connecting to Microsoft SQL Server Desktop Engine (MSDE) you should use a trusted connection.

Username

User name for the database server.

Password

User password for the database server.

This password is ignored if you set a password property.

Confirm Password

Confirm user password.

The supplied password will be stored securely (encrypted) both in the project file and the MSI package.

Use a property to set password

The property used at install time if you want the password to be set by the user. Use the [… ] button to select it.

Catalogue

Database

Specify the name of the database you want to use.

Create database if it does not exist

Using this option will ensure the creation of the database if it doesn’t exist.

If no SQL Scripts are defined but you still want to use this option to create the database on the target machine you can add a script with a simple query. Something likeUSE database_name;.

The Catalogue option is available only when connecting to the database server through an ODBC driver.

A use database statement executed as part of an SQL Script will change the active database for all the subsequent SQL Scripts that are next in the list for execution.

When you connect through a Data Source the following attributes have to be configured in the ODBC Data Source:

• Server
• Port
• Database

All this information can be collected with the Advanced Installer MSI dialog “SQLConnectionDlg”. You can add this dialog from the Dialog Editor view and customize it or you can create your own. When using an MSI dialog to collect the data, in the formatted edit fields: ODBC Driver/Data Source, Server, Port, Username, Password, Database insert references to those respective Windows Installer properties associated with the MSI dialog edit controls.

The following fields are of Formatted Type and can be edited with Smart Edit Control :

• Server
• Port
• Username
• Database

When using the SQL Scripts functionality and you require to prompt the user for ODBC connection parameters (for an ODBC driver), you might want to test if the connection works before proceeding. This will prevent an installation rollback due to incorrect connection parameters when executing the SQL scripts. Please see Test SQL Connection article for details.

You can choose to let the installing user to pick an SQL Server to connect to, from the available Microsoft SQL Servers on the network. Advanced Installer can automaticallybrowse the network to discover any SQL Server (instances) on-line. Also, you have the possibility of allow the user to select a database from a list of available databases to which the user is allowed to connect . Please see Browse for SQL Servers and Show Databases from an SQL Server articles for details.

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: