JUnit/HSQLDB: How to get around errors with Oracle syntax when testing using HSQLDB (no privilege and/or no DUAL object)

Question

I have DAO code which contains some JDBC with Oracle-specific syntax, for example:

select count(*) cnt from DUAL 
where exists (select null from " + TABLE_NAME + "
              where LOCATION = '" + location + "')")

I am running JUnit tests on this DAO method using an in-memory HSQLDB database. Apparently the DUAL table is Oracle specific and causes an error when I run the test:

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; 
    bad SQL grammar [select count(*) cnt from DUAL where exists 
                    (select null from ESRL_OBSERVATIONS where LOCATION = '/path1')];
nested exception is java.sql.SQLException: user lacks privilege or object 
    not found: DUAL

Can anyone suggest anything I can do to get around this issue? I am using Hibernate to create the schema -- perhaps there's a setting I can make in my Hibernate properties which will enable support for Oracle style syntax?

Answer 1

If you use Hibernate 3.6 with HSQLDB 2.0.1 or later, you can use a connection property sql.syntax_ora=true on your connection URL. This enables the DUAL table together with some other Oracle specific syntax.

You probably need a few more connection properties for behaviour that is not covered by the main property. See: http://hsqldb.org/doc/2.0/guide/management-chapt.html#mtc_compatibility_oracle

Answer 2

The HSQL "Oracle style syntax" can also be enabled via a SQL command

SET DATABASE SQL SYNTAX ORA TRUE

See 12.30 . It's an alternative to the property sql.syntax_ora=true as suggested in fredt's answer. It may be more practical in some cases : the flag can be set via JDBC after the HSQL database has been started.