Old split hsqldb won't connect in new Linux Mint 20.2 and LO 6.4.7.2

[echo8hink]

Wow! New board look. Wow!

I recently upgraded my lenovo T510 laptop to linux mint 20.2. It came with LibreOffice 6.4.7.2 installed. I transferred a Base split hsqldb database with its directory structure onto the hard drive under my new home directory and I cannot get it to access the data tables. The split database was created with a macro application that I downloaded some years ago from the OOo forum, if I remember correctly. It split an embedded hsqldb out into a .odb and a couple of subdirs for /database and /driver. All worked well up through mint 19.0 and LO 5.4.7.1.

Now, I can load the .odb which contains the split launch macros and many more macros I wrote to control the forms, queries, reports, etc., I can see the forms, but there are no tables loaded and error messages pop up.

The first error message occurs after the initialization macro tries to load Access2Base:
“Error #1 (An exception occurred) occurred at line 1302 in OpenConnection”

If I click past this one the base screen finishes loading and populates with the form names.
If I try to load a form the following error message box appears:
The connection to the data source “MyRecipesSplit” could not be established…
error in script file line: 1 Unexpected token UNIQUE, requires COLLATION in statement [SET DATABASE UNIQUE]

If I run the java hsqldb database manager in a terminal, the database will connect and all tables load, so I know they are still there and the hsqldb.jar in the /driver directory works.

I thought something in the old content.xml in the .odb file might be amiss and when I inspected it, I found the directory name for the “/home” had changed. The old linux box used “/lenovo” and the new one uses “/dave” for my login name. This name is used twice in content.xml and I edited it to reflect the new name.

I still cannot get base to load and run the database app correctly. I have read something in my searches to fix this that says the new LO will not allow any hsqldb other than 1.8 to be loaded. I believe the .jar file used for this split db is 2.2 or 2.4? which may be part of the problem…

Any help resolving this problem would be appreciated.
-hink

[Ratslinger]

Hello,

With split databases, at least initially, it is necessary to run a setup macro to insure install of the HSQLDB file and that all directories are correct. If wanted, this is run each time the Base file is opened in case it has been moved:

Now if not going to be moved this is no longer necessary and the event can be removed. This may be what happened in your case. You can simply run the macro in the base file in “Standard->Embedded” run “Setup”. This should establish the correct directories.

[RobertG]

Note: The last version of the extension has been created 2017-07-06 as I can see here: HSQLDB Extension. The version from LO this time has been LO 5.2.

I would prefer to connect to an external HSQLDB by including the path to the hsqldb.jar in the *.odb-file. Could be the whole database is there but the external hsqldb.jar is missing. See also here: Database only opens in save mode

[Ratslinger]

Likely the jar file is there but the macro to update its’ location was not run. See also for split DB’s → [Wizard] Create a new ‘split’ HSQL 2.x database

[echo8hink]

( Note I am not sure how to reply to this board version… )
The Open Document macro on mine is the same as in the screen shot. Standard.Embedded.Setup

The connection string at the bottom of the screen shows JDBC and the connection string. I used Villeroy’s extension. I tried the code from the Oo forum link and “hard-coded” the filename links and it looks like I get a connection. However, when I click on the “Tables” icon or try to open a form I am still getting the error I listed above:

The connection to the data source “MyRecipesSplit” could not be established…
error in script file line: 1 Unexpected token UNIQUE, requires COLLATION in statement [SET DATABASE UNIQUE]

I have even reloaded the data directory files from the original working backup and started over. I disabled my own macro code that opened a switchboard form and still no connection to the tables.

[Ratslinger]

Switched over to Mint 20 and have fairly clean system there with the distro LO version as you have. Using a copy of the split file which worked on Ubuntu 20. It failed on Mint 20 as happened to you. See nothing incorrect in files.

Loaded out LO v 7.1.5.2 for a test and all worked without any modifications. There is a problem with the Mint 20 distro version.

[anon73440385]

@Ratslinger

There is a problem with the Mint 20 distro version.

May be it is another problem adding to the list of known problems (incomplete PDF exports, printing issues, weird UI behavior,…) on all Ubuntu 20.04 based systems using distribution packages of LibreOffice after initial installation. My strong recommendation, if the following conditions are met

• Ubuntu 20.04 based system/distribution
• initial installation (which as of today installs LibreOffice 6.4.7.2),

perform the following procedure (regardless of having an actual problem):

• dpkg --list | awk '/ii/&&/libreoffice/{print $2}' | sudo xargs apt purge --yes
• sudo apt autoremove --yes
• sudo apt install libreoffice libreoffice-gtk3 libreoffice-sdbc-hsqldb libreoffice-help-en-us --yes

[Azura]

I can confirm that switching to LO 7.1.5.2 also fixed the same problem for me. I’ll update that other thread.

[echo8hink]

I tried LO 7.1.5.2 and it still fails on my Mint 20.2, but, with a different error message. All of the opening macros appear to complete ok. The message box I pop up to tell that Access2Base loads appears now and the opening switchboard (form) opens. I have a routine that gets the HSQLDB version and it displays 2.3.2 which is correct for my hsqldb.jar file in the split db. The bottom of the screen status line shows JDBC and the hsql:file string. However, when I try to use a form that needs data I get the following error messages:

The data content could not be loaded. /home/buildslave/source/libo-core/connectivity/source/commontools/dbtools.cxx:751

SQL Status: HY000

There exists no table named “Recipes”. /home/buildslave/source/libo-core/connectivity/source/commontools/dbexception.cxx:399

I’ve never seen anything like those before. I wonder if I should try a newer hsqldb.jar and sqltool.jar in the driver folder?

I still can’t believe the split database implementation is broken for me. There seemed to be a lot of people using it to beat the problems associated with the embedded HSQLDB shortcomings. I will continue to try to get mine working again. Thanks to all for the suggestions.

[Ratslinger]

It appears you have a connection to the data - Opening form starts and you can read the DB version. Open the Base file and see if you can view data in the tables. If so you have a working Base file and now a different problem - Switchboard setup. Know little of that.

[echo8hink]

I finally got a connection to tables!!!

I started out experimenting with an older version of my database that was still in the “embedded” version of HSQLDB. I used the old splitter wizard and created a connected db that would let me build tables. I opened a second iteration of Base and copy/pasted all the tables, queries, forms, etc. over and uses the table relations tool and all went well. I now had a working split database using the 2.3.2 hsqldb.jar.

However the first split database I had tried to use still wouldn’t let me see tables. I got two file managers running side-by-side and started checking for differences. I found two files in the /database folder in the non-working app that were not in the working app. They were:
mydb.tmp.data and mydb.tmp.properties. I had mydb.data and mydb.properties in there also. I deleted the mydb.tmp.*, closed and restarted LO, opened the split db and clicked on the tables button and I now have tables. I think those extra files came from a messed up attempt by me to use the java utilities…

I bounced around the forms and had a couple of macro errors. It turned out that in all my other messing around, I had forgotten to register the database. I have some code that will not run correctly until the base file is registered.

For now, all seems to be working fine again. Thanks for all of your(everyone’s) help. Now, how do we mark this “solved”.

-hink

[Ratslinger]

@echo8hink

Now, how do we mark this “solved”.

See Bar below a question

and How do I mark an answer as solved?

[Villeroy]

https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=77543
Install the extension. It contains only Basic code.
Open your odb document.
Run FreeHSQLDB>FreeHSQLDB>Main
Use the file picker buttons to spot the hsqldb.jar and the *.script file.
The package contains an auto-installer module which can be wrapped in your odb in order to make it portable.

If you scroll down the forum topic you will find a little script where you can hard code the 2 paths.