Psuedo Guru Blog by a wannabe guru with spelling issues.

HSQLDB read only from Open Office via JDBC

Tried to open an existing hsqldb 1.8.x database from Open Office 2.0 using JDBC. I was unable to edit any of the tables. Turns out this is a known bug documented here:

http://www.openoffice.org/issues/show_bug.cgi?id=55029

The suggested workaround did fix the problem. Just add the following to the end of the JDBC connection url (don't forget the semicolon at the start):

;default_schema=true

You would think they would have better support for HSQLDB, since it is their internal database format, but I guess not.

The database is already in use by another process

I was writing an app that uses HSQLDB in Eclipse. The app would work fine the first time I ran it, but subsequent invocations within Eclipse resulted in the following error:

java.sql.SQLException: The database is already in use by another process

I got tired of restarting Eclipse each time, and decided to RTFM. Found this on the hsqldb website:

All databases running in different modes can be closed with the SHUTDOWN command, issued as an SQL query. From version 1.7.2, in-process databases are no longer closed when the last connection to the database is explicitly closed via JDBC, a SHUTDOWN is required. In 1.8.0, a connection property, shutdown=true, can be specified on the first connection to the database (the connection that opens the database) to force a shutdown when the last connection closes.

So turns out the fix was easy. I can either set the shutdown property, or issue the shutdown command via SQL. I chose the latter. Here is my code for closing the database:

Statement stmt = conn.createStatement();
stmt.execute("SHUTDOWN");
stmt.close();
conn.close();

Works fine now.

FYI, this was also causing a problem with Excelsior Jet, which seems to keep the JVM loaded between invocations of compilied apps, so while Eclipse was ok between restarts, I had to physically reboot the computer between invocations of a compiled Jet program, which was really inconvenient.