Using an SQL Query to interrogate SQL databases

ImportantThe following article uses options that are available starting with the Enterprise edition and project type.

This tutorial will explain how an SQL Query can be used to interrogate SQL databases and save the query returned output (result set) into an installer property.

1. Configure an SQL Connection

Let's suppose during installation you need to interrogate an SQL Server database (e.g. get all "username" entries from a "UserName" table) and save the query result into an installer property. Then, the property value will be used in the installation process (e.g. all usernames will populate a combo box control).

SQL DatabasesGo to the SQL Databases Page, Database server.

New SQL ConnectionUse the [ New Predefined Connection ] toolbar button to create a new SQL Connection called "MS SQL connnection".

NoteThe configuration data for the connection 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.

Configure it as follows:

MS SQL Connection

2. Add the SQL Query to the SQL Connection

New SQL QuerySelect the created "MS SQL connection" and use the [ New SQL Query ] toolbar button to create a new SQL Query called "MS SQL Query".

Configure it as follows:

MS SQL Query

3. Configure the "Query SQL database for data" action

Go to the Custom Actions Page and delete the existing "SqlQueryAction" associated with the above created "MS SQL Query". This custom action is created by default and added with sequence after "Searches" action groups.

Add a new Query SQL database for data custom action without sequence and associate it with the above created "MS SQL Query".

Query SQL database for data custom action

4. Display the query result set into a combo box control

Go to the Dialogs Page.

Select the "SQLConnectionDlg" dialog add use the [ New Dialog ] toolbar button to create a new dialog (i.e. QueryResultDlg). On "QueryResultDlg" add a combo box control with the "QUERY_PROP" associated property (this is the property in which the "MS SQL Query" will dump its result set).

On the [ Next ] button of the "SQLConnectionDlg" dialog add a new published event like this:

  • Event: Execute custom action
  • Argument: SqlQueryAction
  • Condition: 1 (it will be shown as AI_INSTALL)

Execute custom action control event

The "Execute custom action" event will run the "SqlQueryAction" which will trigger the "MS SQL Query" execution.

ImportantAn SQL Query can be executed anytime during the installation process by simply scheduling its associated "Query SQL database for data" custom action anywhere you need during installation.

5. Run the project

BuildBuild and run the package.

On the "SQLConnectionDlg" dialog enter your ODBC connection parameters and click [ Next ] button.

SQLConnectionDlg

On the "QueryResultDlg" dialog the combo box control should be populated with the query result set.

QueryResultDlg dialog