HyperSQL with OpenOffice

How to use HyperSQL with OpenOffice.org

Fred Toussi

The HSQL Development Group

$Revision: 5051 $

2014-02-13 18:22:10-0500

Table of Contents

HyperSQL with OpenOffice
Using OpenOffice / LibreOffice as a Database Tool
Converting .odb files to use with HyperSQL Server

HyperSQL with OpenOffice

OpenOffice.org / LibreOffice / ApacheOpenOffice includes HyperSQL and uses it for embedded databases. Our collaboration with OpenOffice.org developers over the last few years has benefited the development and maturity of HyperSQL. Before integration into OOo, HSQLDB was intended solely for application-specific database access. The application developer was expected to resolve any integration issues. Because OpenOffice.org is used by a vast range of users, from schoolchildren to corporate developers, a much higher level of quality assurance has been required. We have achieved it with constant help and feedback from OOo users and developers.

Apart from embedded use, you may want to use OpenOffice / LibreOffice with a HyperSQL server instance. The typical use for this is to allow multiple office users access to the same database.

There is also a strong case for using OpenOffice to develop your database schema and application, even if the database is intended for your own application, rather than OpenOffice.

HSQLDB version 1.8.0 is included in OOo, ApacheOpenOffice and LibreOffice 3.x. You can simply replace the jar with an HSQLDB version 2.2.9 jar to use the latest capabilities with external databases. It is not yet possible to create and use embedded databases with this version.

HSQLDB version 2.x jar will hopefully be included in the future versions of ApacheOpenOffice and LibreOffice.

Using OpenOffice / LibreOffice as a Database Tool

OpenOffice is a powerful database front end. If you want to create schemas, edit tables, edit the database contents manually, design and produce well-formatted reports, then OpenOffice is probably the best open source tools currently available.

To connect from OpenOffice to your database, first run a local server instance for the database. This is describes in the Network Listeners chapter of this guide.

When you connect from OpenOffice.org, you must specify connection to an external database and use the URL property "default_schema=true". For example, the URL to connect the local database may be like

 jdbc;hsqldb:hsql://localhost/mydb;default_schema=true 

The only current limitation is that OpenOffice only works with the PUBLIC schema. This limitation will hopefully disappear in the future versions of OOo.

There wil hopefuly be a version 2.x jar in the future versions of OpenOffice.

Converting .odb files to use with HyperSQL Server

You may already have an OOo database file, which you want to use outside OOo, or as a server database. The file is in fact in the standard ZIP format and contains the normal HyperSQL database files. Just use a utility such as 7Zip to expand the .odb file. In the /db directory, there are files such as .script, .data, etc. Just rename these files into mydb.script, mydb.data, etc. You can now open the mydb database directly with HyperSQL as an embedded database or as a server instance.


$Revision: 5206 $