Creates a database environment and a connection, and specifies a
connection name for that association.
A connection specifies an association between the set of cursors,
intermediate result tables, and procedures in all modules of an
application and the database environment currently attached.
A database environment is one or more databases that can be
attached or detached as a unit. The connection name designates a
particular connection and database environment. When you execute
a procedure, it executes in the context of a connection.
When you issue a CONNECT statement, SQL creates a new connection
from all the procedures in your application and creates a
new environment from all the databases named in the CONNECT
statement. The new environment can include databases already
attached in the default environment.
There are two ways to attach a database to the default
environment:
o Use an ATTACH statement to specify a database environment at
run time. All the databases you specify with subsequent ATTACH
statements become part of the default environment.
o Use a DECLARE ALIAS statement to specify a database
environment at compile time in precompiled SQL and SQL module
language. All the databases that you specify using DECLARE
ALIAS statements also become part of the default environment.
A CONNECT statement creates a new connection with a new set
of attachments, and does an implicit SET CONNECT to that new
connection. Although a CONNECT statement does not create a
transaction, each connection has its own implicit transaction
context. You can issue two different CONNECT statements that
attach to the same database, but each attach is unique.
Once you have specified a connection name in a CONNECT statement,
you can refer to that connection name in subsequent SET CONNECT
statements. You can use a SET CONNECT statement to specify a new
connection for an application to run against without having to
detach and recompile queries. See the SET_CONNECT statement for
more information.
The DISCONNECT statement detaches from databases, ends the
transactions in the connections that you specify, and rolls back
all the changes you made since those transactions began.
1 – Environment
You can use the CONNECT statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
CONNECT --+
+---------+
+-+---------------------------------------+--+
+-> TO --+-> <connect-string-literal> --+ |
+-> <connect-parameter> -------+ |
+-> <connect-parameter-marker> + |
+--------------------------------------------+
+-+---------------------------------------+--+
+-> AS --> runtime-options (1) ---------+ |
+--------------------------------------------+
+-+-----------------------------+------------+
+-> user-authentication ----+ |
+--------------------------------------------+
++------------------------------------------+-+
++-+-> CATALOG --> runtime-options (2) +-+-+ |
| +-> SCHEMA ---> runtime-options (3) + | |
+--------------- , <--------------------+ |
+---------------------------------------------+
+-+---------------------------------------+--->
+-> NAMES runtime-options (4) ----------+
user-authentication =
--> USER -+-> '<username>' --+---+-----------------------------+-->
+-> parameter ---+ +-> USING -+- '<password>' +--+
+-> parameter --+
connect-string-literal =
--> ' --> connect-expression --> ' -->
connect-expression =
---+--> DEFAULT ------------+->
++-> db-specification -+-+
+-------- , <---------+
db-specification =
-+-+-> ALIAS <alias> ---------------------------------------+
| | +----------------- <----------------------------------+
| | +-+-------------------------------------------------------+-+-+->
| | +-> FILENAME 'attach-spec' -+-+----------------------+--+ | |
| | +-> PATHNAME <path-name> ---+ +-> literal-user-auth -+ | |
| | | |
| +-> ATTACH attach-expression ----------------------------------+ |
+--------------------------------, <-------------------------------+
literal-user-auth =
---> USER '<username>' -+------------------------+-->
+-> USING '<password>' -+
attach-expression =
-+------------------++-> FILENAME -> '<attach-spec>' -+-+
+-> ALIAS <alias> -++-> PATHNAME -> <path-name> ----+ |
+------------------------------------------------------+
++------------------------+----------------------------+
+--> literal-user-auth --+ |
+------------------------------------------------------+
++-----------------------------+------------------------>
+-+-+-> database-options --+-++
| +-> attach-options ----+ |
+----------- <-------------+
attach-spec =
--+----------------+-> <file-spec> ----->
+-> <node-spec> -+
node-spec =
-+-> <nodename> -+-------------------+-+->
| +-> <access-string> + |
+------------------ :: <--------------+
access-string =
-+-> " <user-name> <password> " --+->
+-> " <VMS-proxy-user-name> " ---+
database-options =
--+--> ELN ---------------------+-->
+--> NSDS --------------------+
+--> rdb-options -------------+
+--> VIDA --------------------+
+--> VIDA V1 -----------------+
+--> VIDA V2 -----------------+
+--> VIDA V2N ----------------+
+--> NOVIDA ------------------+
+--> DBIV1 -------------------+
+--> DBIV31 ------------------+
+--> DBIV70 ------------------+
rdb-options =
-+-> RDBVMS --+-->
+-> RDB030 --+
+-> RDB031 --+
+-> RDB040 --+
+-> RDB041 --+
+-> RDB042 --+
+-> RDB050 --+
+-> RDB051 --+
+-> RDB060 --+
+-> RDB061 --+
+-> RDB070 --+
+-> RDB071 --+
attach-options =
-+-> DBKEY -+-> SCOPE IS -+-> ATTACH -------+----+->
+-> ROWID -+ +-> TRANSACTION --+ |
+-> MULTISCHEMA IS -+-> ON --+------------------+
| +-> OFF -+ |
+-> PRESTARTED TRANSACTIONS ARE -+-> ON --+-----+
| +-> OFF -+ |
+-+-------+-> RESTRICTED ACCESS ----------------+
| +-> NO -+ |
+-> DISPLAY CHARACTER SET <character-set-name> -+
runtime-options
--+---> 'string-literal' ------+----->
+---> parameter -------------+
+---> parameter-marker ------+
3 – Arguments
3.1 – ALIAS alias
Specifies a name for a particular attach to a database.
Specifying an alias in the connect expression lets your program
or interactive SQL statements refer to more than one database.
You do not have to specify an alias in the CONNECT statement if
you are referring only to the default database.
If you specify an alias, but do not specify a FILENAME or
PATHNAME, SQL uses the path name or file name in the DECLARE
ALIAS statement for that database by default. The alias must be
part of the default environment.
3.2 – AS
Specifies an identifier for the association between the group of
databases being attached (the environment) and the database and
request handles that reference them (the connection).
The connection name must be unique within your application. Use
a literal string enclosed within single quotation marks, for
example:
CONNECT TO 'ALIAS CORP FILENAME corporate_data' AS 'JULY_CORP_DATA'
If you do not specify a connection name, SQL generates a unique
connection name. For example:
SQL> CONNECT TO
cont> 'ATTACH FILENAME mf_personnel';
SQL> SHOW CONNECTIONS
RDB$DEFAULT_CONNECTION
-> SQL$CONN_00000000
3.3 – ATTACH attach expression
Specifies an alias that is not part of the default environment.
See the ATTACH statement for details about the FILENAME 'attach-
spec', PATHNAME path-name, database-options, and attach-options.
3.4 – CATALOG
Specifies the default catalog for dynamic statements in the
connection.
You can supply a parameter marker from dynamic SQL, a host
language variable from a precompiled SQL program, a parameter
from an SQL module language module, or a string literal. The
argument that you supply must be a character string that contains
a connect expression that is interpreted at run time.
3.5 – db-specification
Specifies one or more valid aliases. An alias, which identifies
a particular database, is valid only if that database is either
declared in any of the modules in the current application or
attached with the ATTACH statement. You can issue an ATTACH
statement as part of the db-specification.
3.6 – FILENAME
A quoted string containing full or partial information needed to
access a database.
When you use the FILENAME argument, any changes you make to
database definitions are entered only to the database system
file, not to the repository. If you specify FILENAME, your
application attaches to the database with that file name at run
time.
For information regarding node-spec and file-spec, see Oracle Rdb
Attach Specifications.
3.7 – literal-user-auth
Specifies the user name and password for the specified alias
in the connection. This clause enables access to databases,
particularly remote databases.
This literal lets you explicitly provide user name and password
information for each alias in the CONNECT statement. For more
information about when to use this clause, see the statement
ATTACH.
3.8 – NAMES
Specifies a character set name that is used as the default,
identifier, and literal character sets for the session of the
current connection. The value of runtime-options must be one of
the character sets listed in the Supported_Characters_Sets HELP
topic .
You can supply a parameter marker from dynamic SQL, a host
language variable from a precompiled SQL program, a parameter
from an SQL module language module, or a string literal. The
argument that you supply must be a character string that contains
a connect expression that is interpreted at run time.
3.9 – PATHNAME
<OpenVMS>
A full or relative repository path name that specifies the source
of the schema definitions. When you use the PATHNAME argument,
any changes you make to schema definitions are entered in the
repository and the database system file. Oracle Rdb recommends
using the PATHNAME argument if you have the repository on your
system and you plan to use any data definition statements.
The path name that you specify overrides the path name associated
with the alias at run time.
If you specify PATHNAME at run time, your application attaches to
the database file name extracted from the repository.
3.10 – runtime-options
'literal' | parameter | parameter-marker
Specifies a character set name that is used as the default,
identifier, and literal character sets for the session of the
current connection. The value of runtime-options must be one of
the character sets listed in
3.11 – SCHEMA
Specifies the schema for dynamic statements in the connection.
You can supply a parameter marker from dynamic SQL, a host
language variable from a precompiled SQL program, a parameter
from an SQL module language module, or a string literal. The
argument that you supply must be a character string that contains
a connect expression that is interpreted at run time.
3.12 – TO
Syntax options:
connect-string-literal
connect-parameter
connect-parameter-marker
Specifies the database environment. You can supply a parameter
marker from dynamic SQL, a host language variable from a precompiled
SQL program, a parameter from an SQL module language module, or a
string literal. The argument that you supply must be a character
string that contains a connect expression that is interpreted at run
time.
3.13 – USER clause
Syntax options:
USER 'username' | USER parameter
A character string literal that specifies the operating system
user name that the database system uses for privilege checking.
3.14 – USING clause
Syntax options:
USING 'password' | USING parameter
A character string literal that specifies the user's password for
the user name specified in the USER clause.
3.15 – user-authentication
Specifies the user name and password to enable access to
databases, particularly remote databases.
This clause lets you explicitly provide user name and password
information in the CONNECT statement. If you do not specify user
name and password information in the ALIAS clause or the ATTACH
clause, SQL uses the user name and password specified in this
clause as the default for each alias specified.
For more information about when to use this clause, see the
ATTACH statement.
4 – Examples
Example 1: Creating a default connection and one other connection
The following example shows how a user attaches to one database
with two different connections: the default connection and the
named connection TEST.
SQL> attach 'alias MIA1 filename MIA_CHAR_SET';
SQL> connect to 'alias MIA1 filename MIA_CHAR_SET' as 'TEST';
SQL> show connections;
RDB$DEFAULT_CONNECTION
-> TEST
SQL> show connections TEST;
Connection: TEST
Default alias is RDB$DBHANDLE
Default catalog name is RDB$CATALOG
Default schema name is SMITH
Dialect: SQLV40
Default character unit: OCTETS
Keyword Rules: SQLV40
View Rules: SQLV40
Default DATE type: DATE VMS
Quoting Rules: SQLV40
Optimization Level: DEFAULT
Hold Cursors default: WITH HOLD PRESERVE NONE
Quiet commit mode: OFF
Compound transactions mode: EXTERNAL
Default character set is DEC_MCS
National character set is DEC_MCS
Identifier character set is DEC_MCS
Literal character set is DEC_MCS
Display character set is UNSPECIFIED
Alias MIA1:
Identifier character set is DEC_KANJI
Default character set is DEC_KANJI
National character set is KANJI
Example 2: Creating a default connection and two other
connections
The following example attaches to three databases: personnel_
northwest, personnel_northeast, and personnel_southeast. (By
not specifying an alias for personnel_northwest, it is assigned
the default alias.) Several connections are established,
including EAST_COAST, which includes both personnel_northeast
and personnel_southeast.
Use the SHOW DATABASE statement to see the changes to the
database.
SQL> --
SQL> -- Attach to the personnel_northwest and personnel_northeast databases.
SQL> -- Personnel_northwest has the default alias, so personnel_northeast
SQL> -- requires an alias.
SQL> -- All of the attached databases comprise the default connection.
SQL> --
SQL> ATTACH 'FILENAME personnel_northwest';
SQL> ATTACH 'ALIAS NORTHEAST FILENAME personnel_northeast';
SQL> --
SQL> -- Add the personnel_southeast database.
SQL> --
SQL> ATTACH 'ALIAS SOUTHEAST FILENAME personnel_southeast';
SQL> --
SQL> -- Connect to personnel_southeast. CONNECT does an
SQL> -- implicit SET CONNECT to the newly created connection.
SQL> --
SQL> CONNECT TO 'ALIAS SOUTHEAST FILENAME personnel_southeast'
cont> AS 'SOUTHEAST_CONNECTION';
SQL> --
SQL> -- Connect to both personnel_southeast and personnel_northeast as
SQL> -- EAST_COAST connection. SQL replaces the current connection to
SQL> -- the personnel_southeast database with the EAST_COAST connection
SQL> -- when you issue the CONNECT statement. You now have two different
SQL> -- connections that include personnel_southeast.
SQL> --
SQL> CONNECT TO 'ALIAS NORTHEAST FILENAME personnel_northeast,
cont> ALIAS SOUTHEAST FILENAME personnel_southeast'
cont> AS 'EAST_COAST';
SQL> --
SQL> -- The DEFAULT connection still includes all of the attached databases.
SQL> --
SQL> SET CONNECT DEFAULT;
SQL> --
SQL> -- DISCONNECT releases the connection name EAST_COAST, but
SQL> -- does not detach from the EAST_COAST databases because
SQL> -- they are also part of the default connection.
SQL> --
SQL> DISCONNECT 'EAST_COAST';
SQL> --
SQL> SET CONNECT 'EAST_COAST';
%SQL-F-NOSUCHCON, There is not an active connection by that name
SQL> --
SQL> -- If you disconnect from the default connection, and have no other
SQL> -- current connections, you are longer be attached to any databases.
SQL> --
SQL> DISCONNECT DEFAULT;
SQL> SHOW DATABASES;
%SQL-F-ERRATTDEF, Could not use database file specified by SQL$DATABASE
-RDB-E-BAD_DB_FORMAT, SQL$DATABASE does not reference a database known to Rdb
-RMS-E-FNF, file not found