[Solved] Install split_hsqldb_2.3.2_wizard.odb fails

[bartjeb2]

I have an Open Office (4.1.8) on my Imac (macOS Big Sur 11.1) where I want to use Base.
I want to use a trigger for this.
When I try to run this at Tools / SQL I get an error.
When I look this up it appears that the trigger cannot be used in the standard HSQL that is behind OpenOffice.
This is 1.8.0.10 and should be at least 2.x.

As a solution, it was recommended to use the split_hsqldb_2.3.2_wizard.odb (v ... t = 61183% 3E), using not only the higher version but also a split database.
I also have an old PC with Windows 10 Home and I put the windows version there and yes, I can now create a trigger.
However, if I put the macOS version on my Imac and according to the documentation (first in OpenOffice at options-OpenOffice-Security-Macro security set to medium, at options-OpenOffice-Java the Class path checked (empty), the .odb file in put a new directory) try to install (with Macros enabled at the popup) the installation hangs, the 'driver' directory is created (incl hsqldb.jar and sqltool.jar) but no 'database' directory yet, I get an OpenOffice Base screen with the forms tab open, I can click what I want but there is no response, I just can get the application stopped with 'force stop'.

When I try to look this up (where would we be without google) I find on the English OpenOffice forum
(https://forum-test.openoffice.org/en/fo ... 13 & t = 74751) someone with the same problem (only on Kubuntu) but no solution. (it does say that a user profile reset could sometimes help, but this didn't help in my case.

So anyone has any idea on how to get the split_hsqldb_2.3.2_wizard.odb running?
I'm trying this on my Imac (macOS Big Sur 11.1) with Open Office (4.1.8).

Thanks in advance!

[bartjeb2]

after advice from the dutch forum I installed OpenOffice 4.1.9, same result.

[Villeroy]

You still have a version with embedded HSQL 1.8? viewtopic.php?f=21&t=86071
If not you can either reconnect your extracted DB with the same macro or use FreeHSQLDB

[Villeroy]

If you are familiar with macro code, notice the minimal version viewtopic.php?f=21&t=77543#p493018 which runs a few lines of Basic code after you adjusted the path settings.

All these solutions, the split_hsql_wizard, my Python extractor and FreeHSQL assume that no HSQL driver has been added to the class path settings in the office's Java settings. The path to the hsqldb.jar driver is saved within the document.

[bartjeb2]

I started this question on the Dutch forum in the beginners section, as I just started using OpenOffice. there I was told that I had better ask the question here.
I don't quite understand what to do now, I do have a version with HSQL 1.8, but no experience with macro code, the class path settings are unchanged.
can you explain the steps to me?

[Villeroy]

1) Go to menu:Tools>Options... Security > [Macro Security...] and set the highest security level. On the second tab, add some trusted directory where documents are allowed to execute embedded code. This must not be your Downloads directory but some directory in your personal documents folder would be fine.
2) Create some directory where you save a copy of your database with the embedded HSQL. It may be within the trusted directory or not as long as it does not contain any macros.
3) Within this directory with your database document, you create a subdirectory named "driver". In this directory you put a copy of your HSQL driver and name it hsqldb.jar. It can be any hsqldb.jar until version 2.4.x. The latest versions since HSQL 2.5 can not convert old databases on the fly anymore.
4) Download my Writer document download/file.php?id=35569 from topic topic [Python] Macro to extract and reconnect embedded HSQLDB. Put it anywhere in your trusted directory. It contains a macro that installs another macro for you. Open it and push the button to do so. You should get some success message.
5) Now open the database document in your database directory created in step 2. Its status bar should look like this:

6) Call the installed macro via menu:Tools>Macros>Run ... user > pyDBA > ExtractHSQL > Main and click the [Run] button. You should get a success message that the database has been extracted to subdirectory "database" and connected to the hsqldb.jar you have created in step 3.
The status bar should have changed to something like
and your tables, queries, forms and reports should work as expected. Now there is another folder "database" with a set of files named after your odb document. This is the extracted database.
7) Save the modified database document with its new connection settings.
--------------------------------------------------------------
Any next database document with embedded HSQL requires steps 2, 5 and 6 (less than a minute).
If you ever rename or move your database document folder with subfolders "database" and "driver", repeat steps 5 and 6. The macro detects that you may not want to extract the embedded database but reconnect the document to the existing database in the same folder hierarchy.
If you want to upgrade from HSQL 2.3 or 2.4 to version 2.5 (the one which can not handle 1.8 anymore), just close everything and replace the old hsqldb.jar with a new one.
If you ever feel inclined to tear apart the folder structure, my "FreeHSQLDB" macro suite provides a dialog to connect some Base document with some set of HSQL files and some hsqldb.jar.
Your document still contains the old embedded HSQLDB. If you are sure that your new database works fine, pyDBA > ExtractHSQL > Remove_Embedded to get a smaller document file.
For any HSQL since version 2.0 the following query reports the version in use:

Code: Select all

SELECT DATABASE_VERSION( ) AS "HSQL_VERSION" FROM "INFORMATION_SCHEMA"."SYSTEM_TABLES" WHERE "TABLE_NAME" = 'ROUTINES'

[bartjeb2]

2 questions :
at point 2 I have to "save a copy of my database with the embedded HSQL" Do you mean only the odb-file?
at point 3 I have to "put a copy of my HSQL driver in "driver"subdirectory and name it hsqldb.jar. It can be any hsqldb.jar until version 2.4.x" Do you mean the hsqldb.jar that was installed in the driver"subdirectory by the wizard (2.3.2)
?

[Villeroy]

Yes, the odb file. Always keep the original and mess with a copy of it. I dont see your folder hierarchy and I know nothing about MacOS but you should have:

/homeDir/SomeDir/YourDatabase.odb
and
/homeDir/SomeDir/driver/hsqldb.jar (it should origin from any HSQL package smaller than 2.5.0)
Finally you get an additional database directory with
/homeDir/SomeDir/database/YourDatabase.script YourDatabase.properties YourDatabase.data (and others starting with "YourDatabase.".

If there are any existing files database/YourDatabase.* the macro will ask you to use the existing set of files or replace them with the ones that are still embedded in the document. Let's replace them unless you already added new data to the existing files, but in this case you would not have the connection problem. There is also a [Cancel] option before the macro actually does anything.
When you access the extracted database for the first time with the driver/hsqldb.jar in place, the extracted 1.8 database will be converted on the fly into a 2.3 database. The most recent driver versions since 2.5 do not do that anymore.

[Villeroy]

Alternatively, if you already have extracted the database files from the odb file:
Install the extension download/file.php?id=31906 (menu:Tools>Extensions... [Add...])
Run [My Macros] -> FreeHSQLDB -> FreeHSQLDB -> Main with your database in the active window..
A dialog pops up. If you get an error message instead of a dialog, close everything and try again (I never found out how to fix that).
In the dialog point to the right hsqldb.jar and to the right *.script file of your extracted database.

[bartjeb2]

thanks to your patience I have now been able to do everything and finally create the trigger, thank you very much

[Villeroy]

Which method succeeded at last? The Python macro extracting and connecting to a fixed hierarchy of folders and files? Or the FreeHSQLDB dialog where you specify the location of an already extracted database?

[bartjeb2]

extracting the macro and connecting to a fixed hierarchy of folders and files did it.