How to test SQL connection parameters

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

When using the SQL Databases Page, Database server 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.

In order to successfully use this tutorial and test the connectivity, the ODBC driver must be already installed on the target machine. This tutorial will not work with ODBC drivers added in the ODBC page.

Go to the SQL Databases page.

Click on the the Test SQL Connection toolbar button to add a new SQL Test Connection Operation to your project.

The next step is to configure the connection string.

The SQL Connection string must adhere to the strict ODBC keywords. The connection string parameters of OLEDB or SQL Client connection type are not supported by Advanced Installer.

Examples of ODBC Connection strings:

Generic SQL Server

Driver=[\{][ODBC_RES_PROP][\}];Server=[SERVER_PROP];Port=[PORT_PROP];Uid=[USERNAME_PROP];Pwd=[\{][PASSWORD_PROP][\}];

Microsoft SQL Server

Driver=[\{]SQLServer[\}];Server=[SERVER_PROP];PORT=[PORT_PROP];Uid=[USERNAME_PROP];Pwd=[\{][PASSWORD_PROP][\}];

For Microsoft SQL Server LocalDB connections only an SQL Server Client ODBC driver can be used (e.g. "SQL Server Native Client 11.0").

MySQL Server

Driver=[\{]MySQL ODBC 5.1 Driver[\}];Server=[SERVER_PROP];Port=[PORT_PROP];Uid=[USERNAME_PROP];Pwd=[\{][PASSWORD_PROP][\}];

The Driver name must be exactly the same with the MySQL ODBC Driver installed on the computer and can be easily obtained from the Predefined SQL Connection Page in the ODBC Driver field.

Oracle Server

Driver=[\{]Microsoft ODBC for Oracle[\}];Server=[SERVER_PROP];Uid=[USERNAME_PROP];Pwd=[\{][PASSWORD_PROP][\}];

Postgre Server

Driver=[\{]PostgreSQL ANSI[\}];Server=[SERVER_PROP];Port=[PORT_PROP];Uid=[USERNAME_PROP];Pwd=[\{][PASSWORD_PROP][\}];Database=[DATABASE_PROP]

The Driver name must be exactly the same with the Postgre ODBC Driver installed on the computer and can be easily obtained from the "Predefined SQL Connection Page" in the ODBC Driver field.

Connection Timeout

Optionally, you can specify a connection timeout (in seconds) for this test. The default is 60 seconds.

Verbose

You can suppress the spawned message box for the connection test operation by unchecking the "Show the Please Wait... message box" option.

Execution

"Test ODBC Connection" custom action will be automatically added to the Custom Actions page :

There are two Execution modes for the Test ODBC Connection operation:

Run in UI Sequence - the "Test ODBC Connection" custom action will be executed in the Wizard Dialog Stage
Attached to an event - the "Test ODBC Connection" custom action will be executed by an UI trigger event. You can select the Dialog and the Control that will contain the event.

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).