[Solved] HsqlDB 2.51 in Base

Discuss the database features
User avatar
Villeroy
Volunteer
Posts: 31270
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HsqlDB 2.51 in Base

Post by 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. :)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
psilocybe
Posts: 107
Joined: Thu Jun 15, 2017 5:33 am

Re: HsqlDB 2.51 in Base

Post by psilocybe »

Hi Villeroy,

First of all: merry Christmas... :D

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 ...
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
User avatar
Villeroy
Volunteer
Posts: 31270
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HsqlDB 2.51 in Base

Post by Villeroy »

Merry Christmas to you too. Anything I write on the internet is ... well ... on the internet.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
psilocybe
Posts: 107
Joined: Thu Jun 15, 2017 5:33 am

Re: HsqlDB 2.51 in Base

Post by 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 :crazy:

Sorry for this waste of time ....
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
psilocybe
Posts: 107
Joined: Thu Jun 15, 2017 5:33 am

Re: HsqlDB 2.51 in Base

Post by 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 ...
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
psilocybe
Posts: 107
Joined: Thu Jun 15, 2017 5:33 am

Re: HsqlDB 2.51 in Base

Post by 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...
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
psilocybe
Posts: 107
Joined: Thu Jun 15, 2017 5:33 am

Re: HsqlDB 2.51 in Base

Post by psilocybe »

Hi Villeroy,

Happy New Year to you and everyone. :D
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...
Last edited by psilocybe on Sun Jan 03, 2021 1:41 pm, edited 1 time in total.
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
psilocybe
Posts: 107
Joined: Thu Jun 15, 2017 5:33 am

Re: HsqlDB 2.51 in Base

Post by 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...
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
User avatar
Villeroy
Volunteer
Posts: 31270
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HsqlDB 2.51 in Base

Post by Villeroy »

Module com.sun.star.packages with sub-module zip
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
psilocybe
Posts: 107
Joined: Thu Jun 15, 2017 5:33 am

Re: HsqlDB 2.51 in Base

Post by 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 :super:

Thank you again to Villeroy for giving me the path to follow... :bravo:
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
psilocybe
Posts: 107
Joined: Thu Jun 15, 2017 5:33 am

Re: HsqlDB 2.51 in Base

Post by psilocybe »

Just to let you know that version 0.0.3 is available:
Lots of fixes, works with OpenOffice 4.1.8 on Windows and MacOS :D
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
psilocybe
Posts: 107
Joined: Thu Jun 15, 2017 5:33 am

Re: HsqlDB 2.51 in Base

Post by 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.
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
Post Reply