User's Guide

Part III. Using SQL Anywhere

Chapter 15. Connecting to a Database

Any client application that uses a database must establish a connection to that database before any work can be done. While the user may be prompted to enter a user ID, a password, and other parameters, the connection is established by the client application through one of SQL Anywhere's programming interfaces.

Once the connection is established, it forms the channel through which all the activity you engage in from the client application takes place. For example, the permissions you have to carry out actions on the database are determined by your user ID---and the database engine is aware of your user ID because it is part of the request to establish a connection.

Database connection parameters

When an application connects to a database, it uses a set of connection parameters, collected together in a connection string, to define the connection. For example, connection strings specify a user ID and password, as well as other optional information; all connections using ODBC must supply a Data Source name.

Connection parameters used with SQL Anywhere databases

A large class of client applications uses a very similar set of connection parameters when attempting to connect to a SQL Anywhere database. These are:

• All applications connecting to SQL Anywhere databases through the ODBC interface using the SQLDriverConnect function. This includes many ODBC-enabled application development systems, as well as applications developed using those systems.
• The SQL Anywhere database utilities ISQL, DBBACKUP, DBWATCH, DBUNLOAD, and DBVALID.
• All other embedded SQL applications connecting using the db_string_connect function, which is the recommended way to connect to a database in embedded SQL.

Methods used to specify connection parameters

The way you specify the connection parameters depends on the particular client application you are working from. Applications may use one of the following methods:

• ODBC-enabled applications use a Data Source configuration, which may be entered using the ODBC Administrator program

    For more information on adding an ODBC data source using the ODBC Administrator, see "Adding an ODBC data source".

• You may be prompted to fill in fields in a dialog box
• You may be required to enter command-line arguments
• The application may look in prespecified files to find the parameter values
• The application may use environment variable settings
• The application may have a fixed set of parameters built in permanently

Keywords used in connection strings

All these applications use a connection string consisting of a list of parameter settings, each of the form keyword=value, delimited by semicolons. The keywords must come from the following table.

Verbose keyword	Short form	Argument
Agent	Agent	string (Client or Engine)
AutoStop	AutoStop	YES/NO
ConnectionName *	CON	string
DatabaseFile	DBF	string
DatabaseName	DBN	string
DatabaseSwitches	DBS	string
DatasourceName #**	DSN	string
EngineName	ENG	string
Integrated		YES/NO
Password **	PWD	string
Start	Start	string
Userid **	UID	string

* Not supported in ODBC connections

# Supported in ODBC connections only

** Verbose form of keyword not supported in ODBC connection parameters

For more information on connection parameters, see "Connection keyword meanings", next

  For information on other ODBC-specific connection parameters, see "Adding an ODBC data source".

Connection keyword meanings

The meaning of each of the connection parameter keywords is as follows:

Keyword	Description
Agent	If you have both a standalone engine and a client available, and you wish to ensure that you correct to one of these, you can specify Agent=client or Agent=engine to ensure a connection to the appropriate agent..
AutoStop	You should supply an AutoStop keyword only if you are connecting to a database that is not currently running. If AutoStop is set toyes then the database is unloaded automatically as soon as there are no more open connections to it.
ConnectionName	An optional parameter, providing a name for the particular connection you are making. You may leave this unspecified unless you are going to establish more than one connection, and switch between them. ConnectionName is not used when connecting through ODBC
DatabaseFile	The root file of the database to which you want to connectYou should supply a DatabaseFile only if you are connecting to a database that is not currently running.For example, to start and connect to the sample database (installed in directory c:\sqlany50), use a DatabaseFile entry of c:\sqlany50\sademo.db.
DatabaseName	When a database is started, it is assigned a database name. The default database name is the name of the database file with the extension and path removed.For more information about the database name see "Some database terms".For information on this field, see "How client applications connect to a database"
DatabaseSwitches	You should supply DatabaseSwitches only if you are connecting to a database that is not currently running. When the engine starts the database specified by DatabaseFile, the engine uses the supplied DatabaseSwitches as command line options to determine startup options for the database.Only database switches can be supplied using this keyword. Engien or server switches must be supplied using the START connection parameter.For more information about database switches, see "The database engine".
DataSourceName	The DataSourceName tells an ODBC application where to look in ODBC.INI to find information about the database to which you want to connect.DataSourceName is used only by ODBC-enabled applications, and is compulsory for this class of application
EngineName	The name of a running database engine or server to which you want to connect. You need to supply a server name only if more than one database engine is running.For more information about the engine name see "Some database terms".In the Sybase Central and ISQL Connect dialog box, and in the ODBC Administrator, this is theServer Name field
Integrated	Use the integrated login facility.For more information about the integrated login facility, see "Using an integrated login".
Password	Your database password. You must always supply a password when connecting to a database
Start	You should supply a Start keyword only if you are connecting to a database engine that is not currently running. The Start parameter is a command line to start a database engine.For a detailed description of available command line switches, see "The database engine".
Userid	The user ID with which you log on to the database. You must always supply a user id when connecting to a database

For a detailed description of how client applications use the connection parameters when connecting to a database, see "How client applications connect to a database".

How client applications connect to a database

Once you have specified the connection parameters, using whatever method your client application requires, the application attempts to connect to a database. The procedure the client follows is exactly the same for each of two important sets of client application:

• Any ODBC-enabled client application. Many application development systems, such as Powersoft PowerBuilder, belong to this class of application.
• Any client application using embedded SQL and using the recommended function for connecting to a database (db_string_connect). All the SQL Anywhere database tools, including ISQL, are a part of this set.

Connecting to a database

Connecting to a database is of fundamental importance: you cannot do any work on a database without first connecting to it. For this reason, the process followed by client applications is described in detail:

1. The application tries to find the appropriate database engine or server.
   • If EngineName is specified, the application looks for a local database engine with that name, and then for a SQL Anywhere Client (DBCLIENT) with that server name. For QNX, the application looks on the network for an engine or server.
   • If EngineName is not specified, the application tries to connect to the default local engine. (If there is only one engine running, it is the default, otherwise the default choice is operating-system specific.)
2. If no matching local database engine is found, and the SQL Anywhere Client is not running, the application starts a database engine or SQL Anywhere Client using the Start parameter.
   • If no Start parameter is specified, but DatabaseFile is specified, the application attempts to start a database engine on the named file, using a default start parameter.
   • For more information about database engine parameters, see "The database engine".
3. If the application successfully finds or starts a database, the application tries to connect to the database. (If no engine has yet been found or started, the attempt to connect fails at this point)
   • If neither a DatabaseName nor a DatabaseFile is given, the application attempts to connect to the default database on the engine, using the specified Userid, Password, and ConnectionName parameters.
   • If the database named by DatabaseName is running, the application attempts to connect to the database using the specified Userid, Password, and ConnectionName parameters.
   • If DatabaseName is not specified, but DatabaseFile is, the application attempts to connect to a database whose name is the root of DatabaseFile.
   • If no running database is found, but a database specified by the root of DatabaseFile is running, the application attempts to connect to the database using the specified Userid, Password, and ConnectionName parameters.
   • If the database corresponding to DatabaseFile is not running, the application sends a request to the engine or network server to start a database using the DatabaseFile, DatabaseName, and DatabaseSwitches parameters. (The AutoStop parameter determines if the database automatically stops when the last connection to the database is disconnected.) The application then attempts to connect to the database using the specified Userid, Password, and ConnectionName parameters.
   • If no connection is made, the attempt fails at this point.

CONNECT statement from ISQL The ISQL utility has a different behavior from the default embedded SQL behavior when a CONNECT statement is issued while already connected to a database. If no database or engine is specified in the CONNECT statement, ISQL connects to the current database, rather than to the default database. This behavior is required for database reloading operations.For an example, see "CONNECT statement".