Connecting to HyperSQL with OpenOffice.org

For those of you who wished for a 'user-friendly' graphical interface for a HyperSQL database, your wait is over! OpenOffice.org Base is the preferred graphical environment for a HyperSQL database as OOo actually utilises HyperSQL as its embedded database engine.

Here's what you'll need:

• A HyperSQL database server. You can download HyperSQL from here: http://sourceforge.net/projects/hsqldb/files/
• OpenOffice.org Base. You can download the full OpenOffice.org suite from here: http://download.openoffice.org/ 

And here's how to get it up and running:

1. Extract the downloaded HyperSQL bundle to a location on your local machine, e.g. C:\hsqldb-1.8.0.10

[optional]: Add the following entries to your system environment variables:

• name: HSQLDB_HOME value: <path to your HyperSQL installation, e.g. C:\hsqldb-1.8.0.10>
• name: PATH value: ;%HSQLDB_HOME%\lib; Note: This value should be added to your existing PATH value!

2. Install OpenOffice.org.

3. Configure OpenOffice.org's Java classpath. 

Open the OpenOffice.org launch pad dialog (on Windows: Start > Programs > OpenOffice.org X.X > OpenOffice.org).

Add your HyperSQL installation to the OpenOffice.org classpath settings: Tools > Options... > OpenOffice.org > Java and click on the Class Path... button (see below):

Click on Add Archive... and/or Add Folder buttons and add the hsqldb.jar and servlet.jar archives located in your HyperSQL \lib directory to OpenOffice.org's classpath settings.

You'll need to restart OpenOffice.org for the changes to take effect. Make sure to check in your taskbar (or in Windows Task Manager) for the OpenOffice.org QuickStarter Applet (look for an icon with little birds on it), otherwise OpenOffice.org won't exit fully.

4. Start the HyperSQL database server. 

Refer to the HyperSQL documentation (see: http://hsqldb.org/web/hsqlFAQ.html) or simply run the following command:

java -cp <path to your hsqldb.jar> org.hsqldb.Server -database.0 file:<path to your database .script file> -dbname.0 <database alias to use>

e.g.: java -cp C:\\hsqldb-1.8.0.10\\lib\\hsqldb.jar org.hsqldb.Server -database.0 file:C:\\Temp\\fndres\\resdb -dbname.0 resdb

Note: If the specified database instance doesn't exist, HyperSQL should generate a new instance for you. I've also outlined a convenient procedure for starting/stopping an HyperSQL database server is a previous post (see here).

5. Open a database connection.

Open OpenOffice.org Base and select Connect to existing database, then choose JDBC. Set the database connection parameters as follows:

Datasource URL: jdbc:hsqldb:hsql:<database url>;default_schema=true

JDBC driver class: org.hsqldb.jdbcDriver

User name: <database user>

where:

• Database URL: refers to the URL of your database, e.g. //localhost/resdb. Note: The 'jdbc' protion of the Datasource URL is prefilled for you.
• Database User: This is the database user accound to that OpeOffice.org should connect as, e.g. sa.

Once finished, you should be able to view/edit the database ina  similar fashion to Microsoft Access.

For more information, you can refer to the HyperSQL documentation here: http://hsqldb.org/doc/2.0/guide/openoffice-app.html

M.