TopLink JPA and HSQLDB Quirk

I discovered a weird issue when using HSQLDB as database provider for TopLink JPA.

We need to explicitly include the toplink.target-database property in the persistence.xml file to make it work.

<property name="toplink.target-database" value="HSQL"/>

Without that, you are most likely to get this error below when you run your application:


Query: DataModifyQuery()
	at oracle.toplink.essentials.exceptions.DatabaseException.sqlException(DatabaseException.java:304)
       ...
Caused by: java.sql.SQLException: Table not found in statement

I did not have that property set when I was using TopLink JPA with MySQL so I was a bit surprised why it was not working initially when I switched to HSQLDB.

Anyway, here’s the complete properties element of persistence.xml for HSQLDB for your reference:

      <properties>
         <property name="toplink.ddl-generation" value="drop-and-create-tables" />
         <property name="toplink.jdbc.driver" value="org.hsqldb.jdbcDriver" />
         <property name="toplink.jdbc.url" value="jdbc:hsqldb:notifications" />
         <property name="toplink.jdbc.user" value="sa" />
         <property name="toplink.jdbc.password" value="" />
         <property name="toplink.target-database" value="HSQL"/>
      </properties>

Hope I can save you guys a lot of trouble through this post.

Because it certainly took me hours to figure this thing out.

Advertisements

6 Comments »

  1. minanov said

    Hmm, that is interesting
    I am suing HSQLDB too with Toplink Essentials.

    Thanks

  2. Indian said

    Thanks a lot!! it works fine :o)

  3. jadrake said

    This proved to be very helpful in setting up my unit tests. I linked my blog to this blog entry.

  4. […] Before i looking many hours in Google i find the solution thanks to this web […]

  5. Rama said

    hi..
    I want to use hsql database along with glassfish for persisting data.But I am facing some problems with configuration.
    can u please tell what is required configuration for setting up a connection pool.
    I created a pool using username,password and url properties..but whne i try to ping its giving exception saying Connection Object cant be NULL.

    please help me..

  6. dayg said

    Sorry Rama but this looks like a Glassfish issue which I’m not very familiar with.

    Did you try following the steps from this post?

    HTH.

RSS feed for comments on this post · TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: