HsqlDB Embedded come back

[psilocybe]

Hi all and Happy New Year,

I announce the come back of HsqlDB in integrated mode in LibreOffice / OpenOffice.

It's even more than a come back since now we can manage:

• the rights of users and roles (groups).

• Schemas.

• lots of other things to discover.

This with the version of HsqlDB of your choice and all in a single odb file.

All this thanks to two links:
HsqlDBembeddedOOo
jdbcDriverOOo

[charlie.it]

Interesting ... but it doesn't work in Mac environment. The Driver is not recognized, both in OpenOffice and in LibreOffice.

[psilocybe]

it's disappointing, unfortunately it's one of the few platforms that I can't test.

Can you give me the OS version?
Are you sure you have Java 11 installed?

[charlie.it]

I forgot to do that…
Now however Java 11.0.16.1 is installed correctly, but in LibreOffice 7.2.6.2 I get this error installing the Driver extension:

Schermata 2023-01-13 alle 18.32.18.png (25.5 KiB) Viewed 4292 times

My operating system is that of my Firma, rather old ...

[charlie.it]

With OpenOffice 4.1.13, something happens that I don't understand. If I follow the "Create new database" procedure I get the error:

Code: Select all

 The connection to the data source "New database" could not be established. Url: 'sbdc:embedded: hsqldb' cannot be loaded. Please check syntax

But if instead I choose

Code: Select all

Connect to an existing database > HsqlDB Driver

and then compile

Code: Select all

datasorce URL = //Users/mac/Desktop

and then

Code: Select all

User name = mac > Finish

a database opens with already filled tables. Where they come from?

[psilocybe]

It seems that with OpenOffice the HsqlDBembeddedOOo extension is not usable as is.
I need to do more investigation to find out where it came from.

a database opens with already filled tables. Where they come from?

It looks like a new database called: New database4.odb has been created with the driver HsqlDB Driver provided by the jdbcDriverOOo extension.
The location of this database is: /Users/mac/Desktop
The user who is connected is: mac (it is probably the account that was provided during the first connection and creation of the database)

Tables already populated are HsqlDB system tables, jdbcDriverOOo now showing all tables including system tables, as they can be filtered in Base through the menu; Tools -> Table Filter

[psilocybe]

The last update of HsqlDBembeddedOOo now allows the use of OpenOffice, with some limitations:

• The OpenOffice API does not allow the update of the odb file when it is closed (unlike the LibreOffice API) so the database is kept in split mode.

• This means that odb files are not portable like LibreOffice.

I will test on Mac as soon as the opportunity arises.

[charlie.it]

L'ultimo aggiornamento di HsqlDBembeddedOOo consente ora l'utilizzo di OpenOffice

I confirm, it works also in macOS.

L'API di OpenOffice non consente l'aggiornamento del file odb quando è chiuso (a differenza dell'API di LibreOffice) quindi il database viene mantenuto in modalità divisa.

Ciò significa che i file odb non sono portabili come LibreOffice.

I didn't quite understand this part but, really, I still don't understand what it consists HsqlDB embedded
Where can i find documentation?

[psilocybe]

I didn't quite understand this part but, really, I still don't understand what it consists HsqlDB embedded
Where can i find documentation?

I don't know where to find documentation but to make it simple, we can say:

• In a normal database like jdbcDriverOOo all the files composing the database are in the directory corresponding to the path provided during the connection.

• In an embedded database like HsqlDBembeddedOOo all the files composing the database are in the odb file (in reality they are extracted from the odb file when connecting to the database then reintegrated when closing the odb file). This makes it easy to transport the database since everything is contained in a single odb file (in fact it's a zip file).

[charlie.it]

In un database embedded come HsqlDBembeddedOOo tutti i file che compongono il database sono nel file odb (in realtà vengono estratti dal file odb durante la connessione al database e poi reintegrati alla chiusura del file odb). Ciò facilita il trasporto del database poiché tutto è contenuto in un unico file odb (in realtà è un file zip).

But this is already the case for normal embedded HSQL 1.8. Or am I wrong?

[psilocybe]

But this is already the case for normal embedded HSQL 1.8. Or am I wrong?

You are right. But embedded HSQL 1.8. suffers from the limitations:

• LibreOffice roadmap is to migrate HsqlDB Embedded 1.8 to Firebird.

• HsqlDB version 1.8 is now over 10 years old.

HsqlDBembeddedOOo attempts to overcome these limitations.

[psilocybe]

The last update of HsqlDBembeddedOOo now allows the full support of OpenOffice...
Now all schemas are displayed by default (including INFORMATION_SCHEMA and SYSTEM_LOBS). They can be filtered in Base through the menu: Tools -> Table Filter

In the latest update, the driver is a true singleton as the LibreOffice/OpenOffice UNO API demands and this makes its second load almost instantaneous...