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.
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).
Use thetoolbar 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:
Select the created "MS SQL connection" and use thetoolbar button to create a new SQL Query called "MS SQL Query".
Configure it as follows:
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".
Go to the Dialogs Page.
Select the "SQLConnectionDlg" dialog add use thetoolbar 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 thebutton 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.
Build and run the package.
On the "SQLConnectionDlg" dialog enter your ODBC connection parameters and clickbutton.
On the "QueryResultDlg" dialog the combo box control should be populated with the query result set.