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 Dialog Editor page.

Add dialogAdd 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 "Set installer property value" 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 shown as AI_INSTALL)

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

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

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

  • 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 shown as AI_INSTALL)

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

  • Name: [SqlConnectionString]
  • Argument: Driver=[\{]Microsoft ODBC for Oracle[\}];Server=[SERVER_PROP];Uid=[USERNAME_PROP];Pwd=[PASSWORD_PROP];
  • Condition: 1 (it will be shown 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 shown as AI_INSTALL)

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.

SQL Connection String control event

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 Set installer property value Control Event).

TipYou 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 Set installer property value Control Event).

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

Test ODBC Connection CA

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

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

Execute custom action control event

Edit the existing Display a specific dialog 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.

All control events

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