HSQLDB Installation and Usage

HyperSQL is relational database software that can be downloaded and used for free. It works on top of Java.

HSQLDB Installation

I downloaded the HyperSQL package (hsqldb-2.2.8.zip) and extracted its contents into the "C:\Program Files\hsqldb" folder.

Starting an HSQLDB Server

The following example command starts the HyperSQL server with one database called "mydb" and the public name of "xdb". The public name hides the file names from users.

java -cp ../lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:mydb --dbname.0 xdb

If the files named "mydb.*" do not already exist, they will be created. This way you can start the HSQLDB server and create a new database at the same time.

If the computer hosting the database server is restricted to the HTTP protocol by a firewall, use the HyperSQL HTTP Server.

java -cp ../lib/hsqldb.jar org.hsqldb.server.WebServer --database.0 file:mydb --dbname.0 xdb

When a HyperSQL server is running, client programs can connect to it using the HSQLDB JDBC Driver contained in hsqldb.jar.

Running Database Access Tools

HyperSQL includes the Database Manager JDBC frontend to connect to a HSQLDB server, query and modify tables.

java -cp ../lib/hsqldb.jar org.hsqldb.util.DatabaseManagerSwing

At the Connect dialog, enter the information necessary for connecting to the database.

SQL Commands

After I started the Database Manager using the command above, I entered the following SQL commands to create tables for MyBooks database.

CREATE CACHED TABLE Publishers (pnum INTEGER NOT NULL PRIMARY KEY, publisher VARCHAR(50) NOT NULL, parent VARCHAR(50));



CREATE CACHED TABLE Authors (anum BIGINT NOT NULL PRIMARY KEY, author
VARCHAR(50) NOT NULL, lastname VARCHAR(25), aka VARCHAR(50));



CREATE CACHED TABLE Books (index VARCHAR_IGNORECASE(20) NOT NULL PRIMARY KEY, title VARCHAR(50) NOT NULL, edition TINYINT, pubdate DATE NOT NULL, author VARCHAR(64) NOT NULL, anum BIGINT NOT NULL REFERENCES Authors, pnum INTEGER NOT NULL REFERENCES Publishers, pages SMALLINT, format VARCHAR_IGNORECASE(20), isbn13 CHAR(13), isbn10 CHAR(10));



CREATE CACHED TABLE Keywords (index VARCHAR_IGNORECASE(20) NOT NULL PRIMARY KEY REFERENCES Books, genre1 VARCHAR_IGNORECASE(25), genre2
VARCHAR_IGNORECASE(25), subject1 VARCHAR_IGNORECASE(36), subject2
VARCHAR_IGNORECASE(36), subject3 VARCHAR_IGNORECASE(36), keyword1
VARCHAR_IGNORECASE(25), keyword2 VARCHAR_IGNORECASE(25), keyword3
VARCHAR_IGNORECASE(25));

When you finish using the Database Manager, make sure that you properly shut down the database.



SHUTDOWN: To save changes and close the database.

SHUTDOWN COMPACT: To save the database in the minimal size and close it. Should be used periodically.

Using OpenOffice.org Base to Work with HSQLDB Server

You can use OpenOffice.org Base to work with a server-based HyperSQL database. First, you need to set up OpenOffice.org so that it can use the HyperSQL JDBC driver. Select Tools > Options > Java and click Class Path.

Click the Add Archive... button and enter the path to the hsqldb.jar file. Click OK to save the changes and restart OpenOffice.org Base. Make sure that you also close the OpenOffice.org Quickstart in the notification area of the taskbar. At the Database wizard, choose JDBC to connect to an existing database, and enter org.hsqldb.jdbc.JDBCDriver for the JDBC driver class.

Enter SA as username. Password is not required.