User's Guide Part III. Using SQL Anywhere Chapter 26. Client Application DeploymentThis section describes application settings required on client machines for a deployed application.
When installing SQL Anywhere files as part of the deployment of a SQL Anywhere-based application, the choice of file location should be guided by two principles:
For example, the files belonging to the engine component layer and those which are part of the interface layer should reside in the same directory if the standalone engine is being deployed (or access to their respective directories by modifying the Path statement). Files belonging to the network client component in a client/server deployment should reside in the same directory as the interface layer.
Also, it is recommended that the language-specific string library, WL50EN?.DLL, be placed in the same directory as other database components and not in the Windows system directory to avoid version mismatch problems.
| ODBC driver manager location The Microsoft-provided file ODBC32.DLL (or ODBC.DLL for 16-bit connections) must be located in the Windows system directory, which is typically C:\WINDOWS\SYSTEM for Windows 95/Windows 3.x, or C:\WINNT\SYSTEM32 for Windows NT. |
Open Database Connectivity (ODBC) is a Microsoft Corp.-defined interface for accessing data from those database management systems (DBMSs) which provide an ODBC driver. SQL Anywhere is one such DBMS.
In the ODBC architecture, ODBC-compliant applications pass ODBC API function calls --- as defined by the Microsoft-authored ODBC driver manager --- to a vendor-supplied ODBC driver. This driver does the work of translating ODBC calls into commands the DBMS can use. In this manner, the same client application can interact with a variety of data sources from a variety of vendors.
As outlined in "Required files: interface layer", the files essential for an ODBC connection include:
Beyond the requirement that these files be present on the system, the range of options for establishing an ODBC connection between client application and database is extensive.
| Windows 3.x ODBC connections The configurations options outline in this section apply equally to 16-bit ODBC connections, with some notable differences. These include: the use of ODBC.DLL as the ODBC driver manager; and the use of similarly-named initialization files instead of Registry keys to store ODBC driver and data source configuration parameters. For example, the file ODBCINST.INI contains 16-bit ODBC driver settings. Where 32-bit ODBC configurations can have separate user and system data source definitions of different scope and availability, 16-bit ODBC configurations store all data source definitions in the file ODBC.INI. |
The following outlines some of the options and restrictions for configuring an ODBC connection using Windows 95 or Windows NT and a client application that uses a 32-bit ODBC connection.
In a typical system (such as one where SQL Anywhere and the ODBC support option have been installed), two system configuration changes are usually made to accommodate ODBC communications:
With SQL Anywhere, this takes place during an normal installation using the SQL Anywhere installation program, and selecting the ODBC support option.
Typically created by the user, a user data source identifies which ODBC driver to use during the ODBC connection, in addition to a number of parameters used by the DBMS. Such parameters can include: which database file/server/engine name to use, switch options, user ID and password. If not created programmatically, a user data source is created using the ODBC Administrator, which offers a list of ODBC drivers to choose from and is available by opening the Control Panel window. The ODBC Administrator generates the list based on the above system configuration change, the identifying and configuring of ODBC drivers in the system Registry.
When SQL Anywhere is installed from the installation CD, changes are made to the system Registry to identify and configure a SQL Anywhere ODBC driver. This identifies the location of the SQL Anywhere ODBC driver and a driver configuration parameter.
Every ODBC driver is identified to the system by entries in the registry in the following locations:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\drivername
and
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers.
| ODBC driver configuration required If the SQL Anywhere installation program is not used on the system running the client application, the SQL Anywhere ODBC driver is not likely configured properly. Changes to the Registry to properly configure the SQL Anywhere ODBC driver must made before that ODBC driver can be used in an ODBC connection. |
A user data source is the most often used method, or configuration, for connecting a client application to an external data source because it can contain information such as which database is to be used. If a user data source is created using the ODBC Administrator (as opposed to creating it programmatically with new entries to the Registry) the SQL Anywhere driver displays the configuration dialog for entering settings for either the location of the database file, the name of the database engine or the name of the database server to used as the source of data, as well as any start up parameters and other options.
Once created, a user data source is then available to all processes available to that current system user.
When an attempt is made to connect to the database using the defined user data source, the SQL Anywhere ODBC driver does the work of prompting users for the user ID and password if those connection parameters are not provided in either the user data source settings, system data source settings or ODBC connection string.
Each user data source is identified to the system by entries in the Registry in the following locations:
HKEY_CURRENT_MACHINE\SOFTWARE\ODBC\ODBC.INI\userdatasourcename
and
HKEY_ CURRENT _MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources.
| Caution: ODBC settings are easily viewed User data source configurations often 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 view using the Windows registry editors REGEDIT.EXE or REGEDT32.EXE, which are provided by Microsoft with the operating system. |
A system data source is identical to a user data source except in the scope of its availability.
System data sources identify an ODBC connection beyond the scope of the current user. For example, when a user creates a user data source the definition is stored in the part of the registry containing settings for the specific user currently logged on to the system. Creating a system data source, however, makes that definition available to all users and to Windows NT or Windows 95 services, which run regardless of whether a user is logged onto the system or not.
Each system data source is identified to the system by entries in the Registry in the following locations:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\systemdatasourcename
and
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources.
The name identifying a user data source or system data source can be referred to as a data source name (DSN). Identifying the data source name in an ODBC configuration string in this manner, "DSN=userdatasourcename", identifies which user data source or system data source from the Registry is to be used for the ODBC connection.
Both system data source and user data source definitions in the Registry are searched when a DSN is identified in the ODBC connection string. The difference is that only the user data source entries for the currently logged on user are available to be scanned. The same system data source definitions are available to all users. Therefore a correctly configured system data source named "MyApp" will mean any user can use that ODBC connection by identifying "DSN=MyApp" in the ODBC connection string.
During the SQL Anywhere installation, a user data source definition is created permitting a connection to the SQL Anywhere sample database, but no system data source definitions are created.
The following table illustrates the implications to the user and developer when a DSN exists, when it is provided and the effects of including different parameters in the DSN.
| To connect a client application to a database ... | The ODBC connection string must identify... | The user must supply... |
|---|---|---|
| When the ODBC connection string identifies: a fully-configured DSN that contains the ODBC driver name and location; the name of the database file/engine/server; startup parameters; and the user ID and password. | No additional information | No additional information. |
| When the OBDC connection string identifies a DSN containing only the name and location of the ODBC driver. | The name of the database file/engine/server; and, optionally, the user ID and the password. | User ID and password if not provided in the DSN or ODBC connection string. |
| When the system that has no appropriately configured DSN. | The name of the ODBC driver to be used, in the format "Driver = {ODBCdrivername}"; the name of the database, the database file or the database server; and, optionally, other connection parameters such as user ID and password. | User ID and password if not provided in the ODBC connection string. |
For more information on ODBC connections and configurations, see the following:
| Engine name must reflect deployed connection requirements If you develop an application on a standalone engine and deploy in a client/server environment or with a runtime engine, the start command option must reflect the agent started by your user, such as the SQL Anywhere Client or the runtime database engine. |
SQL Anywhere databases distributed on read-only media such as a CD-ROM require the use of a Write File. This file records changes made to a database stored on a CD-ROM and is located on a read/write storage media such as a hard disk.
For more information, "The Write File utility".