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, based on the SQL Server:

Generic 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 showed as AI_INSTALL)

Microsoft SQL Server

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

MySQL Server

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

Oracle Server

• Name: [SqlConnectionString]
• Argument: Driver=[\{]Microsoft ODBC for Oracle[\}];Server=[SERVER_PROP];Uid=[USERNAME_PROP];Pwd=[PASSWORD_PROP];
• Condition: 1 (it will be showed as AI_INSTALL)

Postgre Server

• Name: [SqlConnectionString]
• Argument: Driver=[\{]PostgreSQL ANSI[\}];Server=[SERVER_PROP];Port=[PORT_PROP];Uid=[USERNAME_PROP];Pwd=[PASSWORD_PROP];Database=[DATABASE_PROP]
• Condition: 1 (it will be showed 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).

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.