I had developed an application that stored some metrics in a HyperSQL database for offline processing and reporting with OpenOffice.
It seemed simple enough – OpenOffice would connect to the database via JDBC, with the JDBC URL and driver class..trouble was, no matter what I did, the driver class would not be recognised!
After endlessly searching on the web for a solution, I realised the piece of the puzzle I was missing – the version of the hsqldb jar file. The version 2.0.0 jar appears to be incompatible with OpenOffice 3.3 – Reverting back to 220.127.116.11 solves the problem, and the JDBC driver class is recognised.
So I’ll publish the process in full here, just in case anyone else runs into the same problem!
- OpenOffice 3.3.0 (The latest stable at the time of writing)
- An existing hsql database (created using hsqldb 18.104.22.168 jars)
Despite OpenOffice using hsqldb for it’s internal databases, the driver is not natively available for JDBC.
Preferences > OpenOffice.org > Java, ensure that a JRE is registered. Open up the
classpath dialog, and add an archive entry for the hsqldb jar
This is basically where I had been running into trouble. My application originally used hsqldb version 2.0.0 with Hibernate. Despite the driver class being the same (
org.hsqldb.jdbcDriver), it seems OpenOffice would not recognise this version of hsqldb.
Connect OpenOffice to Database
Using OpenOffice’s Database, select
Connect to an existing database (using JDBC).
Enter the JDBC Connection details:
- URL: The JDBC URL minus the ‘jdbc:’ – should be of the form
hsqldb:/path/to/database– also append
;default_schema=trueto the end.
- JDBC driver class is:
Note that ‘database’ is the collective database name for the files making up the hsqldb instance (e.g. database.log, database.properties, database.script).
Click ‘Next, enter the default username of
sa (No password required).
Accept the defaults for registering the database, choose a location to save and you’re done!
If anyone has an explanation of the OpenOffice incompatibility, please leave a comment!