JDBC connections for OpenOffice HSQLDB

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.


New Music Book: Doon the Redewater

Over the last five years I’ve been helping out local mouth-organ player Don Clegg in transcribing all of his new compositions and now we’ve gathered them all together and published them in a proper book. “Doon the Redewater” is available at Core Music in Hexham and online

Doon the Redewater

Many of Don’s tunes are in the key of A, but he’s always happy to transpose for Northumbrian pipes – so we’ve also got a piper’s manuscript to accompany the book with all of the tunes arranged for NSP.

Don is also a wonderful dialect poet and storyteller and I’m currently compiling a book of poems & tales which should be available before Christmas.

How many keys do I need on my pipes?

Here’s the second of the decision charts for new pipers – one of the most frequent questions I am asked is ‘what keys should I get?’ There is no perfect configuration – everything is a compromise – and the most common one is ‘standard 7’:

Note that this chart only applies for F & G pipes – on a D set the chanter stick is much longer and different restrictions apply.