How to set up the jdbc driver to connect to hsqldb from libreoffice?

Question

I am trying to "split" a LibreOffice .odb file into a HSQL database and an OpenOffice document containing forms and macros. I am trying to follow the instructions from this thread:

Within a few minutes you can convert your embedded HSQLDB to a stand-alone HSQLDB which is just a very fine database engine.

1) Download and extract the current version from http://hsqldb.org/ and point the Java class path in Tools->Options->Java to the new hsqldb.jar

2) Extract the database folder from your embedded database and rename the files data, properties, script to name.data, name.properties, name.script, where "name." is an arbitrary name prefix.

3) Connect a Base document to an existing JDBC database such as jdbc:hsqldb:file:/home/chenier/hsqldb/name;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false (again, "name" refers to your own file name prefix). This local single-user connection gives you much more than the embedded HSQLDB.

4) Copy queries, forms and reports from the old database over to the new one.

The wizard presents me with a window expecting two inputs: a "Datasource URL" and a "JDBC driver class".

As far as I can tell, the tutorial above only tells me what to put into the Datasource URL. As for the JDBC driver class, I have no idea what to write into this field. I tried the fully-qualified name of the Java class, org.hsqldb.jdbc.JDBCDriver as given in the HSQLDB documentation. When that failed, I tried the physical path /var/lib/hsqldb/lib/hsqldb.jar (although that should have been unnecessary, because first I pointed to this path as described under 1 and then restarted LibreOffice). In both cases, "Test class" failed with the message "The JDBC driver could not be loaded". OpenOffice's documentation doesn't say anything sensible about the field, it was something like "enter the JDBC driver in this box".

Any ideas what I should enter there to get the connection working?

Answer 1

The path to the hsqldb.jar file is set this way.

On the main Libreoffice menu, choose Tools/Options/Libreoffice and look for Java or Advanced. Click the Class Path button, and then Add Archive. Navigate to the location where the hsqldb.jar file is (apparently, /var/lib/hsqldb/lib/hsqldb.jar, in your case)

On my Debian system /usr/share/java/hsqldb.jar is a link to the actual file /usr/share/java/hsqldb-1.8.0.10.jar. Both belong to root.

Step 3 is what you have illustrated with your screenshot. Put the properly modified (for your database) long command "jdbc:hsqldb:file:/home/chenier/hsqldb/name;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false" in the datasource url, and put "org.hsqldb.jdbcDriver" (without the quotes in both cases) in the JDBC Driver class box. Test the class. The next part asks for a user name; use "SA" (without quotes), leave the password required unchecked, and test the connection.