Collection Contents Index Introduction to connections Working with ODBC data sources pdf/chap2.pdf

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

Simple connection examples


Although the connection model for Adaptive Server Anywhere is configurable, and can become complex, in many cases connecting to a database is very simple.

Who should read this section? 

This section describes some simple cases of applications connecting to an Adaptive Server Anywhere database. When you are getting started, this section may be all you need.

For Info     For more detailed information on available connection parameters and their use, see Connection parameters.

Top of page  Connecting to the sample database from Interactive SQL

Many examples and exercises throughout the documentation start by connecting to the sample database from Interactive SQL. Here is how to carry out this step:

  To connect to the sample database from Interactive SQL:
  1. Start Interactive SQL     You can do this as follows:

    A connection window is displayed.

  2. Connect     From the ODBC data source list, select ASA 6.0 Sample. You can leave all the other fields empty. Click OK to start the database server and connect to the database.

Top of page  Connecting to an embedded database

An embedded database is designed for use by a single application, runs on the same machine as the application, and is largely hidden from the application user.

When an application uses an embedded database, the personal server is generally not running when the application connects. In this case, you can start the database using the connection string. You can do this in one of the following ways:

Using the DBF parameter 

The following connection parameters show how the sample database could be loaded as an embedded database:

dbf=path\asademo.db
uid=dba
pwd=sql

where path is the name of your Adaptive Server Anywhere installation directory.

The DBF parameter specifies the database file to be used. If no server is running, one is started. If one or more servers are running, the database is loaded onto the default server.

When there are no more connections to the database (generally when the application that started it disconnects) the database is unloaded. If the server was started by the connection, it is stopped once the database is unloaded.

Using the Start parameter 

The following connection parameters show how you can customize the startup of the sample database as an embedded database. This is useful if you wish to use command-line options, such as the cache size:

Start=dbeng6 -c 8M 
dbf=path\asademo.db
uid=dba
pwd=sql

Extra cache needed for Java    
If you are using Java in an embedded database, you should use the start line to provide more than the default cache size. For development purposes, a cache size of 8 Mb is sufficient.

Example: connecting from Interactive SQL 

In this example, the sample database is used as an embedded database within Interactive SQL. This example assumes that you have no SQLCONNECT environment variable set.

  To connect to an embedded database from Interactive SQL:
  1. Start Interactive SQL with no databases running. You can use either of the following ways:

    When Interactive SQL starts, it is not connected to any database.

  2. Type CONNECT in the command window, and press F9 to execute the command. The connection dialog displays.

  3. If you have an ODBC data source for your database, you can select that data source

  4. Enter DBA as the user ID and SQL as the password. Then click the Database tab. Enter the full path of the sample database in the Database File field. For example, if your installation directory is c:\sybase\asa6 you should enter the following:

    c:\sybase\asa6\asademo.db
  5. Leave all other fields blank, and click OK. Adaptive Server Anywhere starts up and loads the sample database, and Interactive SQL connects to the database.

Top of page  Connecting using a data source

You can save sets of connection parameters in a data source. Data sources can be used by ODBC and Embedded SQL applications. You can create data sources from the ODBC Administrator.

Here, we show how to connect to the sample database from Interactive SQL using a data source

  To connect using a data source:
  1. Start Interactive SQL with no databases running. You can use either of the following ways:

    When Interactive SQL starts, it is not connected to any database.

  2. Type CONNECT in the command window, and press F9 to execute the command. The connection dialog displays.

  3. Enter DBA as the user ID and SQL as the password. Select ASA 6.0 sample from the drop-down list of ODBC data sources.

  4. Leave all other fields blank, and click OK. Adaptive Server Anywhere starts up and loads the sample database, and Interactive SQL connects to the database.

The sample data source 

The ASA 6.0 Sample data source holds a set of connection parameters, including the database file and a Start parameter to start the database.

Top of page  Connecting to a server on a network

To connect to a database running on a network server somewhere on a local or wide area network, the client software must be able to locate the database server. Adaptive Server Anywhere provides a network library (a DLL or shared library) that handles this task.

Any network connection is made over a network protocol. Several protocols are supported, including TCP/IP, IPX, and NetBIOS.

For Info     For a full description of client/server communications over a network, see Client/Server Communications.

Specifying the server 

Adaptive Server Anywhere server names must be unique on a local domain for a given network protocol. The following connection parameters provide a simple example for connecting to a server running elsewhere on a network:

eng=svr_name
dbn=db_name
uid=user_id
pwd=password
CommLinks=all

The client library first looks for a personal server of the given name, and then looks on the network for a server of the specified name.

For Info     The above example finds any server started using default port number. However, you can start servers using other port numbers and in this case you need to provide more information in the CommLinks parameter. For information, see CommLinks connection parameter.

Specifying the protocol 

If several protocols are available, you can instruct the network library which ones to use to improve performance. The following parameters use only the TCP/IP protocol:

eng=svr_name
dbn=db_name
uid=user_id
pwd=password
CommLinks=tcpip

The network library searches for a server by broadcasting over the network. This can be a time-consuming process. Once a server is located, its name and network address are stored by the client library in a file. This entry is reused for subsequent connection attempts to that server using the specified protocol. This can make subsequent connections many times faster than a connection achieved by broadcast.

For Info     Many other connection parameters are available to assist Adaptive Server Anywhere in locating a server efficiently over a network. For more information see Network communications parameters.

Top of page  Using default connection parameters

Many connection parameters can be left unspecified, using default behavior to make a connection. You should be cautious about relying on default behavior in production environments, especially if you distribute your application to customers who may install other Adaptive Server Anywhere applications on their machine.

Default database server and database 

If a single personal server is running, and it has loaded a single database, you can connect using entirely default parameters:

uid=user_id
pwd=password

Default database server 

If more than one database is loaded on a single personal server, you need to specify the database you wish to connect to, but you can leave the server as a default:

dbn=db_name
uid=user_id
pwd=password

Default database 

If more than one server is running, you need to specify which one you wish to connect to. If only one database is loaded on that server, you do not need to specify the database name. The following connection string connects to a named server, using the default database:

eng=server_name
uid=user_id
pwd=password

No defaults 

The following connection string connects to a named server, using a named database:

eng=server_name
dbn=db_name
uid=user_id
pwd=password

For Info     For more information about default behavior, see How connections are made.

Top of page  Connecting from Adaptive Server Anywhere utilities

All Adaptive Server Anywhere database utilities that communicate with the server (rather than acting directly on database files) do so using Embedded SQL. They follow the procedure outlined in How connections are made when connecting to a database.

How database tools obtain connection parameter values 

Many of the administration utilities obtain the values of the connection parameters in the following way:

  1. If there are values specified on the command line, those values are used for the connection parameters. For example, the following command starts a backup of the default database on the default server using the user ID DBA and the password SQL:

    dbbackup -c "uid=dba;pwd=sql" c:\backup
  2. If any command line values are missing, the application looks at the setting of the SQLCONNECT environment variable. This variable is not set automatically by Adaptive Server Anywhere.

    For Info     For a description of the SQLCONNECT environment variable, see Environment variables.

  3. If parameters are not set in the command line, or the SQLCONNECT environment variable, then the application prompts for a user ID and password to connect to the default database on the default server.

For a description of command line switches for each database tool, see chapter Database Administration Utilities.

Top of page  

Collection Contents Index Introduction to connections Working with ODBC data sources pdf/chap2.pdf