jdbcDriverOOo

Ce document en français.

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

version 0.0.4

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 allows you to use the JDBC driver of your choice directly in Base.
It embeds the drivers for the following databases:

• HyperSQL or HsqlDB version 2.70 The supported HsqlDB managed protocols are: hsql://, hsqls://, http://, https://, mem://, file:// and res://
• H2 Database Engine version 2.219-SNAPSHOT (2022-06-13)
• Apache Derby version 10.15.2.0
• SQLite JDBC Driver version 3.39.3.1-20220916.082601-2
• MariaDB Connector/J version 3.1.1
• SmallSQL version 0.22

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 / OpenOffice of a JRE version 11 or later.
I recommend Adoptium as your Java installation source.

If you are using the HsqlDB driver with LibreOffice on Linux, then you are subject to bug 139538.
To work around the problem, please uninstall the packages:

• libreoffice-sdbc-hsqldb
• libhsqldb1.8.0-java

If you still want to use the Embedded HsqlDB functionality provided by LibreOffice, then install the HsqlDBembeddedOOo extension.
OpenOffice and LibreOffice on Windows are not subject to this malfunction.

Installation:

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

• Install jdbcDriverOOo.oxt extension version 0.0.4.

Restart LibreOffice / OpenOffice after installation.

Use:

This mode of use uses an HsqlDB database.

How to create a new database:

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

In step: Select database

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

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

In step: Set up user authentication

• click on button: Test connection

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

Have fun…

How to update the JDBC driver:

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 -> Base drivers -> JDBC Driver, 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

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.

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

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

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.

Has been tested with:

• LibreOffice 7.0.4.2 - Ubuntu 20.04 - LxQt 0.14.1

• LibreOffice 6.4.4.2 - Windows 7 SP1

• Apache OpenOffice 4.1.13 - Lubuntu 22.04

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:h2:*
• xdbc:derby:*
• xdbc:hsqldb:*

but uses the jdbc:* protocol internally to connect.

It also provides functionality that the JDBC driver implemented in LibreOffice / OpenOffice 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.

What has been done for version 0.0.1:

• The writing of this driver was facilitated by a discussion with Villeroy, on the OpenOffice forum, which I would like to thank, because knowledge is only worth if it is shared…

• Using the new version of HsqlDB 2.5.1.

• Many other fix…

What has been done for version 0.0.2:

• Added a dialog box allowing to update the driver (hsqldb.jar) in: Tools -> Options -> Base drivers -> HsqlDB driver

• Many other fix…

What has been done for version 0.0.3:

• I especially want to thank fredt at hsqldb.org for:

  • His welcome for this project and his permission to use the HsqlDB logo in the extension.

  • Its involvement in the test phase which made it possible to produce this version 0.0.3.

  • The quality of its HsqlDB database.

• Now works with OpenOffice on Windows.

• An unsupported protocol now displays an accurate error.

• A non-parsable url now displays a precise error.

• Now correctly handles spaces in filenames and paths.

• Many other fix…

What has been done for version 0.0.4:

• Rewrite of Driver in Java version 11 OpenJDK amd64 under Eclipse IDE for Java Developers version 4.23.0 with the plugins:
  • LOEclipse or LibreOffice Eclipse plugin for extension development version 4.0.1.
  • PyDev or Python IDE for Eclipse version 9.3.0.

• Writing the Statement, PreparedStatement, CallableStatement, ResultSet, ... services of JDBC (thanks to hanya for MRI which was of great help to me…)

  • com.sun.star.sdb.*
  • com.sun.star.sdbc.*
  • com.sun.star.sdbcx.*

• Integration in jdbcDriverOOo of H2 and Derby JDBC drivers in addition to HsqlDB. Implementation of Java Services:

  • Driver-HsqlDB.jar
  • Driver-H2.jar
  • Driver-Derby.jar

  In order to correct possible defects, or incompatibility with the UNO API, of embedded JDBC drivers.

• Renamed the HsqlDBDriverOOo repository and extension to jdbcDriverOOo.

• Support in Base for auto-incrementing primary keys for HsqlDB, H2 and Derby.

• Writing of com.sun.star.sdbcx.Driver. This high-level driver must allow the management of users, roles and privileges in Base. Its use can be disabled via the menu: Tools -> Options -> Base drivers -> JDBC Driver.

• Implemented a Java service provider UnoLogger.jar for the SLF4J API to be able to redirect driver logging from the underlying databases to the UNO API com.sun.star.logging.*.

• Rewrite, following the MVC model, of the Options dialog accessible via the menu: Tools -> Options -> Base drivers -> JDBC Driver, to allow:

  • Updating and/or adding Java archives of JDBC drivers.
  • Enabling driver logging of the underlying database.

• Writing, following the MVC model, administration windows for users and roles (groups) and their associated privileges, accessible in Base via the menu: Administration -> User administration and/or Administration - > Group administration, allowing:

  • Management of users and their privileges.
  • Management of roles (groups) and their privileges.

  These new features have only been tested with the HsqlDB driver so far.

• Many other fix…

What remains to be done for version 0.0.4:

• Add new languages for internationalization…

• Anything welcome…