Environment

JBoss 4.0.2
Hibernate 3.0
HSQLDB 1.8

I created a file-based HSQLDB database with runManager.bat. It contains only one table create definition. It works fine. *.properties, *.lck, *.log, *.script are created.

I copy those files to JBoss start location, and setup hibernate.cfg.xml as below

<hibernate-configuration>
<!-- Mapping to MySQL database -->
<session-factory>
<property name="hibernate.connection.driver_class">org.hsqldb.jdbcDriver</property>
<property name="hibernate.connection.url">jdbc:hsqldb:file:rds-db</property>
<property name="hibernate.connection.username">rds</property>
<property name="hibernate.connection.password">rds</property>
<property name="hibernate.connection.zeroDateTimeBehavior">round</property>
<property name="dialect">org.hibernate.dialect.HSQLDialect</property>
<property name="show_sql">true</property>

<!-- pooling -->
<property name="c3p0.min_size">3</property>
<property name="c3p0.max_size">10</property>
<property name="c3p0.timeout">300</property>
<property name="c3p0.max_statements">10</property>
<property name="c3p0.idle_test_period">3000</property>

<mapping resource="xxx/xx/data/Customer.hbm.xml"/>
</session-factory>
</hibernate-configuration>

As you can see I also use c3p0 connection pool. When I start JBOSS, I got error below

21:16:56,730 INFO [STDOUT] java.sql.SQLException: error in script file line: 1 Unexpected token: SCHEMA in statement [CREATE SCHEMA]
21:16:56,730 INFO [STDOUT] at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
21:16:56,730 INFO [STDOUT] at org.hsqldb.jdbc.jdbcConnection.<init>(Unknown Source)
21:16:56,730 INFO [STDOUT] at org.hsqldb.jdbcDriver.getConnection(Unknown Source)
21:16:56,730 INFO [STDOUT] at org.hsqldb.jdbcDriver.connect(Unknown Source)
21:16:56,730 INFO [STDOUT] at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:68)
21:16:56,730 INFO [STDOUT] at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:87)
21:16:56,730 INFO [STDOUT] at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1.acquireResource(C3P0PooledConnectionPool.java:83)
21:16:56,730 INFO [STDOUT] at com.mchange.v2.resourcepool.BasicResourcePool.assimilateResource(BasicResourcePool.java:884)
21:16:56,730 INFO [STDOUT] at com.mchange.v2.resourcepool.BasicResourcePool.acquireUntil(BasicResourcePool.java:601)
21:16:56,746 INFO [STDOUT] at com.mchange.v2.resourcepool.BasicResourcePool.access$400(BasicResourcePool.java:31)
21:16:56,746 INFO [STDOUT] at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1079)
21:16:56,746 INFO [STDOUT] at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:354)

Apparently it stuck in the 1st line of rds-db.scripts. which is

CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE Customer(ID INTEGER NOT NULL PRIMARY KEY, USERID CHAR(255),REFERENCENUMBER INTEGER,"timestamp" BIGINT)
CREATE USER SA PASSWORD ""
CREATE USER RDS PASSWORD "RDS"
GRANT DBA TO SA
GRANT DBA TO RDS
SET WRITE_DELAY 20

Whatever I do, I can never make hibernate happy. :(

mmm, not sure what this has to do with hibernate...

To isolate the problem, I take the c3p0 pooling out. I still got some error, but seems move a little bit forward.

In my Jboss MXBean class at startup time, I get some data from another server and attempt to persist them to a local file-based HSQLDB. The error happens in the saving process. It looks like hibernate is not able to find the table - TRADEDATA

09:51:14,004 INFO [TradeDataAccessor] ******* saving ******
09:51:14,113 WARN [JDBCExceptionReporter] SQL Error: -22, SQLState: S0002
09:51:14,113 ERROR [JDBCExceptionReporter] Table not found: TRADEDATA in statement [select max(id) from tradedata]
09:51:14,129 ERROR [RDSStartupService] Starting failed com.hedgestreet:service=RDSStartup
org.hibernate.exception.SQLGrammarException: could not fetch initial value
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.id.IncrementGenerator.getNext(IncrementGenerator.java:107)
at org.hibernate.id.IncrementGenerator.generate(IncrementGenerator.java:45)
at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:85)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:184)
at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:33)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:173)
at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:27)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:69)