How To / FAQ

How to install and run HSQLDB
How to compile HSQLDB
How to create a new database
How to start programming JDBC / HSQLDB
Where to get more documentation
How to use HSQLDB inside JBuilder
Selecting big results / Scanning big tables
Java Stored Procedures / Functions
How to upgrade from an old version

Reliability, Performance and Deployment

Documentation

---

How to install and run HSQLDB

• Download the hsqldb.zip file in the download page to your local computer.
• Expand the .zip. Any program that can handle zip files should work If you have trouble expanding the zip file and have installed a JDK you may also use the command line tool jar: open a command prompt, change to the directory where the file hsqldb.zip is, and type:
  jar -xf hsqldb.zip
• You need a Java Runtime Environment (JRE) or Java Development Kit (JDK) to run HSQLDB.
• Browse the documentation included in the hsqldb folder. Start with 'index.html'.
  

---

How to compile HSQLDB

A compiled JAR of HSQLDB is included in the download. This runs under JRE 1.4 or later. If you want to re-compile for other JDK's, you will need a JDK and ANT. See the documentation in the /build directory.

Command line compiler tools are included in the free JDK (Java Development Kit) from Sun.

---

How to create a new database

A new database is created automatically if it does not yet exist. Just connect to the not-yet-existing database using the jdbc:hsqldb:file:«database-path» URL (should replace the last part with the path you want) with the user 'sa' and an empty password.

---

How to start programming JDBC / HSQLDB

HSQLDB comes with documentation, example applets and source code that can help programers who are new to JDBC programming.

An example application is in the guide. Another example is FindFile.java in the /src/org/hsqldb/sample folder. This is a tool to search files. The source code of this demo application is well documented, and the functionality is kept low, so it should be easy to understand what is going on just reading the source code.

Because the source code of all other tools and applets is also included in the download, more advanced programmers will find some hints there. Check the sources in /src/org/hsqldb/sample and /src/org/hsqldb/test folders.

---

Where to get more Documentation

HSQLDB has a standard JDBC interface. HSQLDB specific JDBC documentation is included in the /doc/src folder.

There are also many books available on JDBC programming.

For people who are interested in the database engine design of HSQLDB, the source code is where to look.

---

How to use HSQLDB inside JBuilder, Eclipse, etc.

To use HSQLDB at design-time in JBuilder, Eclipse or other tools, you usually require the plug-in for databases that comes with the development environment. You usually need to add a reference to the HSQLDB jar to the environment. Also you normally need to register the JDBC driver (which is part of the hsqldb.jar) with the environment.

---

Selecting big results / Scanning big tables

One limitation of HSQLDB is that it currently does not support server side cursors. (This allows it to run without any writeable media). This means the result of a query must always fit in memory, otherwise an OutOfMemory error occurs. In the rare situation that a huge resultsets must be processed, then the following workaround can be used:

Limit the ResultSet using Statement.setMaxRows(1024), and select multiple 'smaller' blocks. If the table is for example 'CREATE TABLE Test(Id INT IDENTITY PRIMARY KEY, Name VARCHAR)' then the first block can be selected using 'SELECT * FROM Test'. The biggest ID should be recorded and the next block should be selected using 'SELECT * FROM Test WHERE Id>(biggest_id)' until no more records are returned. Don't forget to switch of the limit using setMaxRows(0).

---

Java Stored Procedures / Functions

Stored procedures are Java functions that are called directly from the SQL language or using an alias. Calling Java functions (directly or using the alias) requires that the Java class can be reached by the database (server). User defined stored procedures / functions must be placed in the classpath of the database server. For HSQLDB, stored procedures and functions are equal. The syntax for calling functions and stored procedures (inside a SELECT statement for example) is:

SELECT Value, "java.lang.Math.sqrt"(Value) AS SQRT FROM Test

It can be called also without the SELECT, using the command CALL. This makes more sense for 'Stored Procedures' but works for functions, too:

CALL "java.lang.Math.sqrt"(2.0)

The package must be provided, and the name must be written as one word, and inside double quotes (") because otherwise it is converted to uppercase (and not found).

An alias can be created using the command CREATE ALIAS:

CREATE ALIAS SQRT FOR "java.lang.Math.sqrt"

When an alias is defined, then the function can be called additionally using this alias:

CALL SQRT(2.0)
SELECT Value, SQRT(Value) AS SQRT FROM Test

These are the rules when creating user defined functions / stored procedures:

• The function must be 'public static'
• Parameters that are not provided by the user are filled with 'null' by the system. For example SIN() is equal to SIN(NULL). SIN is the alias for java.lang.Math.sin.
• When a parameter is 'null' and the parameter type in the function is a basic data type (for example 'int', 'double') then the function is not called at all and 'null' is returned by the system. In the example above java.lang.Math.sin(double d) is not called because the parameter d is 'null'.
• When a parameter can be null, don't use the basic data types (for example int) use the class instead (for example java.lang.Integer).
• An example for functions with optional parameters is org.hsqldb.Library.substring(String s,int start,Integer length): the parameters 'length' is optional. If not provided by the user, it is filled with 'null' by the system, and the function is called. There is a system defined alias for this function so it can be called as CALL SUBSTRING('mystring', 1, 3).
• Stored procedures (or 'constants' like PI) with no parameters are called with brackets: PI(). PI alone can be a (table- or column-) name.
• If you need a Connection in your function, just provide a first parameter, 'Connection'. This parameter will be filled by HSQLDB. Example is the function org.hsqldb.Library.user(Connection conn). The function is called by the user with: CALL USER()
• A stored procedure (with a Connection parameter as the first argument) can contain SQL queries. In this case it is important to use only the CALL command to invoke the procedure. Other arguments for the queries can be defined after the Connection arguments. This mechanism allows great flexibility for updating the database as it has the following properties:
  • In a multi-session environment, all the SQL queries in the body of the procedure are run consecutively in the current session without any interveing queries from other sessions. If a transaction is performed by these queries, it is run at isolation level SERIALIZEABLE. The transaction can be rolled back or committed depending on the intermediate resuts of the queries.
  • In networked client-server mode, only one network roundtrip is needed to run a series of SQL queries. This improves speed by an order of magnitude by reducing the network latency.

 

A security mechanism is provided for non-admin users: Access to each class can be granted / revoked to a user:

GRANT ALL ON "org.mysoftware.MyClass" TO PUBLIC

By default, all users have access to java.lang.Math and org.hsqldb.Library.

---

How to upgrade from an old version to the current version

An old version database can always be opened by the latest version and automatically converted if there is no CACHED table data in the database (the *.data file is empty or does not exist).

With CACHED tables, the procedures in the Deployment Issues section of the Guide should be followed.

Note that an upgrade is a one-way process, so please always keep a backup of the old database.

---

Reliability, Performance and Deployment

• May I use HSQLDB in a commercial product?

  Yes. HSQLDB is Open Source and free to use in any commercial product so long as the terms of the Licenses are met. The Licenses of HSQLDB and Hypersonic SQL (on which HSQLDB is based) are both based on the new BSD License.

• Does HSQLDB store all data in memory. Doesn't memory run out as a result?

  Only if you want to. By default, CREATE TABLE results in a memory table, as this is the best type for smaller tables. For larger tables, use CREATE CACHED TABLE and adjust the hsqldb.cache_scale to suite your memory use requirements (as little as 8MB or so). See the Deployment Issues section of the Guide. There is no simple rule and no imposition on the part of HSQLDB as maximum flexibility is allowed using only a couple of settings. A popular use of HSQLDB is for OLAP, ETL, and data mining applications where huge Java memory allocations are used to hold millions of rows of data in memory.

• How does HSQLDB compare to other RDBMS engines in SQL support / JDBC support

  HSQLDB has very extensive support for SQL 92, 99 and 2003. This support is not full but is in some respects better than many other products. JDBC support is good and extends to real prepared and batch statements, full metadata reporting on database objects and result sets.

• How does HSQLDB compare to other RDBMS engines in speed
  This is something that you can measure. Overall, HSQLDB is comparable in speed to fast, non-java open-source RDBMS engines. It is generally much faster than java open-source engines in most operations. You can use the performance tests supplied with HSQLDB. One test, JDBCBench.java is a standard TPC-B implementation that measures speed and reliablility of multi-threaded access. Another test, TestCacheSize.java, is a single-threaded test for speed of INSERT, UPDATE, DELETE and SELECT operations with hundreds of thousands of rows. Some comparisons have been posted by users in our mailing lists and on the web. Any specific SELECT query speed issues can normally be resolved with slight modifications to the query or by adding appropriate indexes. See the Guide for details.
  
  
• How solid is HSQLDB

  HSQLDB 1.7.2 / 1.7.3 / 1.8.0 have undergone several months of extensive user tests with over 20,000 downloads of the Release Candidate versions. This includes extensive SQL compatibility tests and stress tests by application vendors who use HSQLDB in their products. All the reported issues were fixed before the final releases and no serious issue has been reported since the 1.7.2 / 1.7.3 release. Minor reported problems since the original releases have been fixed promptly in revisions. If any new issue arises, it will be dealt with promptly.

• How solid is HSQLDB when a machine crashes
  
  HSQLDB employs a redo log for data recovery. All the changes to the database are reflected in this log. Extensive user tests have demonstrated this mechanism to be effective and fail-safe in most cases. For added security, you can backup the database files while the engine is running.
  
  
• Can HSQLDB handle multithreading?

  The core engine is currently not multithreaded (*), but it is multithreading-safe. The Server is multithreading and responds to requests while any database core is busy. Application that are multithreading can use the database, but all requests are executed one after the other per database, this means only one at a time. This model contributes to speed, reliability, and completely avoids issues such as deadlocks (*). There is a separate timer thread for flushing and synching the transaction log to disk. Server modes also use threads to respond to connection request.

• What are the limitations of the database (size of columns, number of tables, rows...)?

  There is no imposed limitation. Number of columns, tables, indexes, size of columns and so on is limited only by the memory. For example, a user reported using a SELECT statement with 41 LEFT OUTER JOIN clauses on a huge database for a data mining application.

• My database runs out of memory / How much memory does a database need?

  If only memory tables (CREATE TABLE or CREATE MEMORY TABLE) are used then the database is limited by the memory. A minimum of about 100 bytes plus the actual data size are required for each row. If you use CREATE CACHED TABLE, then the size of the table is not limited by the memory beyond a certain minimum size. The data and indexes of cached tables are saved to disk. With text tables, indexes are memory resident but the data is cached to disk.

• What is the biggest known HSQLDB database?

  With older versions tests had been made with 4,000,000 records using the SelfTest (TestSelf) application (insert, update, delete). The size of the database file was then over 400 MB. For this test the JVM memory was limited to 16 MB. The current (1.8.0) size limit of an HSQLDB database is 8GB for all CACHED tables, 2GB for each TEXT table and less than 2GB for all memory tables. Extensive tests have been made with the latest versions using the TestCacheSize application inserting millions of rows and resulting in data files of over 1 GB.

• After the program is finished, almost all statements are in the *.log file, and the *.data file is almost empty. Why?

  The database was not shut down properly. When you restart the database, the *.log file will be processed and an automatic checkpoint will be performed. No commited data will be lost. To avoid this, use the SQL command "SHUTDOWN" when your application has finished with the database.
  
  The statements that make up the database are saved in the *.script file (mostly CREATE statements and INSERT statements for memory tables). Only the data of cached tables (CREATE CACHED TABLE) is stored in the *.data file. Also all data manipulation operations are stored in the *.log file (mostly DELETE/INSERT) for crash recovery. When the SHUTDOWN or CHECKPOINT command is issued to a database, then the *.script file is re-created and becomes up-to-date. The .log file is deleted. The *.data file is also closed and backed up as the *.backup file. When the database is restarted, all statements of the *.script file are executed first and new statements are appended to the .log file as the database is used.

• The size of the .data file grows in operation although the total row count for CACHED tables has not gone up. Why?

  HSQLDB tracks only up to a fixed number of the empty spaces left after DELETE or during UPDATE operations. If the delete rate is higher than insert rate, or large number of rows are updated in a single query, then some spaces are left empty. Both CHECKPOINT DEFRAG and SHUTDOWN COMPACT commands will remove the empty spaces. In version 1.8.0 empty spaces are tracked better and DEFRAG will be performed automatically, based on a user-defined property.

• I need transaction isolation levels beyond read-committed

  HSQLDB support only READ UNCOMMITTED isolation level when queries are made from your Java program. However, it supports the SERIALIZABLE isolation level if you write the database access code as public static Java methods and call them as stored procedures (see above). Each method should contain all the code and queries that make up a transaction, including any rollback or commit statements. Values from your program should be submitted as parameters to the stored procedures and functions, while any Java object, including even ResultSet objects, can be returned by a function. This is the logical approach for application-specific databases, deployed either in-process, or as a database server.
  
  See the Guide for a fuller discussion of all the issues.

 

This text is based on original Hypersonic SQL documentation, updated to reflect the latest version of HSQLDB