ASA User's Guide

Home

ASA User's Guide

Part 1 Working with Databases

Chapter 2: Connecting to a Database

Working with ODBC data sources

Microsoft Corporation defines the Open Database Connectivity (ODBC) interface, which 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 and on Windows operating systems. For information, see Using ODBC data sources on UNIX .

Where data sources are held

You connect to an ODBC database using an ODBC data source. You need an ODBC data source on the client computer for each database you want to connect to.

The ODBC data source contains a set of connection parameters. You can store sets of Adaptive Server Anywhere connection parameters as an ODBC data source, in either 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
Use the DSN connection parameter to reference a data source in the registry:
```
DSN=my data source
```
File data source
Use the FileDSN connection parameter to reference a data source held in a file:
```
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:

To start the ODBC Administrator, click on the 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 appears.

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.

To edit a data source, select one from the list in the ODBC administrator window and click Configure.

Configuring ODBC data sources

This section describes the meaning of each of the options on the ODBC configuration dialog box, organized by tab.

ODBC tab

Data source name

The Data Source Name is used to identify the ODBC data source. You can use any descriptive name for the data source (spaces are allowed) but it is recommended that you keep the name short, as you may need to enter it in connection strings.

For more information, see DataSourceName connection parameter .

Description

You may enter an optional longer description of the Data Source.

Translator

Choose Adaptive Server Anywhere 6.0 Translator if your database uses an OEM code page. If your database uses an ANSI code page, which is the default, leave this unchecked.

Isolation level

Select the desired isolation level for this data source:

0
Dirty reads, non-repeatable reads and phantom rows may occur.
1
Non-repeatable rows and phantom rows, may occur. Dirty reads are prevented.
2
Phantom rows may occur. Dirty reads and non-repeatable rows are prevented.
3
Dirty reads, non-repeatable reads and phantom rows are prevented.

For more information, see Choosing isolation levels .

Microsoft applications (keys in SQL Statistics)

Check this box if you wish foreign keys to be returned by SQL statistics. The ODBC specifications states that primary and foreign keys should not be returned by SQL statistics, however, some Microsoft applications (such as Visual Basic and Access) assume that primary and foreign keys are returned by SQL statistics.

Delphi applications

Check this box to improve performance for Borland Delphi applications. When this option is checked, one bookmark value is assigned to each row, instead of the two that are otherwise assigned (one for fetching forwards and a different one for fetching backwards).

Delphi cannot handle multiple bookmark values for a row. If the option is unchecked , scrollable cursor performance can suffer since scrolling must always take place from the beginning of the cursor to the row requested in order to get the correct bookmark value.

Prevent Driver Not Capable errors

The Adaptive Server 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. Check this box to disable this error code, allowing such applications to work.

Delay AutoCommit until statement close

Check this box if you wish the Adaptive Server Anywhere ODBC driver to delay the commit operation until a statement has been closed.

Describe cursor behavior

Select how often you wish a cursor to be re-described when a procedure is executed or resumed.

Test Connection

Tests if the information provided will result in a proper connection. In order for the test to work a user ID and password must have been specified.

Use integrated login

Connects using an integrated login. The User ID and password do not need to be specified. To use this type of login users must have been granted integrated login permission. The database being connected to must also be set up to accept integrated logins. Only users with DBA access may administer integrated login permissions.

For more information, see Using integrated logins .

User ID

Provide a place for you to enter the User ID for the connection.

For more information, see Userid connection parameter .

Password

Provides a place for you to enter the password for the connection.

For more information, see Password connection parameter .

Encrypt password

Check this box is you wish the password to be stored in encrypted for in the profile.

For more information, see EncryptedPassword connection parameter .

Database tab

Server name

Provides a place for you to enter the name of the Adaptive Server Anywhere personal or network server.

For more information, see EngineName connection parameter .

Start line

Enter the server that should be started. Only provide a Start Line parameter if a database server is being connected to that is not currently running. For example:

C:\Program Files\Sybase\Adaptive Server Anywhere\win32\dbeng6.exe -c 8m

For more information, see StartLine connection parameter .

Database name

Provides a place for you to enter the name of the Adaptive Server Anywhere database that you wish to connect to.

For more information, see DatabaseName connection parameter .

Database file

Provides a place for you to enter the full path and name of the Adaptive Server Anywhere database file on the server PC. You may also click Browse to locate the file. For example:

C:\Program Files\Sybase\Adaptive Server Anywhere\asademo.db

For more information, see DatabaseFile connection parameter .

Automatically shut down database after last disconnect

Selecting this will cause the automatic shutdown of the server after the last user has disconnected.

For more information, see AutoStop connection parameter .

Network tab

Select the network protocol and specify any protocol specific options where necessary

The TCP/IP, IPX, and NetBIOS check boxes specifies what protocol or protocols the ODBC DSN will use to access a network database server. In the adjacent boxes, you may enter communication parameters that establish and tune connections from your client application to a database.

For more information see CommLinks connection parameter , and Network communications parameters .

Encrypt all network packets

Enables encryption of packets transmitted from the client machine over the network. By default, network encryption packets is set to OFF.

For more information, see Encryption connection parameter .

Liveness timeout

A liveness packet is sent across a client/server to confirm that a connection is intact. If the client runs for the liveness timeout period without detecting a liveness packet, the communication will be severed. This parameter works only with network server and TCP/IP or IPX communications protocols. The default is 120 seconds.

For more information, see LivenessTimeout connection parameter .

Buffer size

Set the maximum size of communication packets, in bytes.

For more information, see CommBufferSize connection parameter .

Buffer space

Indicates the amount of space to allocate on startup for network buffers, in kilobytes.

For more information, see CommBufferSpace connection parameter .

Advanced tab

Connection name

The name of the connection that is being created.

Allow multiple record fetching

Enables multiple records to be retrieved at one time instead of individually. By default, multiple record fetching is allowed.

Display debugging information in a log file

The name of the file in which the debugging information is to be saved.

Additional connection parameters

Enter any additional switches here. Parameters set throughout the remainder of this dialog take precedence over parameters typed here. See the Adaptive Server Anywhere documentation for information about switches.

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:

To start the ODBC Administrator, click on the File DSN tab, and 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 looks like this:

[My Data Source]
ENG=myserver
CommLinks=tcpip(Host=hostname)
UID=dba
PWD=sql

You can enter any connection parameter in the .odbc.ini file. For a complete list, see Connection parameters .

Network communications parameters are added as part of the CommLinks parameter. For a complete list, see Network communications parameters .

File location

The database server looks for the .odbc.ini file in the following locations:

ODBCINI environment variable
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) DSN and FileDSN are synonyms.

Data source location

Windows CE searches for the data source files 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 more information about file data sources, see Creating a File Data Source .

Connecting from desktop applications to a Windows CE database

You can connect from applications running on a desktop PC, such as Sybase Central or Interactive SQL, to a database server running on a Windows CE device.

To connect from a desktop application to a database server running on Windows CE:

Determine the IP address of the server. Start the server on the Windows CE device with the

-z

option (output extra debug information).

For example:

dbsrv6 -z -x tcpip -n TestServer asademo.db

With the

-z

switch, the server writes out its IP address during startup. The address may change if you disconnect your HPC from the network and then re-connect it.

See the settings in Control Panel-->Network-->Services-->Remote Access Service-->Properties-->Network-->TCP/IP Configuration if you would like to change between static and dynamic IP assignment for the HPC.

Create an ODBC profile on your desktop machine. Go to Control Panel-->ODBC-->Add a new User Datasource name (DSN). Choose the Adaptive Server Anywhere driver.

On the Login tab, enter a user ID and password.
On the Database tab, enter the server name in the Server Name field.
Under the Network tab, check TCP/IP, and enter the following in the adjacent field:
```
dobroadcast=no;host=XXX.XXX.XXX.XXX
```
where XXX.XXX.XXX.XXX is the server IP address.
Under the ODBC tab, click Test Connection to confirm that your ODBC data source is properly configured.

Exit the ODBC administrator.

Ensure the database server is running on your Windows CE machine.

On your desktop machine, start an application such as Interactive SQL and select the ODBC data source you have created. The application connects to the Windows CE database.


	Expand Search