User's Guide Part III. Using SQL Anywhere Chapter 15. Connecting to a DatabaseThe Open Database Connectivity (ODBC) interface is defined by Microsoft Corporation, and is a standard interface for connecting client applications to database management systems in the Windows and Windows NT environments. Many client applications, including application development systems, use the ODBC interface to access a wide range of database systems. These are ODBC-enabled applications. SQL Anywhere supports the ODBC interface.
This section describes how to connect to SQL Anywhere from ODBC-enabled applications.
For information about how to write an ODBC program, see the chapter "ODBC Programming".
ODBC-enabled applications use a set of connection parameters in the manner described above. However, they obtain values for those parameters in a different manner to embedded SQL applications. ODBC defines data sources. Each data source description contains several of the required connection parameters. The other connection parameters are obtained from the user (for instance, you may be presented with a dialog box to enter a user ID and password), or internally by the client application.
SQL Anywhere provides ODBC Version 2.5 support at conformance Level 2, which is the highest level of support for ODBC Version 2.5.
For Windows and Windows NT, SQL Anywhere ODBC support takes place using the Microsoft ODBC driver manager, installed as part of the SQL Anywhere installation. The ODBC driver manager enables different ODBC drivers to run at the same time and allows an ODBC-enabled application to communicate with more than one ODBC driver and data source.
For more information about using ODBC under Windows and Windows NT, see "Using ODBC under Windows and Windows NT".
For DOS and QNX, SQL Anywhere supports ODBC as an API (application programming interface) only.
For more information about using ODBC under DOS and QNX, see "Using ODBC under DOS and QNX".
SQL Anywhere supports ODBC as an application programming interface for OS/2. Additional software from third party vendors does allow the use of the SQL Anywhere ODBC driver from ODBC-enabled client applications through the third party ODBC driver manager.
For more information about using ODBC client applications under OS/2, see "Using ODBC under OS/2"
All the information about installed ODBC database management systems and databases on your machine is held in two files (in Windows 3.x) or in the registry (in Windows 95 and NT). ODBCINST.INI holds the information about different ODBC DBMS drivers installed on your system, including SQL Anywhere. ODBC.INI holds information about available data sources.
You can look at and alter the information in these files using the ODBC Administrator. The SQL Anywhere installation for Windows operating systems installs the ODBC Administrator as one of the icons in your SQL Anywhere group.
The files needed for ODBC client applications to connect to a SQL Anywhere database are as follows for the case of a Windows 95 or NT setup:
Under Windows and Windows NT, ODBC-enabled client applications call the ODBC driver manager. For Windows 3.x, the ODBC driver manager is ODBC.DLL, installed in your Windows SYSTEM subdirectory; for Windows 95 and NT this is ODBC32.DLL, installed in your NT SYSTEM32 subdirectory or your Windows 95 SYSTEM subdirectory. A third-party driver manager is available from Intersolv, Inc., providing similar functionality for OS/2.
Each ODBC-supporting DBMS, including SQL Anywhere, supplies its own ODBC driver, which is called by the ODBC driver manager. Each ODBC driver is a DLL.
The SQL Anywhere ODBC driver is installed as part of your SQL Anywhere setup.
The location of the SQL Anywhere ODBC driver is recorded in ODBCINST.INI, which is kept in your Windows directory or in the registry.
The default Windows character set differs in some cases from that used by DOS in the default (code page 437). The Windows character set is sometimes called the ANSI character set, and the DOS code page is sometimes called the OEM character set.
A translation DLL can be used to convert characters from the ANSI character set (used by a Windows application) to the default character set in use by the database (code page 437), and vice versa. This translation DLL is:
OEM to ANSI character set translation does not affect the alphabetic and numeric characters. It does affect some graphics characters that occupy higher positions in the collation.
The SQL Anywhere ODBC driver requires additional files. Under Windows, DBL50W.DLL needs to be in the DOS path, under Windows 95 or NT, DBL50T.DLL is required; and under OS/2; DBL502.DLL is required. These files are installed in the Windows, NT, or OS2 subdirectories of your SQL Anywhere installation directory.
A language DLL is also required, as specified in the SQLANY.INI file or registry. For Windows 3.x, SQLANY.INI should be in your Windows directory; for Windows 95 and NT SQLANY.INI is a registry. The SQL Anywhere installation does not install SQLANY.INI; but it is created when you run ISQL or Sybase Central. The default language DLL is WL50EN.DLL (English); other languages must be specified separately.
A file or registry entry named ODBCINST.INI holds information about all the ODBC drivers on the computer. When SQL Anywhere is installed, it adds a description of the SQL Anywhere ODBC driver to ODBCINST.INI, which is held in the Windows 3.x system subdirectory, and in the Windows 95 or NT registry. If you start the ODBC Administrator, and click the Driver button, you should see Sybase SQL Anywhere 5.0 listed among the installed ODBC drivers.
You should not have to make any modifications concerning the SQL Anywhere driver.
The description of the SQL Anywhere driver in the Windows 3.x ODBCINST.INI includes the following:
[ODBC Drivers]Sybase SQL Anywhere 5.0=Installed[ODBC Translators]Sybase SQL Anywhere 5.0 Translator=Installed[Sybase SQL Anywhere 5.0]Driver=c:\sqlany50\win\wod50w.dllSetup=c:\sqlany50\win\wod50w.dll[Sybase SQL Anywhere 5.0 Translator]Translator=c:\sqlany50\win\wtr50w.dllSetup=c:\sqlany50\win\wtr50w.dll
The corresponding information is held in the Windows 95 or NT registry.
For each database that you want to access from an ODBC client application on your computer, you need to enter a Data Source in the ODBC.INI file or registry. In Windows and Windows NT, you can enter the Data Source description using the ODBC Administrator. The information contained about each Data Source in ODBC.INI includes several of the connection parameters needed to connect to a database.
Some application development systems, such as PowerBuilder, add this information automatically if you create a data source from within the system. In this case you do not need to use the ODBC Administrator.
The SQL Anywhere sample database information is automatically installed into this file during SQL Anywhere setup. You can see the Data Source information for the sample database using the ODBC Administrator.
If you are having trouble connecting to a SQL Anywhere database from an ODBC-enabled client application, check that the information in the Data Source definition is correct.
For more information about using the ODBC Administrator, see "Using the ODBC Administrator".
Although you can add, remove and modify data source information by directly editing the ODBC.INI file in Windows or by using the registry editor in Windows NT, it is much easier to use the ODBC Administrator program.
The left side of the Administrator window lists the available data sources including the SQL Anywhere sample database.
If you have other ODBC software installed on your computer, you may have other data sources available. Pressing the Drivers button will display a list of the currently installed ODBC drivers, and allow you to install new ODBC drivers or remove drivers.
The following actions are available for data sources:
The SQL Anywhere ODBC driver can access databases through local SQL Anywhere engines or SQL Anywhere network servers.
| Database must exist To add a data source for a database file, the database must already exist. See "Working with Database Objects" for information on creating a new database. You must create a database before using the ODBC Administrator program to add a data source for the database. |
To add a new data source:
The SQL Anywhere ODBC Configuration window contains the following fields, which correspond to connection parameters in a connection string.
For a description of connection parameters and a description of the manner in which they are used to establish a connection with a database, see "Database connection parameters".
The following table lists the fields in the SQL Anywhere ODBC Configuration window.
| Field | Description |
|---|---|
| Data Source Name | This should be a short name for the data source, such as Orders or Accounts Payable |
| Description | A longer description of the data source |
| User ID | (Optional) The user name to be used when connecting. If the user ID is omitted, most applications prompt you for a user ID and password when connecting to the data source |
| Password | (Optional) The password for the supplied User ID. Since the password supplied is stored in ODBC.INI, setting the password here may be a security risk. If the password is omitted, most applications prompt you to enter your password when connecting to the data source |
| Server Name | The name of a SQL Anywhere database engine or server. If not specified, the default engine is used. This field corresponds to the EngineName connection parameter |
| Database Name | If specified, this corresponds to the name of a database already running on a SQL Anywhere database engine or SQL Anywhere network server. This field corresponds to the DatabaseName connection parameter. |
| Database File | If specified, this contains the name of a database file---such as C:\sqlany50\sademo.db. You can use the Browse button to locate a database file name to place in this field. This field corresponds to the DatabaseFile connection parameter. |
| Local, Network, Custom | The command used to run the database software when the named database engine or server is not already executing. You can select Local or Network, as appropriate, if the default settings are satisfactory. Otherwise, select Custom and enter the command including any command line parameters by pressing the Options button |
| Microsoft Applications (Keys in SQLStatistics) | The ODBC specification states that primary and foreign keys should not be returned by SQLStatistics. Some programs (including Microsoft Visual Basic V3.0 and Microsoft Access V1.0 and V1.1) assume that primary and foreign keys are returned by SQLStatistics. Checking this option makes the SQL Anywhere ODBC driver mimic the required behavior so these applications work properly |
| Prevent Driver not Capable Errors | The SQL Anywhere ODBC driver returns a "Driver not Capable" error code because it does not support qualifiers. Some ODBC applications do not handle this error properly. Checking this box disables this error code, allowing these applications to work. |
| Delay AutoCommit until statement close | The ODBC standard specifies that the default mode of operation is to run in auto-commit mode (each statement is committed immediately after it is executed). Some applications do not provide a way for users to override this behaviour. Checking this option causes the SQL Anywhere ODBC driver to delay the commit operation until the statement is closed. |
The following example shows the description of the SQL Anywhere sample database and a sample SQL Anywhere client in the Windows ODBC.INI:
[ODBC Data Sources]SQL Anywhere 5.0 Sample=Sybase SQL Anywhere 5.0[SQL Anywhere 5.0 Sample]driver=c:\sqlany50\win\wod50w.dlldescription=Sybase SQL Anywhere Sample DatabaseDatabaseFile=c:\sqlany50\sademo.dbStart=c:\sqlany50\win\dbeng50w -d[SQL Anywhere 5.0 Sample Client]driver=c:\sqlany50\win\wod50w.dlldescription=SQL Anywhere Client/ServerEngineName=place_server_name_hereStart=c:\sqlany50\win\dbclienw
This section presents some sample SQL Anywhere ODBC data source descriptions. The descriptions are presented as settings in the SQL Anywhere ODBC Configuration window.
The parameters Description, User ID, and Password are not described here. The Description parameter is not required, and the User ID and Password can be provided at connection time from the client application.
The examples describe a data source running the sample database (SADEMO.DB), running as a database named sademo on an engine named SampleServer.
The following settings describe an ODBC data source for a standalone database engine running on the local machine. The database is assumed to be running when the application connects; there is no provision in this data source description for starting up a database engine or for loading a database on the engine.
| Parameter | Setting |
|---|---|
| Data Source Name | Sample Name |
| Server Name | SampleServer |
| Database Name | sademo |
| Database File | No entry |
| Local / Network / Custom option | Local |
| Translator | No entry |
The following settings describe an ODBC data source for a named database file. The description loads the database file on the default local database engine, or connects to a database with the name sademo if it is already running.
| Parameter | Setting |
|---|---|
| Data Source Name | Sample Name |
| Server Name | No entry |
| Database Name | No entry |
| Database File | C:\SQLANY50\SADEMO.DB |
| Local / Network / Custom option | Local |
| Translator | No entry |
The following settings describe an ODBC data source for a database running on a network server. The database is assumed to be running when the application connects; there is no provision in this data source for starting up a database server or for loading a database on the server.
To use this data source you need the SQL Anywhere Client, which is installed with the SQL Anywhere Network Server.
| PowerBuilder and InfoMaker users This chapter contains some information about connecting to a SQL Anywhere network server. The SQL Anywhere network server is not included with PowerBuilder and InfoMaker. |
| Parameter | Setting |
|---|---|
| Data Source Name | Sample Name |
| Server Name | SampleServer |
| Database Name | sademo |
| Database File | No entry |
| Local / Network / Custom option | Network |
| Translator | No entry |
To modify an existing data source:
For a description of the attributes, see "Adding an ODBC data source".
To remove an ODBC data source:
| Database file is not deleted Removing a data source does not delete the database file. It simply deletes the description of the data source from the ODBC.ini file or Windows NT registry. It can be added back as described above. |
An ODBC Administrator program and an ODBC driver manager are available for OS/2 and can be acquired from INTERSOLV, Inc. It provides the same functionality as the ODBC Administrator program and ODBC.DLL driver manager under Windows or Windows NT. The Administrator program is not supplied with SQL Anywhere.
Even if you are not using the ODBC Administrator, the file ODBC.INI is still used to describe the available data sources. Under OS/2, ODBC.INI is a binary file. As such, it is not easily editable. The SQL Anywhere installation creates this file with a definition of the sample data source. The installation also includes an OS/2 command file, ODBCINI.CMD that uses the REXX API to modify the file. This command file can be modified and run to create additional data sources.
The file ODBC.INI is still used to describe the available data sources. This file must be located somewhere in your path. The following example contains a data source definition for the SQL Anywhere sample database:
[ODBC Data Sources]SQL Anywhere 5.0 Sample=Sybase SQL Anywhere 5.0[SQL Anywhere 5.0 Sample]Driver=C:\sqlany50\win\wod50w.dllDescription=Sybase SQL Anywhere Sample DatabaseDatabaseFile=C:\sqlany50\sademo.dbStart=C:\sqlany50\dos\dbeng50
The first section of the file ([ODBC Data Sources]) lists all of the currently defined data sources. In this case there is only one, called SQL Anywhere 5.0 Sample.
The second section of the file ([SQL Anywhere 5.0 Sample]) describes the data source named SQL Anywhere 5.0 Sample. This section contains the following fields:
| Fields | Description |
|---|---|
| Description | A longer description of the data source |
| UID | (Optional) The user name to be used when connecting. If it is omitted, most ODBC applications will prompt you for a User ID and Password when connecting to the data source |
| PWD | (Optional) The password for the supplied User ID. Setting the password here may be a security risk because it is easily viewed. If the password is omitted, most applications will prompt you to enter your password when connecting to the data source |
| DatabaseFile | The name of a SQL Anywhere database file |
| Start | The command used to run the database software when the named database is not already executing. It contains the command including any command line parameters |