JDBC connections for OpenOffice HSQLDB

Posted on by Rob Leave a comment

How to connect to and query an OpenOffice database with JDBC

The internet completely failed to provide a clear answer to this question – so having worked it out and on the off chance that someone else needs to know the answer; here’s how to run queries from SQuirrel SQL against your ODB data:

  1. Create your new database in Open Office Base (or equivalent):
    • Call it anything you like but in this post I call mine ‘ManuscriptLibrary‘
    • Add a table to the database, this is the table you want to query – mine is called ‘Manuscript‘
    • Save and Close the Open Office Base file
  2. Extract the database files from the ODB file:
    • Open the file ‘ManuscriptLibrary.odb’ in your favourite zip tool (7-Zip, WinZip .. whatever).
    • Select the database folder and copy the four files (properties, backup, data, script) out to a local directory  such as C:\Path\To\Extracted\Files\
  3. Rename the files using your database name (backup is not needed):
    • properties = ManuscriptLibrary.properties
    • data = ManuscriptLibrary.data
    • script = ManuscriptLibrary.script
  4. Open Squirrel SQL:
    • Add a new Alias with the HSQLSB Standalone driver (you will need to add the hsqldb jar to enable this driver)
    • Set the URL to: jdbc:hsqldb:file:///C:/Path/To/Extracted/Files/ManuscriptLibrary (ManuscriptLibrary is the name of the DB and the name you used in step 3 above)
    • Set the User Name: sa (Do not set the Password – it’s empty)
    • Hit OK  (note the driver will connect even if you get the filenames wrong – so Test is spectacularly unhelpful)
  5. Connect to the DB:
    • Double click on the saved Alias or hit Connect
    • In the Objects tab, expand PUBLIC > PUBLIC > TABLE – at this point you should see the table you created in step 1 (‘Manuscript‘), you may need to hit refresh at this point
    • Select SQL tab and run the query: select * from “Manuscript”; (note the table name is in double quotes). You should get the contents of the table. If you get error codes 42501 or -5501 this means that either – the ODB files weren’t found or the table name is wrong.

And now no doubt you want to manipulate the data and put it back into an ODB file that can be edited in Open Office – that’s a topic for another post.