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

There are two possibilities how to use a database connection. For the one hand, you can create a persistent connection and reuse this connection in other queries. On the other hand, you can directly create a connection for each query. First, we show how to create and drop persistent connections and show how to use them in PQL and StreamSQL(CQL). Afterwards, we show how to directly setup a connection using PQL.

Open and drop persistent database connections

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.

Open a connection using preconfigured drivers

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:

#PARSER CQL
#TRANSCFG Standard
#QUERY
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"

Open a connection using JDBC

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

Drop a connection

To remove a persistent query, you simply have to use the following command where conname corresponds to your connection name

DROP DATABASE CONNECTION conname

Using a connection in PQL

Reading from a database

If you want to read from a database, you may use the DATABASESOURCE operator. For an existing connection called "con1" we can read from the table called "main":

datastream = DATABASESOURCE({connection='con1', table='main'})

Since PQL allows to create views and names for reusing connections (see The Odysseus Procedural Query Language (PQL) - Usage and Development), you may use this syntax, for example to create a dedicated source entry.

datastream := DATABASESOURCE({connection='con1', table='main'})

Then you can use this

Using a connection in StreamSQL (CQL)