Error when trying to use HSQLDB

[Demetrius Nunes]

I downloaded the new version of JMatter and modified the
hibernate.properties of the ContactMgr to:

hibernate.connection.driver_class=org.hsqldb.jdbcDriver
hibernate.dialect=org.hibernate.dialect.HSQLDialect
hibernate.connection.url=jdbc:hsqldb:file:contactmgr.db
hibernate.connection.username=sa
hibernate.connection.password=

"ant schema-export" works alright.

But when I try "ant run" I get:

[java] INFO: time to build session factory and open session: 2265(ms)
[java] 21/06/2006 17:22:50 org.hibernate.util.JDBCExceptionReporter logExce
ptions
[java] WARNING: SQL Error: -22, SQLState: S0002
[java] 21/06/2006 17:22:50 org.hibernate.util.JDBCExceptionReporter logExce
ptions
[java] SEVERE: Table not found in statement [select count(*) as col_0_0_ fr
om users user0_ where user0_.username='admin']
[java] Exception in thread "main" org.hibernate.exception.SQLGrammarExcepti
on: could not execute query using iterate
[java] at org.hibernate.exception.SQLStateConverter.convert(SQLStateCon
verter.java:67)
[java] at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExcep
tionHelper.java:43)
[java] at org.hibernate.loader.hql.QueryLoader.iterate(QueryLoader.java
:420)
[java] at org.hibernate.hql.ast.QueryTranslatorImpl.iterate(QueryTransl
atorImpl.java:318)
[java] at org.hibernate.engine.query.HQLQueryPlan.performIterate(HQLQue
ryPlan.java:177)
[java] at org.hibernate.impl.SessionImpl.iterate(SessionImpl.java:1156)

[java] at org.hibernate.impl.QueryImpl.iterate(QueryImpl.java:46)
[java] at com.u2d.app.Application.initObjects(Application.java:103)
[java] at com.u2d.app.Application.launch(Application.java:148)
[java] at com.u2d.app.Application.main(Application.java:418)
[java] Caused by: java.sql.SQLException: Table not found in statement [sele
ct count(*) as col_0_0_ from users user0_ where user0_.username='admin']
[java] at org.hsqldb.jdbc.Util.throwError(Unknown Source)
[java] at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
[java] at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Sourc
e)
[java] at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(Abstr
actBatcher.java:442)
[java] at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(Abstr
actBatcher.java:368)
[java] at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(Abst
ractBatcher.java:105)
[java] at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java
:1561)
[java] at org.hibernate.loader.hql.QueryLoader.iterate(QueryLoader.java
:392)
[java] ... 7 more

[eitan]

the error message indicates that the schema export did not appear to
work. it can't find
the table 'users.' fyi, i used hsqldb version 1.8_0_4 which is the
latest stable version.

use the hsqsldb databasemanager to verify that the tables were actually
created.
also, when you run the schema-export, i dump the ddl to
build/schema.sql, so you
can check the contents of that file to verify the operation of
hibernate's schema-export.

i'll do some more testing to see if i can reproduce this issue.

[eitan]

i forgot to mention..i know hsql db creates a number of files for each
database.
it basically appends a different suffix to the base name. i wonder if
the fact that
your specified database name is contactmgr.db causes any issues with
hsql.

[Demetrius Nunes]

On 6/21/06, eitan <eitan...@gmail.com> wrote:
>
> the error message indicates that the schema export did not appear to
> work. it can't find
> the table 'users.' fyi, i used hsqldb version 1.8_0_4 which is the
> latest stable version.

I used the one bundled with JMatter.

>
> use the hsqsldb databasemanager to verify that the tables were actually
> created.

How can I do that?

> also, when you run the schema-export, i dump the ddl to
> build/schema.sql, so you
> can check the contents of that file to verify the operation of
> hibernate's schema-export.

It´s there. All seems fine.

[Demetrius Nunes]

Not really. I tried before with plain "contactmgr" and it´s the same.

On 6/21/06, eitan <eitan...@gmail.com> wrote:
>

[Eitan Suez]

I used the one bundled with JMatter.

oh yes, of course.  :-)

>
> use the hsqsldb databasemanager to verify that the tables were actually
> created.

How can I do that?

 java -cp jmatter/lib/src/hsqldb.jar org.hsqldb.util.DatabaseManagerSwing
 
/ eitan

[Demetrius Nunes]

Just did that. It really didn´t persisted the tables although it
executed the commands.

Is there something like a autocommit property that should be set true somewhere?

[Eitan Suez]

ok, i've reproduced the problem, using the contactmgr
demo app.  still working on it..

/ eitan

[eitan]

well, it warms the heart that there are dozens of reports on the
hibernate
mailing list regarding schema or tables not getting generated with
hsqldb.
here's but one example..

http://forum.hibernate.org/viewtopic.php?t=960002&highlight=hsqldb+schema+export

now to find a thread with an answer!...

[eitan]

ok, got it.
see:
http://forum.hibernate.org/viewtopic.php?t=940532&highlight=hsqldb+schema+export

or simply, add this to the hibernate.properties file for hsqldb:
hibernate.connection.shutdown=true

seems to work here..

[tobe]

Just packed jMatter and tried the demo step by step out of the box.
Just using the hibernate configuration as it was for hsqldb and the
shutdown property is set to true.

But...

I get the same error as the person who started this thread when I try
"ant run"...

[eitan]

i am also getting this error again. what a pain.
the problem is that schema-export does not work, and settings
hibernate.connection.shutdown
does not seem to help. i'm not sure why it worked the first time, but
i can't get it to work again either...back to foraging through the
hibernate mailing list..

[tobe]

OK, looking forward to the solution...

Meanwhile, I am going forward with postgresql.

As a suggestion, modify section 3.1.1 to include the -P option on the
postgresql createuser command as it does not seem to work with a user
without a password.

Anyway, jMatter seems great so far, good going!

[eitan]

thanks!

and here's a configuration that seems to work with hsqldb:

set both of these in hibernate.properties:

hibernate.connection.pool_size=0
hibernate.connection.shutdown=true

[tobe]

Thanks, that does work

[mnuttall]

This doesn't work for me. I still get the error. hmm.

[mnuttall]

Cheated. Ran the script from the HSQL manager. Had to run the commit
command from within HSQL manager

[eitan]

yeah, that works. but then you have to make sure that
at runtime when you create an object that it gets committed
as well.

i'm not sure what the deal is with hsqldb+hibernate. on my end
(hibernate latest v3.1.x with hsqldbv1.8.x on linux) i can reliably
get hsqldb to work with both properties set (pool_size=0 +
shutdown=true)

i found the two properties:

hibernate.connection.pool_size=0
hibernate.connection.shutdown=true

on two separate discussion threads.

try maybe also setting autocommit to false explicitly.
if you do get things to work reliably, let me know.

[mnuttall]

will do. When i do ant run the objects seem to persist correctly. It
is just the schema creation. Weird.