Connecting to a Database
A database in Mimer SQL refers to the complete collection of databanks that may be accessed from one Mimer SQL system.
Mimer ESQL supports the ability to change between different connections (i.e. access different databases) from within the same application program. An application program may have several database connections open simultaneously, although only one is active at any one time.
Only idents of type
OS_USERare allowed to log on to Mimer SQL.
The CONNECT Statement
Logging on is requested from an application program with the
CONNECTstatement, see the Mimer SQL Reference Manual, CONNECT, for the syntax description.
CONNECTstatement establishes a connection between a user and a database. The user may be a
USERident (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_USERident with the same name as the current operating system user, provide an empty ident name string.
ExampleEXEC 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 or Named Database
If the keyword
DEFAULTis used, the
OS_USERident is used for the connection attempt. If the database name is given as an empty string, the
DEFAULTdatabase is used.
The database may be given an explicit connection name for use in
SET CONNECTIONstatements. If no explicit name is given, the database name is used as the connection name.
CONNECTshould be the first SQL statement executed in an application program using ESQL. However, if another SQL 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
DEFAULTdatabase using the current operating system user.
In order for the implicit connect attempt to be successful, the current operating system user must be defined as an
OS_USERident in Mimer SQL and the
DEFAULTdatabase 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.
A connection established by a successful
CONNECTstatement 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 CONNECTIONstatement. Resources such as cursors used by a connection are saved when the connection becomes dormant, and are restored by the appropriate
The statement sequence below connects to a user-specific database as a specified ident name and to the
OS_USER. The user-specific connection is initially active. Then the
DEFAULTconnection 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.
DISCONNECTstatement 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
DEFAULT. (If a transaction is active when the
DISCONNECTis 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
If the active connection is broken, the application program cannot access the database until a new
SET CONNECTIONstatement 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
SET CONNECTIONstatements depending on the state of the connection.
CONNECT TO db1 AS con1
error - connection already exists error - connection already exists
error - connection does not exist
SET CONNECTION con1
error - connection does not exist ignored
CONNECT TO db2 AS con2
SET CONNECTION con2
PROGRAM Idents - ENTER and LEAVE
PROGRAMidents may be entered from within an application program by using the
ENTERstatement, 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
PROGRAMidents cannot connect directly to the system.
PROGRAMident is entered, any privileges granted to that ident become current and privileges belonging to the previous ident (i.e. the ident issuing the
ENTERstatement) are suspended. However, any cursors opened by the previous ident remain open.
PROGRAMidents are disconnected with the
LEAVEis requested with the optional keyword
RETAIN, the full environment of the
PROGRAMident being left is kept.
Cursors left open by the
PROGRAMident are deactivated but not closed, and retain their positions in the respective result tables. The environment is restored if the
PROGRAMident is re-entered.
LEAVEis requested without
RETAIN, the environment of the
PROGRAMident 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.
LEAVEmay 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