Help with Split HSQLDB - existing LibreOffice Base project

[LeslieM]

Can anyone point me in the right direction? My goal is to update my existing Based application to use the current version of HSQLDB. I want my tables, queries, forms, etc. to all come across into the new DB version. I tried following the instructions viewtopic.php?f=83&t=65980 and using step 3X, but when I created the new macro it seemed to be read-only - could not paste in the macro code.

Alternatively - if there is an easy way to change Base to reference the database via JDBC, and a way I can migrate the database folder inside the ODB to the server I could try that approach. I searched on the forum and did not see a guide for taking an existing embedded HSQLDB and moving it to be client/server based.

Thanks in advance for any advice you might be able to provide!

[Nick N,]

Hi Leslie,
I beg your pardon!
Thus i am a newbie in IT science, i would like to try to give you an hint, i.e. does your Operating System contain full pre-loaded software?

At BASE launch time, I usually got two options: by the way HSQLDB or FIREBIRD. But no more with the current one.

Try to verify for yourself.

See attached screenshot.

Hope it would help.

Regards

Nick

[robleyd]

At BASE launch time, I usually got two options: by the way HSQLDB or FIREFOX

I think you mean HSqldb or Firebird

Creating new Firebird databases has been moved to an experimental feature since LO 6.4.3 You can turn on experimental features via Tools>Options>LibreOffice>Advanced from the menu.

Place a check mark next to “Enable experimental features”.

However, I don't think your response is relevant to the question posed by LeslieM

[Nick N,]

@ Robleyd

Thanks for rectifying my draft. Are you certain it wouldn't help somehow?

Have a nice week...

Nick

[Villeroy]

Start with an embedded HSQLDB. When you've set up all your tables, relations and indices follow this approach to extract the embedded database: [Python] Macro to extract and reconnect embedded HSQLDB

If you add a subfolder "driver" with a database driver "hsqldb.jar", this driver will be used. Otherwise, your database will be driven by the same hsqldb.jar as the embedded databases. This does not work with hsqldb later than 2.4.3 Version 2.5. is not compatible with the very old driver of version 1.8 that is shipped with your office suite. After upgrading to 2.4.3 you may upgrade to the most recent version. I stick to 2.4.3 which works very well with LibreOffice and does not lack any features that are important for me.

[UnklDonald418]

Be aware some compatibility issues (usually minor) have been reported when using Base with HSQL versions 2.5.0 and above. I currently have two Base databases using HSQL 2.7.0 and haven't run into any problems ... so far.

If you think you want to try a more recent version of HSQL than 2.4.3
Be sure you have a current backup of all the files in your split database directory.
With the old version open, at Tools>SQL execute the command

Code: Select all

SHUTDOWN SCRIPT

Close the database.
Replace the hsqldb.jar file in the driver sub-directory with the new version.
Open the database and the data should automatically load from the script file into a new mydb.data file compatible with the new HSQL version.
Use the same procedure if you decide to go back to an earlier version of HSQL

[Nick N,]

@ Leslie M

you wrote:

Alternatively - if there is an easy way to change Base to reference the database via JDBC, and a way I can migrate the database folder inside the ODB to the server I could try that approach. I searched on the forum and did not see a guide for taking an existing embedded HSQLDB and moving it to be client/server based.

I connected to MySql via JDBC /localhost.

By now i can use LO as a HSQLDB interface with the benefit of wider SQL commands range. E.G. GROUP_CONCAT.

Regards

Nick N

[Villeroy]

Things like GROUP_CONCAT, DATEADD, regex and array functions are also part of HSQL2.

[Nick N,]

I'll try. Thank you.

[Villeroy]

Simply copy your document with embedded HSQL in a dedicated directory, add a subdirectory "driver" with a copy of hsqldb.jar of version 2.4.3 (last version able to upgrade from version 1.8). Finally, open the document and run my Python macro. The macro can be installed with a single click on a button. The HSQL documentation is shipped with the downloaded archive from where you extracted the hsqldb.jar.

[Nick N,]

Please forgive me if I've tried different solution on the web. I shouldn't say different but "alike" or "ready made" instead.

My aim was only to show to a french fellow that With A Little Help, I would have succeeded.

Consider my attachement.

Have a nice time!