Databases

Relational databases are accessed, among other things, by the Replication and by the database-based identity provider. To enable access to the databases, you must first install the appropriate JDBC drivers for your database system.

Create a database connection only after the drivers have been installed. See Backend Connections of type data_source

Driver Installation

If you want to connect to database systems from different vendors, perform the driver installation steps for each database type.

The driver installation causes Karaf to restart various bundles. These include the BPC Backend Core Bundle (BPC-be-core). Since user sessions are stored transiently in this bundle, they are lost during the restart, and users must log in again.

It is recommended to configure the database drivers via the central configuration file. In this case, you can perform a Karaf update without having to reconfigure anything. You simply need to provision the database driver in the new Karaf installation. In that case, you can ignore the following points.

If the driver is available for different Java versions (e.g., for MSSQL), use the Java 17 version.

Oracle

Option: Latest driver
- An Internet connection is required to automatically download the latest driver
- Open the Karaf console and execute the following command: feature:install pax-jdbc-oracle
Option: Specific version of the driver
- Download the JDBC driver for your Oracle version: Download
- Copy the JDBC driver (e.g., ojdbc8.jar) to the folder <BPC-INSTALL-DIR>/karaf/deploy
- Open the Karaf console and execute the following command: feature:install pax-jdbc-oracle

For information on configuring Oracle databases, see Oracle Database.

Microsoft SQL Server (MSSQL)

Option: Relatively recent driver
- An Internet connection is required to automatically download the driver
- Open the Karaf console and execute the following command: feature:install pax-jdbc-mssql
Option: Specific version of the driver (offline)
- Download the JDBC driver for your MSSQL version: Download
- Copy the JDBC driver (e.g., mssql-jdbc-11.2.3.jre11.jar) to the folder <BPC-INSTALL-DIR>/karaf/deploy
- It is not necessary to run the command: feature:install pax-jdbc-mssql
Option: Specific version of the driver (online)
- An Internet connection is required to automatically download the driver
- Open the Karaf console and execute the following command: bundle:install -s mvn:com.microsoft.sqlserver/mssql-jdbc/13.2.0.jre11
- It is not necessary to run the following command: feature:install pax-jdbc-mssql

Please note that the internal driver name for MSSQL varies across different versions. This means that if the driver changes, you may need to update the specified driver name in existing Backend Connections files.

MariaDB

Option: Relatively recent driver
- An internet connection is required to automatically download the driver
- Open the Karaf console and execute the following command:
  feature:install pax-jdbc-mariadb
Option: Specific version of the driver
- Download the JDBC driver for your MariaDB version: Download
- Copy the JDBC driver (e.g., mariadb-java-client-3.5.7.jar) to the folder <BPC-INSTALL-DIR>/karaf/deploy
- Open the Karaf console and run the following command:
  feature:install pax-jdbc-mariadb

MySQL

Option: Relatively recent driver
- An Internet connection is required to automatically download the driver
- Any previously installed MariaDB driver must be uninstalled first. Open the Karaf console and execute the following command:
  feature:uninstall pax-jdbc-mariadb
- Determine the pax-jdbc version of your installation. To do this, execute the following in the Karaf console:
  feature:repo-list | grep pax.jdbc
- Now execute the following commands in the Karaf console, adjusting the version as needed:
  feature:repo-add mvn:org.ops4j.pax.jdbc/pax-jdbc-features/1.5.7/xml/features-gpl feature:install pax-jdbc-mysql
  If the relatively recent driver, e.g., 8.3.0, is not recent enough and, for example, version 8.4.0 must be used. First, download this version and copy it to the folder <BPC-INSTALL-DIR>/karaf/deploy. Then follow the steps above. If there is a major version jump in the JDBC driver, use the next option.
Option: New major version of the driver

If the JDBC driver from the first option listed is not recent enough (e.g., 8.3.0) and the JDBC driver to be used must be a new major version (e.g., 9.5.0). Then the pax-jdbc-mysql JAR file must be patched.
- Any previously installed MariaDB driver must be uninstalled first. Open the Karaf console and execute the following command:
  feature:uninstall pax-jdbc-mariadb
- Determine the pax-jdbc version of your installation. To do this, run the following in the Karaf console:
  feature:info pax-jdbc
- In a shell, run the following commands to download and unzip the JAR archive, adjusting the version as needed:
  mkdir pax-jdbc-mysql-patch cd pax-jdbc-mysql-patch wget https://repo1.maven.org/maven2/org/ops4j/pax/jdbc/pax-jdbc-mysql/1.5.7/pax-jdbc-mysql-1.5.7.jar jar xf pax-jdbc-mysql-1.5.7.jar rm pax-jdbc-mysql-1.5.7.jar
- Using a text editor, open the file META-INF/MANIFEST.MF and increase the existing "from/to" version ranges, e.g., from [8.3,9) to [8.3,10). [8.3,9) specifies that only MySQL JDBC drivers from version 8.3.0 up to but not including version 9.0.0 can be used. We increase this by changing it to 10.0.0. This also allows driver version 9.5.0 to be used.
- Create the JAR file with the changes
  jar cfM pax-jdbc-mysql-1.5.7.jar .
- Copy the pax-jdbc-mysql-1.5.7.jar to the folder <BPC-INSTALL-DIR>/karaf/deploy
Option: Specific driver version (offline)
- Download the JDBC driver for your MySQL version (platform-independent): Download
- Copy the JDBC driver (e.g., mysql-connector-j-9.5.0.jar) to the folder <BPC-INSTALL-DIR>/karaf/deploy
- Open the Karaf console and run the following command:
  feature:install pax-jdbc-spec
- Make sure that, when configuring the database connection, the value for ` "xa" ` is set to ` "false" ` and that ` com.mysql.cj-native ` is used as the driver name.
Option: Specific version of the driver (online)
- An Internet connection is required to automatically download the driver
- Open the Karaf console and execute the following commands:
  bundle:install -s mvn:com.mysql/mysql-connector-j/9.5.0 feature:install pax-jdbc-spec
- When configuring the database connection, ensure that the value for ` "xa" ` is set to ` "false" ` and that ` com.mysql.cj-native ` is used as the driver name.

PostgreSQL

No driver installation is required
Open the Karaf console and execute the following command: feature:install pax-jdbc-postgresql

Troubleshooting

Is the driver in the correct location?

Make sure that the appropriate JDBC driver is located in the directory <BPC-INSTALL-DIR>/karaf/deploy.

Was the driver detected and registered by Karaf?

To check this, run the following command in the Karaf console: jdbc:ds-factories

Sample output for a registered Oracle JDBC driver

virtimo@bpc()> jdbc:ds-factories
Name              │ Class                    │ Version │ Registration bundle
──────────────────┼──────────────────────────┼─────────┼────────────────────────────────
ojdbc6.jar-native │ oracle.jdbc.OracleDriver │ 0.0.0   │ ojdbc6.jar [229]
oracle            │ oracle.jdbc.OracleDriver │         │ org.ops4j.pax.jdbc.oracle [230]

Have all necessary JDBC features been installed?

To do this, run the following command in the Karaf console: feature:list | grep jdbc

In the output, you must ensure that the following features have the status “ Started ”:

jdbc
pax-jdbc-config
pax-jdbc-<datenbankname>
pax-jdbc-pool-dbcp2

If, for example, the feature pax-jdbc-pool-dbcp2 does not have the status “ Started,” enter feature:install pax-jdbc-pool-dbcp2 to install and start it.

Sample output of an installation with the Oracle driver

virtimo@bpc()> feature:list | grep jdbc
jdbc                            │ 4.2.8            │ x        │ Started     │ enterprise-4.2.8                  │ JDBC service and commands
pax-transx-jdbc                 │ 0.4.4            │          │ Uninstalled │ pax-transx-0.4.4                  │
pax-jdbc-spec                   │ 1.4.4            │          │ Started     │ org.ops4j.pax.jdbc-1.4.4          │ Provides OSGi JDBC Service spec
pax-jdbc                        │ 1.4.4            │          │ Started     │ org.ops4j.pax.jdbc-1.4.4          │ Provides JDBC Service support
pax-jdbc-config                 │ 1.4.4            │ x        │ Started     │ org.ops4j.pax.jdbc-1.4.4          │ Provides JDBC Config support
pax-jdbc-db2                    │ 1.4.4            │          │ Uninstalled │ org.ops4j.pax.jdbc-1.4.4          │ Provides JDBC DB2 DataSourceFactory
pax-jdbc-teradata               │ 1.4.4            │          │ Uninstalled │ org.ops4j.pax.jdbc-1.4.4          │ Provides JDBC Teradata DataSourceFactory
pax-jdbc-derby                  │ 1.4.4            │          │ Uninstalled │ org.ops4j.pax.jdbc-1.4.4          │ Provides JDBC Derby Embedded DataSourceFactory
pax-jdbc-derbyclient            │ 1.4.4            │          │ Uninstalled │ org.ops4j.pax.jdbc-1.4.4          │ Provides JDBC Derby Client DataSourceFactory
pax-jdbc-h2                     │ 1.4.4            │          │ Uninstalled │ org.ops4j.pax.jdbc-1.4.4          │ Provides JDBC H2 DataSourceFactory
pax-jdbc-hsqldb                 │ 1.4.4            │          │ Uninstalled │ org.ops4j.pax.jdbc-1.4.4          │ Provides JDBC HSQLDB DataSourceFactory
pax-jdbc-mariadb                │ 1.4.4            │          │ Uninstalled │ org.ops4j.pax.jdbc-1.4.4          │ Provides JDBC Mariadb DataSourceFactory
pax-jdbc-oracle                 │ 1.4.4            │ x        │ Started     │ org.ops4j.pax.jdbc-1.4.4          │ Provides JDBC Oracle DataSourceFactory
pax-jdbc-postgresql             │ 1.4.4            │          │ Uninstalled │ org.ops4j.pax.jdbc-1.4.4          │ Provides JDBC PostgreSQL DataSourceFactory
pax-jdbc-sqlite                 │ 1.4.4            │          │ Uninstalled │ org.ops4j.pax.jdbc-1.4.4          │ Provides JDBC SQLite DataSourceFactory
pax-jdbc-mssql                  │ 1.4.4            │          │ Uninstalled │ org.ops4j.pax.jdbc-1.4.4          │ Provides JDBC MSSQL DataSourceFactory
pax-jdbc-jtds                   │ 1.4.4            │          │ Uninstalled │ org.ops4j.pax.jdbc-1.4.4          │ Provides JDBC jTDS DataSourceFactory
pax-jdbc-pool-dbcp2             │ 1.4.4            │ x        │ Started     │ org.ops4j.pax.jdbc-1.4.4          │ Provides JDBC Pooling DataSourceFactory
pax-jdbc-pool-c3p0              │ 1.4.4            │          │ Uninstalled │ org.ops4j.pax.jdbc-1.4.4          │ Provides JDBC Pooling using C3P0
pax-jdbc-pool-hikaricp          │ 1.4.4            │          │ Uninstalled │ org.ops4j.pax.jdbc-1.4.4          │ Provides JDBC Pooling DataSourceFactory using Hik
pax-jdbc-pool-aries             │ 1.4.4            │          │ Uninstalled │ org.ops4j.pax.jdbc-1.4.4          │ Provides JDBC Pooling DataSourceFactory using Ari
pax-jdbc-pool-narayana          │ 1.4.4            │          │ Uninstalled │ org.ops4j.pax.jdbc-1.4.4          │ Provides JDBC Pooling DataSourceFactory using Nar
pax-jdbc-pool-transx            │ 1.4.4            │          │ Uninstalled │ org.ops4j.pax.jdbc-1.4.4          │ Provides JDBC Pooling DataSourceFactory using Pax

Is a database connection available?

The data connections created in BPC (see Backend Connections) must also be viewable from the Karaf console. To list them, use the following command: jdbc:ds-list

Sample output of an Oracle database connection

virtimo@bpc()> jdbc:ds-list
Name         │ Service Id │ Product │ Version                                                                   │ URL                                 │ Status
─────────────┼────────────┼─────────┼───────────────────────────────────────────────────────────────────────────┼─────────────────────────────────────┼───────
oracle-local │ 245        │ Oracle  │ Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production │ jdbc:oracle:thin:@localhost:1521:XE │ OK

Keywords: