This article introduces the database feature that allows incoming and outgoing connections to a common relational database system.
The "Database Feature", which is not part of the default Odysseus, must be installed before. See also How to install a new feature
This part describes how create and drop a persistent database connection. Such a connection is stored and can be reused by using an unique name so that you do not have to set up all parameters each time you want to access a database. There are two possibilites to create such a persistent database connection.
There are different drivers for different DBMS (at the moment for MySQL, Postgres and Oracle) that can be used with a more confortable syntax. For example, the following query creates a connection with the name con1 to a MySQL server on localhost (this is an implicit setting of the driver) with a database called test:
CREATE DATABASE CONNECTION con1 AS mysql TO test |
To use another driver, you may use one of the following:
CREATE DATABASE CONNECTION con1b AS oracle TO test CREATE DATABASE CONNECTION con1c AS postgresql TO test |
You may also define the host and/or the port you want to connect to:
CREATE DATABASE CONNECTION con2 AS mysql TO test AT localhost : 3306 |
If no user nor password is used, the driver assumes the user "root" with no password. Since most databases need some credentials, this can be added as follows:
CREATE DATABASE CONNECTION con3 AS mysql TO test AT localhost : 3306 WITH USER dbuser PASSWORD dbpassword |
Of course, it is also possible to omit the host and port:
CREATE DATABASE CONNECTION con4 AS mysql TO test WITH USER dbuser PASSWORD dbpassword |
Notice, that the connection is not established until it used for the first time. Therefore, the correctness of the properties (database name, user, pass etc.) is not checked when the query is translated and installed. We call this a "lazy connection check". To force a check during the installation of the query, you have to disable the lazy connection check by using the NO_LAZY_CONNECTION_CHECK flag as the last parameter:
CREATE DATABASE CONNECTION con5 AS mysql TO test AT localhost : 3306 WITH USER dbuser PASSWORD dbpassword NO_LAZY_CONNECTION_CHECK |
To sum up, the last query means: "Open a connection to a MySQL server at localhost that is listen on port 3306 and save the connection under con5. Use the user dbuser and the password dbpassword and immediately check the connection property at compile time"
To allow additional drivers that may not have the common properties like host or user or password, there is alternativly a JDBC based interface. This also allows DBMS specific settings via the JDBC string/url.
For a MySQL (equal to the previous section), the following query creates a connection called jdbc1 to a local MySql server.
CREATE DATABASE CONNECTION jdbc1 JDBC jdbc:mysql://localhost:3306/test |
Remember that the JDBC string may have some database specific contents. If the user is not part of the JDBC string, you may also add them directly to query:
CREATE DATABASE CONNECTION jdbc2 JDBC jdbc:mysql://localhost:3306/test WITH USER dbuser PASSWORD dbpassword |
Furthermore, the lazy connection check (see previous section) may also be deactivated as follows:
CREATE DATABASE CONNECTION jdbc3 JDBC jdbc:mysql://localhost:3306/test WITH USER dbuser PASSWORD dbpassword NO_LAZY_CONNECTION_CHECK |
To remove a persistent query, you simply have to use the following command where conname corresponds to your connection name
DROP DATABASE CONNECTION conname |