Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

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 USER are allowed to log on to Mimer SQL.

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 ident and a database

 exec sql CONNECT TO 'db' AS 'con1' USER 'ident' USING 'pswd';

To connect using an OS_USER login with the same name as the current operating system user, provide an empty ident name string. E.g.

 exec sql CONNECT TO 'db' AS 'con2' USER ' ' USING ' ';

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, an OS_USER login is used for the connection attempt.


If the database name is given as an empty string, the DEFAULT database is used.

 exec sql CONNECT TO ' ' AS 'con1' USER 'ident' USING 'pswd';

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.

 exec sql CONNECT TO 'db' USER 'ident' USING 'pswd';

Implicit Connection

Normally, CONNECT should 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 DEFAULT database 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_USER login 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 using an OS_USER login. 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';
 -- Set activate connection to 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.


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.

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


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
Phone: +46 18 780 92 00
Mimer SQL Documentation TOC PREV NEXT INDEX