CONNECT

Go Up to Statement and Function Reference (Language Reference Guide)

Attaches to one or more databases. Available in gpre. A subset of CONNECT options is available in isql.

isql:

CONNECT 'filespec' [USER 'username'][PASSWORD 'password']
[CACHE int] [ROLE 'rolename']

SQL:

CONNECT [TO] {ALL | DEFAULT} <config_opts>
| <db_specs> <config_opts> [, <db_specs> <config_opts>...];

<db_specs> = dbhandle
| {'filespec' | :variable} AS dbhandle

<config_opts> = [USER {'username' | :variable}]
[PASSWORD {'password' | :variable}]
[ROLE {'rolename' | :variable}]
[CACHE int [BUFFERS]]

Argument	Description
{`ALL \| DEFAULT`}	Connects to all databases specified with `SET DATABASE`; options specified with `CONNECT TO ALL` affect all databases.
<'filespec>'	Database file name; can include path specification and node. The filespec must be in quotes if it includes spaces.
<dbhandle>	Database handle declared in a previous `SET DATABASE` statement; available in embedded SQL but not in `isql`.
<:variable>	Host-language variable specifying a database, user name, or password; available in embedded SQL but not in `isql`.
`AS`<dbhandle>	Attaches to a database and assigns a previously-declared handle to it; available in embedded SQL but not in `isql`.
`USER` {'<username>' \| :<variable>}	String or host-language variable that specifies a user name for use when attaching to the database. The server checks the user name against the security database. User names are case insensitive on the server.
`PASSWORD`{‘<password>’ \| :<variable>}	String or host-language variable, up to 8 characters in size, that specifies password for use when attaching to the database. The server checks the user name and password against the security database. Case sensitivity is retained for the comparison.
`ROLE`{‘<rolename>’ \| :<variable>}	String or host-language variable, up to 67 characters in size, which specifies the role that the user adopts on connection to the database. The user must have previously been granted membership in the role to gain the privileges of that role. Regardless of role memberships granted, the user has the privileges of a role at connect time only if a `ROLE` clause is specified in the connection. The user can adopt at most one role per connection, and cannot switch roles except by reconnecting.
`CACHE` <int> [`BUFFERS`]	Sets the number of cache buffers for a database, which determines the number of database pages a program can use at the same time. Values for <int>: Default: 256 Maximum value: system-dependent Do not use the <filespec> form of database name with cache assignments.

Description: The CONNECT statement:

Initializes database data structures.
Determines if the database is on the originating node (a local database) or on another node (a remote database). An error message occurs if InterBase cannot locate the database.
Optionally specifies one or more of a user name, password, or role for use when attaching to the database. PC clients must always send a valid user name and password. InterBase recognizes only the first 8 characters of a password.

If an InterBase user has ISC_USER and ISC_PASSWORD environment variables set and the user defined by those variables is not in the InterBase security database (admin.ib by default), the user receives the following error when attempting to view users from the local server manager connection: “undefined user name and password.” This applies only to the local connection; the automatic connection made through Server Manager bypasses user security.

Attaches to the database and verifies the header page. The database file must contain a valid database, and the on-disk structure (ODS) version number of the database must be the one recognized by the installed version of InterBase on the server, or InterBase returns an error.
Optionally establishes a database handle declared in a SET DATABASE statement.
Specifies a cache buffer for the process attaching to a database.

In SQL programs before a database can be opened with CONNECT, it must be declared with the SET DATABASE statement. isql does not use SET DATABASE.

In SQL programs while the same CONNECT statement can open more than one database, use separate statements to keep code easy to read.

When CONNECT attaches to a database, it uses the default character set (NONE), or one specified in a previous SET NAMES statement.

In SQL programs, the CACHE option changes the database cache size count (the total number of available buffers) from the default of 75. This option can be used to:

Set a new default size for all databases listed in the CONNECT statement that do not already have a specific cache size.
Specify a cache for a program that uses a single database.
Change the cache for one database without changing the default for all databases used by the program.

The size of the cache persists as long as the attachment is active. If a database is already attached through a multi-client server, an increase in cache size due to a new attachment persists until all the attachments end. A decrease in cache size does not affect databases that are already attached through a server.

A subset of CONNECT features is available in isql: database file name, USER, and PASSWORD. isql can only be connected to one database at a time. Each time CONNECT is used to attach to a database, previous attachments are disconnected.

Examples: The following statement opens a database for use in isql. It uses all the CONNECT options available to isql:

CONNECT 'employee.ib' USER 'ACCT_REC' PASSWORD 'peanuts';

The next statements, from an embedded application, attach to a database file stored in the host-language variable and assign a previously-declared database handle to it:

EXEC SQL
SET DATABASE DB1 = 'employee.ib';
EXEC SQL
CONNECT :db_file AS DB1;

The following embedded SQL statement attaches to employee.ib and allocates 150 cache buffers:

EXEC SQL
CONNECT 'accounts.ib' CACHE 150;

The next embedded SQL statement connects the user to all databases specified with previous SET DATABASE statements:

EXEC SQL
CONNECT ALL USER 'ACCT_REC' PASSWORD 'peanuts'
CACHE 50;

The following embedded SQL statement attaches to the database, employee.ib, with 80 buffers and database employee2.ib with the default of 75 buffers:

EXEC SQL
CONNECT 'employee.ib' CACHE 80, 'employee2.ib';

The next embedded SQL statement attaches to all databases and allocates 50 buffers:

EXEC SQL
CONNECT ALL CACHE 50;

The following embedded SQL statement connects to EMP1 and v, setting the number of buffers for each to 80:

EXEC SQL
CONNECT EMP1 CACHE 80, EMP2 CACHE 80;

The next embedded SQL statement connects to two databases identified by variable names, setting different user names and passwords for each:

EXEC SQL
CONNECT
:orderdb AS DB1 USER 'ACCT_REC' PASSWORD 'peanuts',
:salesdb AS DB2 USER 'ACCT_PAY' PASSWORD 'payout';

Advance To:

CREATE DATABASE

CONNECT

See Also

Advance To: