How to get HSQLDB to emit error messages during initialization of a datasource from a .script file?

Question

I am working on a Java application that will use some Hibernate (annotated by JPA) classes, backed by a HSQLDB datasource (DBCP BasicDataSource). I am trying to manually tweak the HSQLDB ".script" file (which I can't for the life of me find the authoritative name for by web searching/reading the docs; it's only mentioned in passing) to rapidly add some new in-memory tables for testing. The problem is, any errors that occur when HSQLDB is running this script don't seem to be reported or logged anywhere. For instance, if I have an INSERT statement within that .script (a very common usage scenario to set up an initial dataset), and there is a problem with it (like incorrect date format, incorrect number of columns, etc.), nothing is ever reported, and there is no exception. I only notice later on when the HQL query I construct in the test case throws an exception due to a NULL result, which the program is not expecting since the data set should be initialized properly at that point.

The only reason I even figured out there was a problem with the INSERT statement was by performing them on a raw SQLConnection on the HSQLDB datasource, rather than doing them in the .script file, and observing the SQLExceptions that were thrown at that point.

Logging in general seems to be working and HSQLDB is logging tons of other messages of all thresholds very frequently during startup to stdout. So I'm flummoxed as to why those don't include obvious SQLExceptions that must be getting thrown at some point.

Any ideas on what to try? Some configuration I'm missing, perhaps? I tried searching all through the HSQLDB docs to no avail, but that doesn't necessarily mean the answer isn't there. In case it matters, the usage scenario I'm trying to get this working for is a Spring application context initialized within a JUnit test case (via a FileSystemXmlApplicationContext object). Thanks for any help.

Answer 1

The .script file (this is the authoritative name) is created by HSQLDB when the database is shutdown, and read when it is reopened. It is not intended to be modified by the user except in some old version upgrade scenarios. It is therefore assumed that data integrity is preserved and to maximise loading speed, no check is performed on nullability or referential integrity and incorrect number of columns. Primary key or unique constraint violations are caught because the data is inserted into indexes.

I have read a mention of DBUnit (an extension of JUnit for database operations) supporting what you need to do.

Answer 2

I don't think this is supported simply because the script file containing the definition of tables and other database objects, plus the data for non-cached tables is not supposed to contain syntactically incorrect statements (they are supposed to be written by HSQLDB itself).

But since you're using Hibernate, my suggestion would to use the import.sql feature announced in the Rotterdam JBug and Hibernate's import.sql blog post:

import.sql: easily import data in your unit tests

Hibernate has a neat little feature that is heavily under-documented and unknown. You can execute an SQL script during the SessionFactory creation right after the database schema generation to import data in a fresh database. You just need to add a file named import.sql in your classpath root and set either create or create-drop as your hibernate.hbm2ddl.auto property.

I use it for Hibernate Search in Action now that I have started the query chapter. It initializes my database with a fresh set of data for my unit tests. JBoss Seam also uses it a lot in the various examples. import.sql is a very simple feature but is quite useful at time. Remember that the SQL might be dependent on your database (ah portability!).

#import.sql file
delete from PRODUCTS
insert into PRODUCTS (PROD_ID, ASIN, TITLE, PRICE, IMAGE_URL, DESCRIPTION) values ('1', '630522577X', 'My Fair Lady', 19.98, '630522577X.jpg', 'My Fair blah blah...');
insert into PRODUCTS (PROD_ID, ASIN, TITLE, PRICE, IMAGE_URL, DESCRIPTION) values ('2', 'B00003CXCD', 'Roman Holiday ', 12.98, 'B00003CXCD.jpg', 'We could argue that blah blah');

For more information about this feature, check Eyal's blog, he wrote a nice little entry about it. Remember if you want to add additional database objects (indexes, tables and so on), you can also use the auxiliary database objects feature.

Another option would be to use DbUnit. But if (non) portability is not an issue, I'd use the import.sql trick.

Related questions

• How to execute differnet import.sql in Hibernate/JPA for each persistence unit?