OpenOffice BASE and HSQLDB in server mode

The sample database for this post can be downloaded and used with GNU/Linux. It can also be adapted to Windows and Mac.

OpenOffice Base uses hsqldb as its back end database in embedded mode. If you are a developer like me, this doesn’t normally allow you to connect other tools to the database you have created in Base. In this post, I shall explore using hsqldb in server mode. This way, you get the luxury of using Base’s interface, yet at the same time the capability to connect other tools to it such as the JBOSS tools tutorial for developing a web application to display and edit your data. If you are a PERL hacker, you can use hsqldb-ber to connect to the server. In addition, the database server is started using Maven, so your database is even extra small when you zip it, because Maven is automatically downloading the jar for when you start the database.

These are the steps to follow. First, make sure that you have Maven installed on your system and java. If you don’t, install it/them using the following command:

$ sudo apt-get install maven2
$ sudo apt-get install openjdk-6-jdk
or
$ sudo apt-get install openjdk-6-jre

Start the hsqldb employee database using the following command:

$ ./startdb.sh

I assume that you have Base already installed. Assuming that you do, you can open the database in Base by just double clicking the employee.odb and it will open in OpenOffice Base. When you open the database file, you can browse the tables, a sample query I created named itemsorderedby363 and a report named by the same thing. The odb file contains a setting to the database in server mode. You can see the database settings by going to Edit->Databse->Properties. You can connect other applications to the database, do development, and at the same time have the luxury of using OpenOffice Base GUI interface. The data in the database comes from JBoss Demo Employee Database, yet adds the connection to the database and uses Maven for the hsqldb jar dependency.

I really like hsqldb and its robustness for development. You can easily zip your database and transport it around from machine to machine with zero configuration.

 

About Brian Lavender

I recently obtained my Masters in Computer Science. I like hacking GNU/Linux and hacking different programming languages in general.
This entry was posted in Uncategorized. Bookmark the permalink.