A database connection is a session of work, opened by the program to communicate with a specific database server, in order to execute SQL statements as a specific user.
Before working with database connections, make sure you have properly installed and configured all software, using the correct database client environment and driver. It is very important to understand database client settings, regarding user authentication as well as database client character set configuration.
Figure 1. Schema example of a program using three database connections
The database user can be identified explicitly for each connection. Usually, the user is identified by a login and a password, or by using the authentication mechanism of the operating system (or even from a tier security system).
Database connection instructions DATABASE / CONNECT TO can not be prepared and executed as dynamic SQL statements.
There are two kind of connection modes: unique-session and multi-session mode. When using the DATABASE and CLOSE DATABASE instructions, the program is in unique-session mode. When using the CONNECT TO, SET CONNECTION and DISCONNECT instructions, the program is in multi-session mode. These connection modes are not compatible.
In unique-session mode, the DATABASE instruction initiates a connection the the database server and creates the current session. The database connection is terminated with the CLOSE DATABASE instruction, or when another DATABASE instruction is executed, or when the program ends.
In multi-session mode, open a database session with the CONNECT TO instruction. Other connections can be created with subsequent CONNECT TO instructions. To switch to a specific session, use the SET CONNECTION instruction; this suspends other opened connections. Disconnect from a specific or from all sessions with the DISCONNECT instruction. The end of the program disconnects all sessions automatically.
Once connected to a database server, the program uses the current session to execute SQL statements in that context.