Simple connection examples

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.

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:

Start Interactive SQL You can do this as follows:
- From the Windows 95 or Windows NT Start menu, choose Sybase ->->Adaptive Server Anywhere ->->Interactive SQL.
- In Windows 3.x or OS/2, chose Interactive SQL from the Adaptive Server Anywhere program group.
- Type dbisql at a system command prompt.
A connection window is displayed.
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.

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:

Specify the database file in the DatabaseFile (DBF) parameter of the connection string.

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:

Start Interactive SQL with no databases running. You can use either of the following ways:
- From the Windows 95 or Windows NT Start menu, choose Sybase ->->Adaptive Server Anywhere ->->Interactive SQL.
- In Windows 3.x or OS/2, chose Interactive SQL from the Adaptive Server Anywhere program group.
- Type dbisql at a system command prompt.
When Interactive SQL starts, it is not connected to any database.
Type CONNECT in the command window, and press F9 to execute the command. The connection dialog displays.
If you have an ODBC data source for your database, you can select that data source
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
```
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.

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:

Start Interactive SQL with no databases running. You can use either of the following ways:
- Type dbisql at a system command prompt, or
- From the Windows 95 or Windows NT Start menu, choose Sybase->Adaptive Server Anywhere->Interactive SQL.
- In Windows 3.x or OS/2, chose Interactive SQL from the Adaptive Server Anywhere program group.
When Interactive SQL starts, it is not connected to any database.
Type CONNECT in the command window, and press F9 to execute the command. The connection dialog displays.
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.
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.

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.

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.

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.

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:

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
```
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 a description of the SQLCONNECT environment variable, see Environment variables.
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.