jdbcDriverOOo

The JDBC driver for LibreOffice

prrvchr
8
1
Python

jdbcDriverOOo logo Documentation

Ce document en français.

The use of this software subjects you to our Terms Of Use

version 1.4.6

Introduction:

jdbcDriverOOo is part of a Suite of LibreOffice and/or OpenOffice extensions allowing to offer you innovative services in these office suites.

This extension is the transcription in pure Java of the java.sql.* API to the com.sun.star.sdbc, com.sun.star.sdbcx and com.sun.star.sdb API of UNO.
It allows you to use the JDBC driver of your choice directly in Base.
It embeds the drivers for the following databases:

Thanks to drivers providing an integrated database engine such as: HsqlDB, H2, SQLite, Derby or Jaybird, it is possible in Base to very easily create and manage databases, as easily as creating Writer documents.
You will find the information needed to create a database with these drivers in the section: Connection URL

Being free software I encourage you:

  • To duplicate its source code.
  • To make changes, corrections, improvements.
  • To open issue if needed.

In short, to participate in the development of this extension.
Because it is together that we can make Free Software smarter.

Requirement:

jdbcDriverOOo is a JDBC driver written in Java.
Its use requires the installation and configuration in LibreOffice of a Java JRE or JDK version 17 or later.
I recommend Adoptium as your Java installation source.

The minimum version of LibreOffice supported by the jdbcDriverOOo extension depends on how you installed LibreOffice on your computer:

  • Regardless of platform, if you installed LibreOffice from the LibreOffice download site, the minimum version of LibreOffice is 7.0.

  • On Linux, if you used the package manager to install LibreOffice, the minimum version of LibreOffice is 6.0. However, you must ensure that the system-provided Python version is not lower than 3.8.

    In addition, you may experience the following issues:

    • You are subject to bug #139538. To work around the problem, please uninstall the packages with commands:
      • sudo apt remove libreoffice-sdbc-hsqldb (to uninstall the libreoffice-sdbc-hsqldb package)
      • sudo apt remove libhsqldb1.8.0-java (to uninstall the libhsqldb1.8.0-java package)

    If you still want to use the Embedded HsqlDB functionality provided by LibreOffice, then install the HyperSQLOOo extension.

    • Your system-provided Python packages are out of date. The extension’s logging will allow you to check if this is the case. It is accessible via the menu: Tools -> Options -> LibreOffice Base -> Pure Java JDBC driver -> UNO driver settings -> View log -> System Info and requires restarting LibreOffice after activation.
      If outdated packages appear, you can update them with the command:
      pip install --upgrade <package-name>

    For more information see: What has been done for version 1.1.0.

Installation:

It seems important that the file was not renamed when it was downloaded.
If necessary, rename it before installing it.

Restart LibreOffice after installation.
Be careful, restarting LibreOffice may not be enough.

  • On Windows to ensure that LibreOffice restarts correctly, use Windows Task Manager to verify that no LibreOffice services are visible after LibreOffice shuts down (and kill it if so).
  • Under Linux or macOS you can also ensure that LibreOffice restarts correctly, by launching it from a terminal with the command soffice and using the key combination Ctrl + C if after stopping LibreOffice, the terminal is not active (no command prompt).

After restarting LibreOffice, you can ensure that the extension and its driver are correctly installed by checking that the io.github.prrvchr.jdbcDriverOOo.Driver driver is listed in the Connection Pool, accessible via the menu: Tools -> Options -> LibreOffice Base -> Connections. It is not necessary to enable the connection pool.

If the driver is not listed, the reason for the driver failure can be found in the extension’s logging. This log is accessible via the menu: Tools -> Options -> LibreOffice Base -> Pure Java JDBC Driver -> Logging Options.
The Driver logging must first be enabled and then LibreOffice restarted to get the error message in the log.

Remember to first update the version of the Java JRE or JDK installed on your computer, this new version of jdbcDriverOOo requires Java version 17 or later instead of Java 11 previously.

Use:

This explains how to use an HsqlDB database.
The protocols supported by HsqlDB are: hsql://, hsqls://, http://, https://, mem://, file:// and res://.
This mode of use explains how to connect with the file:// and hsql:// protocols.

How to create a new database:

In LibreOffice / OpenOffice go to menu: File -> New -> Database

jdbcDriverOOo screenshot 1

In step: Select database

  • select: Connect to an existing database
  • choose: HsqlDB Driver
  • click on button: Next

jdbcDriverOOo screenshot 2

In step: Connection settings

  • for the protocol: file://

    • in Datasource URL put:
      • for Linux: file:///tmp/testdb;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
      • for Windows: file:///c:/tmp/testdb;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false

  • for the protocol: hsql://

    • In a terminal, go to a folder containing the hsqldb.jar archive and run:
      • for Linux: java -cp hsqldb.jar org.hsqldb.server.Server --database.0 file:///tmp/testdb --silent false
      • for Windows: java -cp hsqldb.jar org.hsqldb.server.Server --database.0 file:///c:/tmp/testdb --silent false
    • in Datasource URL put: hsql://localhost/

  • click on button: Next

jdbcDriverOOo screenshot 3

In step: Set up user authentication

  • click on button: Test connection

jdbcDriverOOo screenshot 4

If the connection was successful, you should see this dialog window:

jdbcDriverOOo screenshot 5

Have fun…

How to update the JDBC driver:

If you want to update an embedded HsqlDB database (single odb file), please refer to the section: How to migrate an embedded database.

It is possible to update the JDBC driver (hsqldb.jar, h2.jar, derbytools.jar) to a newer version.
If you use HsqlDB as database, follow these steps:

  1. Make a copy (backup) of the folder containing your database.
  2. Start LibreOffice / OpenOffice and change the version of the HsqlDB driver via menu: Tools -> Options -> LibreOffice Base -> Pure Java JDBC Driver -> JDBC drivers settings, by a more recent version.
  3. Restart LibreOffice / OpenOffice after changing the driver (hsqldb.jar).
  4. In Base, after opening your database, go to menu: Tools -> SQL and type the SQL command: SHUTDOWN COMPACT or SHUTDOWN SCRIPT.

Now your database is up to date.

LibreOffice/OpenOffice Base improvement:

This driver allows in LibreOffice / OpenOffice Base the management of users, roles (groups) and their associated privileges of the underlying database.

Managing Users and Privileges in Base:

User management of the underlying database is accessible in Base via the menu: Administration -> User administration

jdbcDriverOOo screenshot 6

The privileges management of the users of the underlying database is accessible in this window by the button: Change privileges
If the privilege is inherited from an assigned role, the checkbox is a three-state type.

jdbcDriverOOo screenshot 7

Managing roles (groups) in Base:

The management of the roles (groups) of the underlying database is accessible in Base via the menu: Administration -> Group administration

jdbcDriverOOo screenshot 8

The management of users who are members of the group of the underlying database is accessible in this window via the button: Group users

jdbcDriverOOo screenshot 9

The management of roles assigned to the group of the underlying database is accessible in this window via the button: Group roles
This functionality is an extension of the UNO API and will only be available if the underlying LibreOffice / OpenOffice driver allows it.

jdbcDriverOOo screenshot 10

Connection URL:

Certain databases such as HsqlDB, H2, SQLite Derby or Firebird via Jaybird allow the creation of the database during connection if this database does not yet exist.
This feature makes it as easy to create databases as Writer documents. Generally it is enough to add the option expected by the driver to the connection URL.
This connection URL may be different depending on the operating system of your computer (Windows, Linux or MacOS).
To create a database, in LibreOffice go to the menu: File -> New -> Database -> Connect to an existing database, then according to your choice:

  • HsqlDB pure Java:
    • Linux: file:///home/prrvchr/testdb/hsqldb/db;hsqldb.default_table_type=cached;create=true
    • Windows: C:\Utilisateurs\prrvc\testdb\hsqldb\db;hsqldb.default_table_type=cached;create=true
  • H2 pure Java:
    • Linux: file:///home/prrvchr/testdb/h2/db
    • Windows: C:\Utilisateurs\prrvc\testdb\h2\db
  • SQLite pure Java:
    • Linux: file:///home/prrvchr/testdb/sqlite/test.db
    • Windows: C:/Utilisateurs/prrvc/testdb/sqlite/test.db
  • Derby pure Java:
    • Linux: /home/prrvchr/testdb/derby;create=true
    • Windows: C:\Utilisateurs\prrvc\testdb\derby;create=true
  • Firebird pure Java:
    • Linux: embedded:/home/prrvchr/testdb/firebird?createDatabaseIfNotExist=true
    • Windows: embedded:C:\Utilisateurs\prrvc\testdb\firebird?createDatabaseIfNotExist=true

How to build the extension:

Normally, the extension is created with Eclipse for Java and LOEclipse. To work around Eclipse, I modified LOEclipse to allow the extension to be created with Apache Ant.
To create the jdbcDriverOOo extension with the help of Apache Ant, you need to:

  • Install the Java SDK version 17 or higher.
  • Install Apache Ant version 1.9.1 or higher.
  • Install LibreOffice and its SDK version 7.x or higher.
  • Clone the jdbcDriverOOo repository on GitHub into a folder.
  • From this folder, move to the directory: source/jdbcDriverOOo/
  • In this directory, edit the file: build.properties so that the office.install.dir and sdk.dir properties point to the folders where LibreOffice and its SDK were installed, respectively.
  • Start the archive creation process using the command: ant
  • You will find the generated archive in the subfolder: dist/

Has been tested with:

  • LibreOffice 24.2.1.2 (x86_64)- Windows 10

  • LibreOffice 7.3.7.2 - Lubuntu 22.04

  • LibreOffice 24.2.1.2 - Lubuntu 22.04

  • LibreOffice 24.8.0.3 (x86_64) - Windows 10(x64) - Python version 3.9.19 (under Lubuntu 22.04 / VirtualBox 6.1.38)

I encourage you in case of problem 😕
to create an issue
I will try to solve it 😄

Historical:

Introduction:

This driver was written to work around certain problems inherent in the UNO implementation of the JDBC driver built into LibreOffice / OpenOffice, namely:

  • The inability to provide the path to the Java driver archive (hsqldb.jar) when loading the JDBC driver.
  • Not being able to use prepared SQL statements (PreparedStatement) see bug #132195.

In order to take advantage of the latest features offered by databases and among others HsqlDB, it was necessary to write a new driver.

Until version 0.0.3, this new driver is just a wrapper in Python around the UNO services provided by the defective LibreOffice / OpenOffice JDBC driver.
Since version 0.0.4, it has been completely rewritten in Java under Eclipse, because who better than Java can provide access to JDBC in the UNO API?
In order not to prevent the native JDBC driver from working, it loads when calling the following protocols:

  • xdbc:*
  • xdbc:hsqldb:*
  • xdbc:sqlite:*
  • xdbc:mariadb:*
  • xdbc:...

but uses the jdbc:* protocol internally to connect.

It also provides functionality that the JDBC driver implemented in LibreOffice does not provide, namely:

  • The management of users, roles (groups) and privileges in Base.
  • The use of the SQL Array type in the queries.
  • Everything we are ready to implement.

All changes are logged in the version History