1 CONNECT 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. 2 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 --+-> --+ | +-> -------+ | +-> + | +--------------------------------------------+ +-+---------------------------------------+--+ +-> AS --> runtime-options (1) ---------+ | +--------------------------------------------+ +-+-----------------------------+------------+ +-> user-authentication ----+ | +--------------------------------------------+ ++------------------------------------------+-+ ++-+-> CATALOG --> runtime-options (2) +-+-+ | | +-> SCHEMA ---> runtime-options (3) + | | +--------------- , <--------------------+ | +---------------------------------------------+ +-+---------------------------------------+---> +-> NAMES runtime-options (4) ----------+ user-authentication = --> USER -+-> '' --+---+-----------------------------+--> +-> parameter ---+ +-> USING -+- '' +--+ +-> parameter --+ connect-string-literal = --> ' --> connect-expression --> ' --> connect-expression = ---+--> DEFAULT ------------+-> ++-> db-specification -+-+ +-------- , <---------+ db-specification = -+-+-> ALIAS ---------------------------------------+ | | +----------------- <----------------------------------+ | | +-+-------------------------------------------------------+-+-+-> | | +-> FILENAME 'attach-spec' -+-+----------------------+--+ | | | | +-> PATHNAME ---+ +-> literal-user-auth -+ | | | | | | | +-> ATTACH attach-expression ----------------------------------+ | +--------------------------------, <-------------------------------+ literal-user-auth = ---> USER '' -+------------------------+--> +-> USING '' -+ attach-expression = -+------------------++-> FILENAME -> '' -+-+ +-> ALIAS -++-> PATHNAME -> ----+ | +------------------------------------------------------+ ++------------------------+----------------------------+ +--> literal-user-auth --+ | +------------------------------------------------------+ ++-----------------------------+------------------------> +-+-+-> database-options --+-++ | +-> attach-options ----+ | +----------- <-------------+ attach-spec = --+----------------+-> -----> +-> -+ node-spec = -+-> -+-------------------+-+-> | +-> + | +------------------ :: <--------------+ access-string = -+-> " " --+-> +-> " " ---+ 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 -+ runtime-options --+---> 'string-literal' ------+-----> +---> parameter -------------+ +---> parameter-marker ------+ 2 Arguments 3 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 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 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 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 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 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 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 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 PATHNAME 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 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 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 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 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 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 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. 2 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