1  SET_CONNECT
   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.
 

2  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

 SET CONNECT ---+-> <connection-name> -+->
                +-> DEFAULT -----------+
 

 

2  Arguments
 

3  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  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.
 

2  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