[Solved] HsqlDB 2.51 in Base

[Villeroy]

I found the shadows when I was looking at my database files, which one has been accessed when. I put all my HSQL backend files (*.script, *.property, *.data etc) in the same directory. It is somewhat disturbing when there appear copies of the frontend.

[psilocybe]

Hi Villeroy,

First of all: merry Christmas...

Let me quote you for helping me, as well as this post in the README accompanying the extension on my GitHub site offering this extension?

Our discussion has saved me a lot of time, and I think it is important to show it, because knowledge is only valuable if it is shared ...

[Villeroy]

Merry Christmas to you too. Anything I write on the internet is ... well ... on the internet.

[psilocybe]

I just noticed that if I could not open the embedded HsqlDB database it is because I had not installed the libreoffice-sdbc-hsqldb package, which brings the hsqldb.jar v1.8.x driver

There is no bug in LibreOffice, it's just a bad installation on my part...

Sometimes the problems add up

Sorry for this waste of time ....

[psilocybe]

I'm ready to upgrade to version 0.0.2, which will allow:

- Choose the version of HsqlDB (hsqldb.jar) used, by a file picker in a Dialog Option.
- To support all the protocols managed by HsqlDB (ie: hsql, hsqls, http, https, mem, file, res), if possible.
- To be able to log driver activity...

The ultimate goal would be to fully integrate the sdbcx layer in order to allow the administration by the Base graphical interface, of the users and the rights of an HsqlDB database, I reserve this for version 0.0.3

I will be back shortly ...

[psilocybe]

Hi all,

After much testing it seems that LibreOffice (6.4.x and 7.x) cannot load a provided HsqlDB driver (hsqldb.jar), if the Embedded HsqlDB driver is installed (and even the solution is sometimes to rename the hsqldb.jar in /usr/share/java, uninstalling the libreoffice-sdbc-hsqldb package does not seem sufficient...)

Can someone confirm this malfunction, at least in Linux ... because under Windows everything seems to work (loading two drivers of different version is possible...)

To overcome this limitation, I had to resolve to write a new driver: HsqlDBembeddedOOo to replace the failing LibreOffice Embedded HsqlDB built-in driver.

So if we install both drivers:
- HsqlDBembeddedOOo
- HsqlDBDriverOOo
we are able to access HsqlDB databases with different version of drivers...

Welcome to your comments...

[psilocybe]

Hi Villeroy,

Happy New Year to you and everyone.

This is how one would convert an embedded HSQL 1.8 into a stand-alone HSQL manually with a minimum of scripting:
1) Extract the database folder
2) Rename all the database files (script, properties, data etc) to DB_NAME.script, DB_NAME.properties, DB_NAME.data etc.
3) menu:Edit>Database>Connection... Type JDBC and enter a valid URL such as jdbc:hsqldb:file:///path/DB_NAME
4) Enter the driver class org.hsqldb.jdbcDriver
Now this will not work because no driver can be found.
5a) Drop support for embedded HSQL and add a class path to some hsqldb.jar to the office configuration.
OR
5b) Keep support for embedded HSQL and add a class path to some hsqldb.jar stored in the document. This requires a one line macro because the GUI does not have any such input box. I stumbled upon property JavaDriverClassPath while browsing the API on connection settings.

It might be a good idea to allow the HsqlDBembeddedOOo driver to do this job automatically if there is data present in the odb file...
This would allow to split an embedded odb to its opening, by the driver...
This also allows a conversion, embedded to split, automatic...

Subject to feasibility, I will reserve this improvement for version 0.0.2, and if we get there then we will have advanced...

Welcome to your good ideas...

[psilocybe]

1) Extract the database folder

Does anyone know if a UNO service is capable of performing this task?
Thank you.

Edit: The UNO service to use is: 'com.sun.star.packages.zip.ZipFileAccess'
Just pass the URL of the zip file as a string in a sequence when creating the service and follow the API...

[Villeroy]

Module com.sun.star.packages with sub-module zip

[psilocybe]

As an end of year gift: HsqlDBembeddedOOo version 0.0.2 is released.

This version allows to automatically split an odb when it is opened ... This makes the driver backward compatible with the built-in LibreOffice Embedded HsqlDB driver

Thank you again to Villeroy for giving me the path to follow...

[psilocybe]

Just to let you know that version 0.0.3 is available:

HsqlDBDriverOOo

HsqlDBembeddedOOo

Lots of fixes, works with OpenOffice 4.1.8 on Windows and MacOS

[psilocybe]

Hi all,

Can someone confirm this malfunction, at least in Linux ... because under Windows everything seems to work (loading two drivers of different version is possible...)

In fact to take a closer look, this bug has been around for almost 10 years... It was time to fix it, isn't it...

Everything is said in bug 139538 which is the end of a problem for using HsqlDB (other than 1.8.0) with LibreOffice under Linux...

And the icing on the cake: it's Embedded HsqlDB version 2.5.1.

Now we can say that HsqlDB is integrated into LibreOffice / OpenOffice.