The driver class org.hsqldb.jdbcDriver could not be loaded

[misitu]

Apologies, there are many posts on this topic but none hit the button exactly.

I've created a server database in AOO4.1.6 on HSQLDB2.5.0
Win10 Home Prem

I've a heap of these running my accounts system with live & various test regimes so the technicalities are fine but on this occasion there is a widget missing.

In order to keep out of the way of the accounts I located this in a separate folder and edited the start and stop and everything else files appropriately.

The database server starts and stops OK using standard HSQL stuff and I can access it using both sqltool, SQL Workbench/J, and DBeaver. I can even get AOO to display the front page but when I click TABLES here is that old chestnut about being unable to load the driver.

I've had a good poke around inside the .odb file and also the other places that might have a clue, like the xml files inside it. But nothing seems to be out of order!

So it is clearly something really simple but for all this effort I cannot think of anything else to try. If someone is able to point me in the right direction, even to say which files I can upload here, I'll be most grateful.

I've been saving the daily covid 19 stats from our Min of Health here and it's about time to take them off the spreadsheet and automate things a bit better, so it's fairly urgent.

Maybe I'll even think of yet another useless thing to try in the meantime ..

Thanks in advance
David D

[Villeroy]

OpenOffice for Windows is a 32-bit application. It can not interface with a 64-bit Java. Download a 32-bit Java and point OpenOffice to it. Usually, it can be found in C:\Program Files(x86)\
https://libreoffice.org/ is availlable for Windows as 64-bit software.

[misitu]

Thanks, Villeroy. Of course, you are correct.
I was trying to save time by cloning an existing. Wasting time, in the end.

If I can get a break from ongoing fixes, etc., I may well try LO. Previously it seemed that it has quicker support; although I can't complain, you are on the ball with this one.

Saludos
David

[Villeroy]

This is the support forum for both OpenOffice and LibreOffice.

[misitu]

OpenOffice for Windows is a 32-bit application. It can not interface with a 64-bit Java. Download a 32-bit Java and point OpenOffice to it. Usually, it can be found in C:\Program Files(x86)\
https://libreoffice.org/ is availlable for Windows as 64-bit software.

=> see executive summary at end of post. the rest is just trying to diagnose cause of problem...

But!
The shortcuts that start my hsqldb server, that stop it, and that execute sqltool, all refer to

Code: Select all

"C:\Program Files (x86)\Java\jre1.8.0_211\bin\java.exe" -cp driver\hsqldb.jar org.hsqldb.server.Server -port 9013 -database.0 file:database\CHINESE_BAT_FLU -dbname.0 CHINESE_BAT_FLU

So, it is not the Java. And that works for my production databases.

Bit annoying this, same road block. Maybe I should stay in Cuarentana.

I can access the server db using sqltool. I can open it with OpenOffice. But when I hit the Tables button the message "the driver class could not be loaded" appears. Like the old friend asking for credit, unwelcome.

I did try various permutations on org.hsqldb.jdbcDriver but none of those worked.

I also tried digging into the manifest and other elements of the compressed odb but there was absolutely nothing that could be changed that worked.

Clearly the instance of AOO that is sitting here is not able to map the driver!

Unlikely to be the driver because sqltool works.

Any clues anyone? I really need to knock this on the head.

Inside hsqldb.jar is: org => hsqldb => jdbc => JDBCDriver.class
I could guess that AOO is looking for the driver with lowercase jdbc ...
I also tried changing the driver class in Database Properties Connection Settings to org.hsqldb.jdbc.JDBCDriver (with the extra hierarchy) but that turned out just as stupid.

My sqltool connection string has Driver: org.hsqldb.jdbc.JDBCDriver
and *that* works.

¿¿??

Well, I replaced org.hsqldb.jdbcDriver in the content segment of the compressed odb with the driver name as above in sqltool and that did not work.
OK, backtracking somewhat:
C:\Users\Accounts>java -version
java version "1.8.0_211"
Java(TM) SE Runtime Environment (build 1.8.0_211-b12)
Java HotSpot(TM) 64-Bit Server VM (build 25.211-b12, mixed mode)

OK Villeroy, I take your point (32 and 64 bit). But I am so so puzzled that the other services (OpenOffice hsqldb client-server databases) all just work fine.

¡¡OH!!

I decided to try by setting a classpath to the hsqldb.jar driver in the current folder. And now AOO opens the tables.



Yes! I KNOW this is strictly forbidden!! So, what are my options now?

I guess this has xxxxed up the rest of my system, my lovely accounting system.

EXECUTIVE SUMMARY
Creating a new client-server (localhost) OpenOffice Base application demands the hsqldb driver path to be added to the AOO Java Classpath list (which was empty before). Otherwise jdbc driver not found.

¿how on earth has nobody else hit this?

Cheers
David D
And the Javas registered in OpenOffice tools-options all say location C:\Program Files (x86)\Java\

Just to make sure, I changed the "start in" directory in the shortcut that starts AOO to point to windows system32 and removed the classpath and AOO starts but fails to show tables.

So - it does appear to be ABSOLUTELY NECESSARY in this case to specify a global classpath for AOO.

But I still do not understand why the remaining apps worked OK.

XXXXXXX

[Villeroy]

OpenOffice is not a database. It is a mere front-end to work with databases. HSQL is a database backend in its own right. It works independently from OpenOffice. Your server works fine with any suitable version of Java in the total absence of any office suite.
Since version 2.0 of 2005 OpenOffice comes with a special database setup which makes it possible to wrap a HSQLDB into a Base document, which makes it look as if the whole thing were a database in one file. As a matter of fact, the Base document is a simple zip archive, OpenOffice extracts the embedded HSQLDB to a temporary directory and connects the Base document with that "installation". Every time you close a data view, the whole database is wrapped back into the Base document. The driver version has never been updated beyond HSQL 1.8.10. This setup is fragile but good enough for simple purposes as long as you follow some backup strategy. The status bar of such a connection reads "embedded HSQLDB".

I decided to try by setting a classpath to the hsqldb.jar driver in the current folder. And now AOO opens the tables.

Now you replaced the built-in HSQL 1.8 driver with a HSQL 2 driver by adding your hsqldb.jar to the global OpenOffice registration. This should work as long as the Java version used by OpenOffice runs on 32-bit (C:\Program Files(x86)\...). However, you can not work with ebedded HSQL anymore. You can open embedded HSQL only once. It will be converted on the fly and after the conversion you can not access it anymore. If this happened to you, you can extract the database and use it as a stand-alone database. As far as I know, there is no way back to HSQL 1.8 except for replaying a backup of the old database document. I guess, the status bar of your database reads like JDBC hsqldb:hsql://ServerName/database_name.

On this forum we recommend to register the hsqldb.jar for the individual database document rather than globally in the Java options of the office suite.
In order to do so, you first de-register your hsqldb.jar from the global options and restart the office.
Then install my little macro suite FreeHSQLDB v.0.3, open your database, run Tools>Macros>Run>FreeHSQLDB>FreeHSQLDB>Main. Set the the hsqldb driver path in that dialog and leave alone the HSQL backend since you have a working server connection. Setting the backend with that tool would result in a local connection to the database files as in JDBC hsqldb:file://C:\Path\database_name (which may be fine for testing on a local machine or for single-user access on a network drive).
OpenOffice provides no GUI to set the driver path for a database. You have to do this by means of XML editing or using a macro script.

[RPG]

Villeroy thank you for this explanation and the short macro code you did give in the FreeHSQLDB thread. The macro did work for me and I need only to change JavaDriverClassPath. I do use HSQLDB 2.x placed by openSUSE.
I could point JavaDriverClassPath to the file what was placed by openSUSE and it worked. I hoped and do expect that when I update openSUSE it will continue to work. For your information also: It did work for ApacheOffice 4.2.0 and LibreOffice.

[Villeroy]

Thank you for testing the little macro. It has only a few lines of working code and lots of comment lines. I hope the comments make it easier to use a HSQL2 driver and more comprehensible to distinguish between file and server as target of a HSQL connection.

If your HSQL2 package is part of your OpenSUSE distribution, the path of hsqldb.jar should be stable. For anyone downloading the HSQL package from hsqldb.org it does not matter where you extract the zip archive as long as it is readable. I put such things in /usr/local/lib/ with a stable symlink /usr/local/lib/hsqldb/ pointing to /usr/local/lib/hsqldb-2.4.1/ or whatever current version I prefer to use right now. The pre-release versions of HSQL are single .jar files, currently hsqldb_6122.jar of 2020-05-21.

In my home directory I have a directory ~/hsql/ with database documents, database/.and driver/ subdirectories.
driver/ has a symlink to /var/local/lib/hsqldb/lib/hsqldb.jar
database/ keeps all the local databases for local file connections.
Every set of database files with the same name prefix belongs to a database document in the parent directory having the same prefix.
Whenever I want to convert any embedded HSQLDB, I put it into my hsql directory, open the document and run my extraction macro viewtopic.php?f=21&t=86071 The python code extracts DBName.script, DBName.properties, DBName.data etc. out of DBName.odb, connects the odb with the equally named file set and with driver/hsqldb.jar.
In most cases (but not all), the result is a fully functional HSQL2 database with all the queries, forms and reports working in the same way as they used to work with the embedded database.