Upgrading to hsqldb 2.0

July 31, 2010

We've just been through the process of upgrading from hsqldb to 2.0.0. Here's some of the things that you need to watch out for:

Data Types

Data types are now enforced, you can't put letters in integer fields any more

Data Type Lengths

Data type lengths are now enforced. If you try to alter the shape of a table that has data that is too long for the field, you'll get an error:

sql>  alter table mis_columns add column raw_column_name varchar(100);
SEVERE  SQL Error at '<stdin>' line 5:
"alter table mis_columns add column raw_column_name varchar(100)"
data exception: string data, right truncation in statement [alter table
mis_columns add column raw_column_name varchar(100)]

The way to get around this error is to increase the column lengths in the create table statement in the .script file so that it will cope with the data in the table. It will then let you alter the table.


You must run a shutdown on the database in before starting it as a 2.0.0 database, or else the database won't start. This will clearup the .log file and put everything into the .script.

Tags: hsqldb sql