How to test SQL connection parameters

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

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

Dialog editorGo to the SQL Databases page.

Test Sql Connection Toolbar buttonClick 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.

Caution!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];

NoteTo escape characters in an SQL connection string you must enclose them between { and } characters (e.g. To escape a password value like ;1234, the connection string should contain Pwd={;1234};. Since Advanced Installer uses formatted fields the syntax will become something like Pwd=[\{][PASSWORD_PROP][\}]; where PASSWORD_PROP public property will be dynamically resolved at runtime to ;1234.)

Microsoft SQL Server

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

TipFor 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];

TipThe 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]

TipThe 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

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

Test ODBC Connection CA

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.

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

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

NoteSee also: Browse for SQL Servers, Show Databases from an SQL Server and Configure most common SQL Server connections