Friday, July 21, 2006
JDBC Issues with OpenOffice.org Database files
I have been working with trying to get a JDBC connection to an OpenOffice.org database file for the past few days. Using the HSQLDB.jar file included with OpenOffice (OO uses the HSQLDB engine for Base) I have tried everything, and each time I get an error about "no table" with my select statements. I tried creating a new database programatically via JDBC, tried reconnecting, recreating. I researched on the Internet and have found scores of programmers who have the same issue, and yet no solution seems available. Of course, I've commented on the futility of searching the OpenOffice site for answers before. So just when I'm about to give up, I have a hunch and hex-edit the .odb file that OpenOffice creates. Amazingly, it only takes seeing the first two bytes of the file and everything makes sense. When you see that distinctive "PZ" starting a file, its almost like seeing the Coke logo, you immediatly know what your looking at. The reason I can't connect to the database via JDBC is because OpenOffice does not store the files using the file format from HSQL, it uses a ZIP file. I confirm this by running "file
.odb", and sure enough, its a zip file. So I unzip the file, and there, lo and behold, are the HSQL database files, along with a bunch of XML files describing things such as forms and queries. Interesting notes, the properties file states that OO uses the HSQL Cached table types. This gives a good indication how I should build my JDBC URL when connecting to these files. Now that one mystery is solved, I can work on connecting via JDBC to these files. I will work on building either a JDBC connector for OpenOffice files for BIRT, or I will make a scripted data connection so that I can use OpenOffice files in BIRT for reporting.