The CONNECT Statement

---

Logging on is requested from an application program with the CONNECT statement, see the Mimer SQL Reference Manual, CONNECT, for the syntax description.

The CONNECT statement establishes a connection between a user and a database. The user may be a USER ident (in which case the password must be provided) or an OS_USER (if such an ident has been created in the Mimer SQL database). To connect as the OS_USER ident with the same name as the current operating system user, provide an empty ident name string.

Example

 EXEC SQL CONNECT TO 'db' AS 'con1' USER 'ident' USING 'pswd';

Local and Remote Databases

A connection may be established to any local or remote database, which has been made accessible from the current machine, see the Mimer SQL System Management Handbook, Creating a Mimer SQL Database, for details, by specifying the database by name or by using the keyword DEFAULT.

Default or Named Database

If the keyword DEFAULT is used, the OS_USER ident is used for the connection attempt. If the database name is given as an empty string, the DEFAULT database is used.

The database may be given an explicit connection name for use in DISCONNECT and SET CONNECTION statements. If no explicit name is given, the database name is used as the connection name.

Implicit Connection

Normally, CONNECT should be the first executable SQL statement in an application program using ESQL. However, if an executable statement is issued before any connection has been established in the current application, an implicit connection will be attempted.

An implicit connection is made to the DEFAULT database using the current operating system user.

In order for the connect attempt to be successful, the current operating system user must be defined as an OS_USER ident in Mimer SQL and the DEFAULT database must be defined as a local database on the machine on which the current operating system user is defined.

If an implicit connection has previously been established in the application and there is no current connection, issuing an executable statement will result in a new attempt to make the same implicit connection. However, if an explicit connection has previously been established in the application and there is no current connection, issuing an executable statement will cause an error.

Changing Connection

A connection established by a successful CONNECT statement is automatically active.

An application program may make multiple connections to the same or different databases using the same or different idents, provided that each connection is identified by a unique connection name.

Only the most recent connection is active. Other connections are dormant, and may be made active by the SET CONNECTION statement. Resources such as cursors used by a connection are saved when the connection becomes dormant, and are restored by the appropriate SET CONNECTION statement.

The statement sequence below connects to a user-specific database as a specified ident name and to the DEFAULT database as OS_USER. The user-specific connection is initially active. Then the DEFAULT connection is activated. Finally the user-specific connection is activated again using SET CONNECTION.

 EXEC SQL CONNECT TO 'db' AS 'con1' USER 'ident' USING 'pswd';
 ...
 EXEC SQL CONNECT TO DEFAULT;
 ...
 -- Set activate connection to CON1
 EXEC SQL SET CONNECTION 'con1';
 

Note: If different connections are made with different idents, the apparent access rights of the application program may change when the current connection is changed.

Disconnecting

The DISCONNECT statement breaks the connection between a user and a database and frees all resources allocated to that user for the specified connection (all cursors are closed and all compiled statements are dropped). The connection to be broken is specified as the connection name or as one of the keywords ALL, CURRENT or DEFAULT. (If a transaction is active when the DISCONNECT is executed, an error is raised and the connection remains open).

A connection does not have to be active in order to be disconnected. If an inactive connection is broken, the application still has uninterrupted access to the database through the current (active) connection, but the broken connection is no longer available for activation with SET CONNECTION.

If the active connection is broken, the application program cannot access the database until a new CONNECT or SET CONNECTION statement is issued.

Note: The distinction between breaking a connection with DISCONNECT and making a connection inactive by issuing a CONNECT or SET CONNECTION for a different connection is, a broken connection has no saved resources and cannot be reactivated by SET CONNECTION.

The table below summarizes the effect on the connection con1 of CONNECT, DISCONNECT and SET CONNECTION statements depending on the state of the connection.

Statement	con1 non-existent	con1 current	con1 inactive
CONNECT TO db1 AS con1	con1 current	error - connection already exists	error - connection already exists
DISCONNECT con1	error - connection does not exist	con1 disconnected	con1 disconnected
SET CONNECTION con1	error - connection does not exist	ignored	con1 made current
CONNECT TO db2 AS con2	-	con1 made inactive	con1 unaffected
DISCONNECT con2	-	con1 unaffected	con1 unaffected
SET CONNECTION con2	-	con1 made inactive	con1 unaffected

PROGRAM Idents - ENTER and LEAVE

PROGRAM idents may be entered from within an application program by using the ENTER statement, see the Mimer SQL Reference Manual, ENTER for the syntax description. This statement must be issued in a context where a user is already connected as PROGRAM idents cannot connect directly to the system.

When a PROGRAM ident is entered, any privileges granted to that ident become current and privileges belonging to the previous ident (i.e. the ident issuing the ENTER statement) are suspended. However, any cursors opened by the previous ident remain open.

PROGRAM idents are disconnected with the LEAVE statement. If LEAVE is requested with the optional keyword RETAIN, the full environment of the PROGRAM ident being left is kept.

Cursors left open by the PROGRAM ident are deactivated but not closed, and retain their positions in the respective result tables. The environment is restored if the PROGRAM ident is re-entered.

If LEAVE is requested without RETAIN, the environment of the PROGRAM ident being left is dropped. This means that all cursors and compiled statements are destroyed.

Note: The distinction between leaving a PROGRAM ident with the option RETAIN and entering a new PROGRAM ident is, while both operations save the environment of the PROGRAM ident, cursors left open at ENTER may still be used but those left open at LEAVE RETAIN are inaccessible until the program ident is re-entered.

The statements ENTER and LEAVE may not be issued within transactions, see Transaction Handling and Database Security.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se