Configure HSQLDB Server Mode for LibreOffice Base

By default, LibreOffice Base uses an embedded HSQLDB engine that restricts database access to a single user at a time. Converting to an external HSQLDB server mode resolves this limitation, allowing multiple users to perform concurrent read and write operations across a network. This guide walks through setting up the external HSQLDB server, configuring LibreOffice’s Java environment, and establishing a multi-user JDBC connection.

1. Prepare the HSQLDB Files

1. Download the HSQLDB package (version 1.8.x for native legacy compatibility, or version 2.x/newer for updated features).
2. Extract the hsqldb.jar file located within the /lib directory of the downloaded archive.
3. Create a dedicated folder for your database server on your host machine (e.g., C:\hsqldb\data or /opt/hsqldb/data) and copy hsqldb.jar into this directory.

2. Start the HSQLDB Network Server

Open a terminal or command prompt, navigate to your database directory, and launch the server using Java:

java -cp hsqldb.jar org.hsqldb.Server -database.0 file:shared_db -dbname.0 shared_db

• -database.0 file:shared_db: Sets the file path and prefix for the database files.
• -dbname.0 shared_db: Sets the alias used by client applications to connect.

To keep the server running automatically, you can configure this command as a background service or daemon using Windows Services or systemd on Linux.

3. Configure the Java Classpath in LibreOffice

LibreOffice requires access to the HSQLDB JDBC driver to communicate with the external server.

1. Open LibreOffice.
2. Navigate to Tools > Options (or LibreOffice > Preferences on macOS).
3. Select LibreOffice > Advanced.
4. Ensure Use a Java runtime environment is checked.
5. Click the Class Path… button.
6. Click Add Archive…, browse to and select your hsqldb.jar file, then click Open.
7. Click OK, then click OK again to apply the settings. Restart LibreOffice to ensure the driver is loaded.

4. Connect LibreOffice Base to the External Database

1. Open LibreOffice and launch the Base application.
2. In the Database Wizard, select Connect to an existing database.
3. Choose JDBC from the dropdown menu and click Next.
4. Set up the connection parameters:
   • Datasource URL: jdbc:hsqldb:hsql://<SERVER_IP_OR_HOSTNAME>/shared_db (Replace <SERVER_IP_OR_HOSTNAME> with localhost for local testing or the actual network IP address of the server).
   • JDBC driver class: org.hsqldb.jdbcDriver (or org.hsqldb.jdbc.JDBCDriver for HSQLDB 2.x).
5. Click Test Class to verify that LibreOffice can locate the driver. Click Next.
6. Enter the database username (the default is SA) and password (leave blank by default if none was set).
7. Click Test Connection to confirm network connectivity to the running HSQLDB server.
8. Click Next, choose whether to register the database in LibreOffice, and click Finish to save your .odb file.

5. Enable Access for Other Users

Distribute the .odb front-end file to client machines on your local network. Ensure each client machine has Java configured with the hsqldb.jar classpath pointing to their local copy of the driver and that the host server’s firewall allows incoming connections on the default HSQLDB port (9001). Multiple clients can now open the .odb file simultaneously and perform read/write actions on the shared database.