Database connection - Tutorial

Last Update: 17.03.2025

Introduction


We offer a connection to about 30 database systems. In general, any relational database that provides a Java8-compatible JDBC driver can be connected. With version 4.5 and newer, Java11 drivers can be used.

Note: From version 4.5, databases can be created comfortably via the GUI. For this, no adjustment of the configuration file ./etc/database.xml and no restart is necessary anymore! (Only after uploading the driver, a restart is needed.)

JDBC driver download


Following is an overview of the most popular drivers for database systems such as MS SQL Server, MySQL, MariaDB, Oracle, PostgreSQL.


Database

Link

Info

MS SQL

Current version: https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15

Older versions: https://learn.microsoft.com/en-us/sql/connect/jdbc/release-notes-for-the-jdbc-driver?view=sql-server-ver15#previous-releases

In the support matrix and version compatibility you can find the correlation JDBC driver → MS SQL Server version:

https://learn.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server-support-matrix?view=sql-server-ver15

Note: If you have several MS SQL servers of different versions in use, select a driver that covers the range between the lowest version and the highest version. Only one driver can be used.

MySQL

https://downloads.mysql.com/archives/c-j/

If JDBC driver versions 8.0.27 and 8.0.28 are used, these should be updated.

MariaDB

Current version: mariadb-java-client-3.2.0.jar (direct link)

Please do not use mariadb-java-client-3.2.0-sources.jar, javadoc.jar, sources.jar!

Oracle

https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html

You need a Java8 compatible JDBC driver that matches your Oracle database. Depending on Oracle version, 18, 19, 21. Driver name: ojdbc8.jar

PostgreSQL

https://jdbc.postgresql.org/download/

In the installation of Postgres 12 with the JDBC driver in version 42.2.11 (4.2 for Java 8 ) errors occur and the Integration Server cannot be started. Workaround: Use JDBC driver in version 42.2.10 (4.2 for Java 8).

Informix DB

https://mvnrepository.com/artifact/com.ibm.informix/jdbc/4.50.10

On the download page, please download the jar (under Files) in the upper area.

Snowflake

https://repo1.maven.org/maven2/net/snowflake/snowflake-jdbc/

Tested with driver version 3.9.2
JDBC Connection: https://docs.snowflake.com/en/developer-guide/jdbc/jdbc-configure

JDBC driver installation


  1. As of version 4.5, the drivers can be uploaded via the GUI (item 1) Administration → Databases/Connectors → Manage Drivers.
    The drivers can also be placed manually in folder ./extlib/. In general, only put .jar files there (zip is not supported).

  2. Restart the service.
    In order for the driver to be loaded correctly, the Integration Server must be restarted once. Please take this into account during installation/commissioning.

  3. Verify that the driver has been initialized correctly.
    At start time, depending on the database system and driver, something like the following should be in the ./logs/wrapper.log.

    INFO   | jvm 1    | 2022/08/10 08:53:24 | 178      file:/C:/Lobster/IS/extlib/mssql-jdbc-10.2.0.jre8.jar
    INFO   | jvm 1    | 2022/08/10 08:53:24 | 179      file:/C:/Lobster/IS/extlib/mysql-connector-java-8.0.26.jar
    INFO   | jvm 1    | 2022/08/10 08:53:24 | 180      file:/C:/Lobster/IS/extlib/ojdbc8.jar

Setting up the database connection


As of version 4.5, database connections can be conveniently created via the GUI.

  1. By right-clicking, or using the 'plus' button on the lower right side, a new window opens to add a new connection.

    images/download/attachments/150864779/new_DB_enty_EN-version-1-modificationdate-1695782623013-api-v2.png

  2. Via Select template, you can choose from different templates/presets. Select the database template.

  3. Adjust the content in the Database field (the database string) by replacing DB_HOST_OR_IP and DB_NAME.

  4. Fill in missing fields and JDBC properties like Catalog Name, User, Password, etc. if needed.

  5. Finally, click Apply to create the new database connection.

Note: A connection test is possible in the context menu of the overview!
images/download/attachments/150864779/image-2024-2-19_15-26-32-version-2-modificationdate-1709272351910-api-v2.png

Testing the database connection


The connection can be tested via Plugins → SQL monitor.


images/download/attachments/150864779/image-2023-8-7_10-34-48-version-1-modificationdate-1695782554261-api-v2.png


To do this, select the previously created database alias from the drop-down field.

Clicking on Tables/Procedures should return an overview of all available tables and stored procedures.


images/download/attachments/150864779/image-2023-8-7_10-50-29-version-1-modificationdate-1695782554275-api-v2.png


If the message 'No entries found!' is displayed or a select statement cannot be executed (pool exhausted), check the database connection settings again!

Disclaimer


Databases and JDBC drivers used to access databases are third-party products and are neither supported nor provided by Lobster. Any support or advice on databases or JDBC drivers that may nevertheless be provided by the Lobster support is voluntary and in no way implies a transfer of responsibility to them. The installation, operation and maintenance of databases/JDBC drivers, or measures carried out on them, are always and without exception the responsibility of the customer.

The Lobster support will of course be happy to assist you with the internal configurations necessary to connect functioning third-party systems.