Connection information deployment

ODBC client connection information is generally deployed as an ODBC data source. You can deploy an ODBC data source in one of the following ways:

Programmatically Add a data source description to your end-user's registry or ODBC initialization files.
Manually Provide your end users with instructions, so that they can create an appropriate data source on their own computer.
On Windows, you create a data source manually using the ODBC Data Source Administrator, from the User DSN tab or the System DSN tab. The SQL Anywhere ODBC driver displays the configuration window for entering settings. Data source settings include the location of the database file, the name of the database server, and any start up parameters and other options. Context sensitive help is accessed by clicking the Help button. Help text is provided locally using sacshelp12.chm. If this file is not present, then help is provided over the Internet from DocCommentXchange using the system's default Internet browser.

On Unix platforms, you can create a data source manually using the SQL Anywhere dbdsn utility. Data source settings include the location of the database file, the name of the database server, and any start up parameters and other options.

This section provides you with the information you need to know for either approach.

Types of data source (Windows)

There are three kinds of data sources: User Data Sources, System Data Sources, and File Data Sources.

User Data Source definitions are stored in the part of the registry containing settings for the specific user currently logged on to the system.
System Data Sources, however, are available to all users and to Windows services, which run regardless of whether a user is logged onto the system or not. Given a correctly configured System Data Source named MyApp, any user can use that ODBC data source by providing DSN=MyApp in the ODBC connection string.
File Data Sources are not held in the registry, but are stored on disk. A connection string must provide a FileDSN connection parameter to use a File Data Source. The default location of File Data Sources is specified by the HKEY_CURRENT_USER\Software\ODBC\odbc.ini\ODBC File DSN\DefaultDSNDir registry entry or by the HKEY_LOCAL_MACHINE\Software\ODBC\odbc.ini\ODBC File DSN\DefaultDSNDir registry entry (if the former is not defined). The path can be included in the FileDSN connection parameter to help locate the File Data Source when it is located elsewhere.

Data source registry entries (Windows)

User and System Data Source definitions are stored in the Windows registry. The simplest way to ensure the correct creation of registry entries for data source definitions is to use the SQL Anywhere dbdsn utility to create them.

Otherwise, you must create a set of registry values in a particular registry key.

For User Data Sources, the registry key is as follows:

HKEY_CURRENT_USER\
   SOFTWARE\
      ODBC\
         ODBC.INI\
            user-data-source-name

For System Data Sources, the registry key is as follows:

HKEY_LOCAL_MACHINE\
   SOFTWARE\
      ODBC\
         ODBC.INI\
            system-data-source-name

The key contains a set of registry values, each of which corresponds to a connection parameter (except for the Driver string). The Driver string is automatically added to the registry by the Microsoft ODBC Driver Manager when a Data Source is created using the Microsoft ODBC Data Source Administrator or the dbdsn utility. For example, the SQL Anywhere 12 Demo key corresponding to the SQL Anywhere 12 Demo System Data Source Name (DSN) contains the following settings for 32-bit Windows:

Value name	Value type	Value data
AutoStop	String	YES
DatabaseFile	String	C:\Users\Public\Documents\SQL Anywhere 12\Samples\demo.db
Description	String	SQL Anywhere 12 Sample Database
Driver	String	C:\Program Files\SQL Anywhere 12\bin32\dbodbc12.dll
Password	String	sql
ServerName	String	demo12
StartLine	String	C:\Program Files\SQL Anywhere 12\bin32\dbeng12.exe
UserID	String	DBA

Note

It is recommended that you include the ServerName parameter in connection strings for deployed applications. This ensures that the application connects to the correct server if a computer is running multiple SQL Anywhere database servers and can help prevent timing-dependent connection failures.

In these entries, for 64-bit Windows, bin32 would be replaced by bin64.

On 64-bit Windows, there is only one registry entry for User Data Sources that is shared by both 64-bit and 32-bit applications. For these types of data sources, it is recommended that you remove the path in the Driver string. If the path designates the 64-bit ODBC driver, then a 32-bit application that connects/disconnects often will incur a performance penalty (as well as a memory leak) when using the Microsoft ODBC Driver Manager. Similarly, If the path designates the 32-bit ODBC driver, then a 64-bit application that connects/disconnects often will incur a similar penalty. At the time of writing, this is a flaw in the Microsoft ODBC Driver Manager. For this reason, System Data Sources are a better choice for 64-bit Windows systems.

In addition, you must add the data source name to the list of data sources in the registry. For User Data Sources, you use the following key:

HKEY_CURRENT_USER\
   SOFTWARE\
      ODBC\
         ODBC.INI\
            ODBC Data Sources

For System Data Sources, use the following key:

HKEY_LOCAL_MACHINE\
   SOFTWARE\
      ODBC\
         ODBC.INI\
            ODBC Data Sources

The value associates each data source with an ODBC driver. The value name is the data source name, and the value data is the ODBC driver name. For example, the System Data Source installed by SQL Anywhere is named SQL Anywhere 12 Demo, and has the following value:

Value name	Value type	Value data
SQL Anywhere 12 Demo	String	SQL Anywhere 12

Caution

ODBC Data Source Name settings are easily viewed. User Data Source configurations can contain sensitive database settings such as a user's ID and password. These settings are stored in the registry in plain text, and can be viewed using the Windows Registry editors regedit.exe or regedt32.exe, which are provided by Microsoft with the operating system. You can choose to encrypt passwords, or require users to enter them when connecting.

Required and optional connection parameters

You can identify the data source name in an ODBC connection string in this manner,

DSN=UserDataSourceName

On Windows, when a DSN parameter is provided in the connection string, the User Data Source definitions in the Windows Registry are searched, followed by System Data Sources. File Data Sources are searched only when FileDSN is provided in the ODBC connection string.

The following table illustrates the implications to the user and the application developer when a data source exists and is included in the application's connection string as a DSN or FileDSN parameter.