15

I am trying to use HSQLDB as an embedded database in a spring application (for testing). As the target production database is Oracle, I would like to use HSQLDBs Oracle syntax mode feature.

In the Spring config I use

<jdbc:embedded-database type="HSQL" id="dataSource">
</jdbc:embedded-database>

<jdbc:initialize-database data-source="dataSource" enabled="true">
    <jdbc:script location="classpath:schema.sql"/> 
</jdbc:initialize-database>

And in schema.sql at the top I wrote:

SET DATABASE SQL SYNTAX ORA TRUE;

However, when running my test, I get the following error:

java.sql.SQLException: Unexpected token: DATABASE in statement [SET DATABASE SQL SYNTAX ORA TRUE]

Is this a syntax error or a permissions error or something entirely different?

Thanks - also for any pointers that might lead to the answer.

Given that HSQL is the Spring default for jdbc:embedded-database and given the target is Oracle, this scenario should actually be very common. However, I found nothing on the Web even touching the issue.

Update:

The issue above is resolved thanks to answer #1.

However, I now get another exception:

org.springframework.dao.DataAccessResourceFailureException: Failed to populate database; nested exception is java.sql.SQLException: java.lang.RuntimeException: unsupported internal operation: StatementCommand unsupported internal operation: StatementCommand

Any idea what this is caused by?

Jan Algermissen
  • 4,930
  • 4
  • 26
  • 39

3 Answers3

12

This option was introduced with HSQLDB 2.0.

Are you sure you are using the correct version?
Maybe you have 1.8 still in the classpath somewhere.

But that won't get you far in terms of testing anyway, because this only turns on some basic syntax "replacing", there is no real behaviour change involved here (and I'm not even talking about more advanced Oracle features like analytical functions, CONNECT BY or something similar).

It is very seldom a good idea to test your application with a DBMS that will not be used in production. It is simply not a valid test.

  • Ah, thanks. Yes - I was at 1.7. I agree with you on testing - however, in my case it is just a very minor use of the DB. – Jan Algermissen Jan 02 '11 at 16:16
  • 2
    2.0 does not work for me, I had to use newer version, (2.2.8) – banterCZ Sep 06 '12 at 07:33
  • 2.2.4 didn't work for me either (+1 to banterCZ, thanks!) I upgraded to 2.2.8 too... it was either that upgrade or http://stackoverflow.com/questions/4628857/junit-hsqldb-how-to-get-around-errors-with-oracle-syntax-when-testing-using-hsq adding the ;sql.syntax_ora=true to the URL – Wayne Earnshaw Jul 10 '13 at 05:10
  • 4
    @a_horse_with_no_name I think it is a very good idea to run unit-tests against an in-memory-db wich uses the production-dialect because it speeds up testing. If tests take to long, developers will just skip them. Of course you need an integration test as well...idealy just another configuration for the very same tests which runs in another build-phase (eg nightly build). – lostiniceland Jan 10 '14 at 10:48
9

Even if it only change some basic syntax here is an example of how you can do it: 

<bean class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" id="dataSource">
    <property name="driverClassName" value="org.hsqldb.jdbcDriver" />
    <property name="url" value="jdbc:hsqldb:mem:PUBLIC;sql.syntax_ora=true" />
    <property name="username" value="sa" />
    <property name="password" value="" />
</bean>
fredt
  • 24,044
  • 3
  • 40
  • 61
borjab
  • 11,149
  • 6
  • 71
  • 98
2

The sql.syntax_ora=true URL property enables syntax compatibility, including the NUMBER type and the DUAL table. Additional properties can be used for more behavior compatibility. These are documented in the HSQLDB Guide:

http://hsqldb.org/doc/2.0/guide/compatibility-chapt.html#coc_compatibility_oracle

fredt
  • 24,044
  • 3
  • 40
  • 61