How to test SQL connection parameters?

Answer

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.

Go to the Dialog Editor page.

Add the Advanced Installer "SQLConnectionDlg" to the dialogs tree.

Select the [ Next ] button from this dialog and go to the Published Events tab and add a new "SetProperty" Published Event with the following parameters (E.g. for MS SQL Server):

• Name: [SqlConnectionString]
• Argument: Driver=[\{][ODBC_RES_PROP][\}];Server=[SERVER_PROP];Port=[PORT_PROP];Uid=[USERNAME_PROP];Pwd=[PASSWORD_PROP];
• Condition: 1 (it will be shown as AI_INSTALL)

Connection Timeout

Optionally, you can specify a connection timeout (in seconds) for this test. The default is 60 seconds. The timeout can be specified through the Windows Installer property SqlConnectionTimeout. You can set this property statically (from Install Parameters), or dynamically (from the installer UI - associated with an Edit Control, or with a SetProperty Control Event).

You can suppress the spawned message box for the connection test operation by setting the SqlSuppressSpawnMsgBox property to 1 . You can set this property statically (from Install Parameters), or dynamically (from the installer UI - associated with an Edit Control, or with a SetProperty Control Event).

Go to the Custom Actions page and add the predefined UI Custom Action "Test ODBC Connection".

Return to Events Editor tab (Dialog Editor view) and add a new published event on the same [ Next ] button:

• Name: DoAction
• Argument: TestODBCConnection (the custom action name you create earlier)
• Condition: NOT (SqlConnectionString = "")

Edit the existing NewDialog published event on this button and set its condition to: SqlConnectionOK = "1". This will prevent the installation wizard to continue if the connection parameters are incorrect.

If the entered connection parameters are incorrect a message box will display the detailed error information (ODBC Driver response).

This testing functionality will work only in the case of a pre-existing ODBC driver (because at this stage of the installation the ODBC drivers installed by the package are not available yet).

See also: Browse for SQL Servers