Using an SQL Query to interrogate SQL databases
The 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).
Go to the SQL Databases Page, Database server.
Use the
toolbar button to create a new SQL Connection called "MS SQL
connnection".
The 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:
2. Add the SQL Query to the SQL Connection
Select the created
"MS SQL connection" and use the
toolbar button to create a new SQL Query called "MS SQL
Query".
Configure it as follows:
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".
4. Display the query result set into a combo box control
Go to
the Dialogs Page.
Select the "SQLConnectionDlg" dialog add use the 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 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)
The "Execute custom action" event will run the "SqlQueryAction" which will trigger the "MS SQL Query" execution.
An 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
Build and run the
package.
On the "SQLConnectionDlg" dialog enter your ODBC connection parameters and click button.
On the "QueryResultDlg" dialog the combo box control should be populated with the query result set.
