Selects the named connection from the available connections,
suspends any current connection and saves its context, and uses
the named connection in subsequent procedures in the application
after the SET CONNECT statement executes.
For information about creating and naming connections, see the
CONNECT statement.
1 – Environment
You can use the SET 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
(B)0[m[1;4mSET[m[1m [1;4mCONNECT[m[1m qqqwq> <connection-name> qwq> [m [1m mq> DEFAULT qqqqqqqqqqqj [m [1m [m
3 – Arguments
3.1 – connection-name
Specifies a name for the association between the group of
databases being attached (the environment) and the database and
request handles that reference them (the connection).
You can specify the connection name as the following:
o String literal enclosed within single quotation marks
o Parameter (in module language)
o Variable (in precompiled SQL)
3.2 – DEFAULT
Specifies one or more databases to be attached as a unit.
Use the DEFAULT keyword to specify the default connection.
The default connection is all the databases that were attached
interactively, or all those made known to the module at compile
time through DECLARE ALIAS statements.
4 – Examples
Example 1: Creating a default connection and two other
connections
The following log file from an interactive SQL connection shows
three databases attachments: personnel_northwest, personnel_
northeast, and personnel_southeast. (By not specifying an alias
for personnel_northwest, the default alias is assigned.) Several
connections are established, including EAST_COAST, which includes
both personnel_northeast and personnel_southeast.
Use the SHOW DATABASE statement to see the database settings.
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 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 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 no longer 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
Example 2: Disconnecting a connection and starting a new
connection with the same database
In this example, there are two connections: the default
connection and a current connection, CA. Both connections use
the personnel_ca database. Use the SHOW DATABASE statement to see
the database settings.
SQL> --
SQL> -- Establish a default connection by attaching to the personnel_ca
SQL> -- database.
SQL> --
SQL> ATTACH 'FILENAME personnel_ca';
SQL> SHOW CONNECTIONS;
-> RDB$DEFAULT_CONNECTION
SQL> --
SQL> -- Start a new connection called CA.
SQL> --
SQL> CONNECT TO 'FILENAME personnel_ca'
cont> AS 'CA';
SQL> SHOW CONNECTIONS;
RDB$DEFAULT_CONNECTION
-> CA
SQL> --
SQL> -- The DISCONNECT CURRENT statement releases the connection name CA,
SQL> -- although the database personnel_ca still belongs to the default
SQL> -- connection.
SQL> --
SQL> DISCONNECT CURRENT;
SQL> SHOW CONNECTIONS;
-> RDB$DEFAULT_CONNECTION
SQL> --
SQL> -- Even though the database personnel_ca is still attached, CA
SQL> -- is no longer an active connection.
SQL> --
SQL> SET CONNECT 'CA';
%SQL-F-NOSUCHCON, There is not an active connection by that name
SQL> --
SQL> -- The original ATTACH statement comprises the default connection.
SQL> -- The DISCONNECT DEFAULT statement detaches the default connection.
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