Working with ODBC data sources

User's Guide
PART 1. Working with Databases
CHAPTER 2. Connecting to a Database

Working with ODBC data sources

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

Adaptive Server Anywhere can use ODBC data sources on UNIX as well as on Windows operating systems. For information, see Using ODBC data sources on UNIX.

Where data sources are held

When you connect to a database using ODBC, you do so using an ODBC data source. The data source contains a set of connection parameters. You need an ODBC data source on the client computer for each database to which you wish to connect.

You can store a set of Adaptive Server Anywhere connection parameters as an ODBC data source. Data sources can be stored in the system registry or as files.

If you have a data source, your connection string can simply name the data source to use:

Data source You can reference a data source in the registry using the DSN connection parameter:
```
DSN=my data source
```
File data source You can reference a data source held in a file using the FileDSN connection parameter:
```
FileDSN=mysource.dsn
```

Embedded SQL can use data sources

Embedded SQL applications such as Interactive SQL and the other Adaptive Server Anywhere database administration utilities can also use ODBC data sources, even though they are not ODBC applications.

Creating an ODBC data source

For Windows operating systems, the ODBC Administrator provides a central place for managing ODBC data sources. You can start the ODBC Administrator from your Adaptive Server Anywhere program group.

To create an ODBC data source:

Start the ODBC Administrator. From the User DSN tab, click Add.
Select Adaptive Server Anywhere 6.0 from the list of drivers, and click Finish. The Adaptive Server Anywhere ODBC Configuration window is displayed.

Many of the fields in this window are optional. Click the question mark at the top right of the window and click an entry field to find more information about that field.
When you have specified the parameters you need, click OK to close the window and create the data source.

You can edit a data source by selecting a data source from the list in the ODBC administrator window and clicking Configure.

Creating a File Data Source

Data sources are stored in the system registry. File data sources are an alternative, which are stored as files. File data sources typically have the extension .dsn. They consist of sections, each section starting with a name enclosed in square brackets. DSN files are very similar in layout to initialization files.

File data sources can be distributed

One benefit of file data sources is that you can distribute the file to users. If the file is placed in the default location for file data sources, it is picked up automatically by ODBC. In this way, managing connections for many users can be made simpler.

Embedded SQL applications can also use ODBC file data sources.

To create an ODBC file data source:

Start the ODBC Administrator. From the File DSN tab, click Add.
Select Adaptive Server Anywhere 6.0 from the list of drivers, and click Next.
Follow the instructions to create the data source

Using ODBC data sources on UNIX

On UNIX operating systems, ODBC data sources are held in a file named .odbc.ini. A sample file is as follows:

[My Data Source]
ENG=myserver
CommLinks=tcpip
UID=dba
PWD=sql

The database server looks for this file as follows:

The ODBCINI environment variable
The ODBCHOME and HOME environment variables
The user's home directory
The path.

Using ODBC data sources on Windows CE

Windows CE does not provide an ODBC driver manager or an ODBC Administrator. On this platform, Adaptive Server Anywhere uses ODBC data sources stored in files. You can specify either the DSN or the FileDSN keyword to use these data source definitions—on Windows CE (only) these are synonyms.

Data source location

The data source files are searched for in the following locations:

The directory from which the ODBC driver ( dbodbc6.dll ) was loaded. This is usually the Windows directory.
The directory specified in Location key of the Adaptive Server Anywhere section of the registry. This is usually the same as the Adaptive Server Anywhere installation directory. The default installation directory is:
```
\Program Files
    \Adaptive Server Anywhere 6.0
        \Windows
```

Each data source itself is held in a file. The file has the same name as the data source, with an extension of .dsn.

For Info For more information about file data sources, see Creating a File Data Source.