Hi,

I have an app that runs fine with Hibernate with MySql 4.1, however when I attempt to perform a simple save operation with Hypersonic SQL (HSQLDB) the save does not work.

The details are:
1. I am using "increment" for the generator
2. Database version is Hypersonic 1.7.3
3. Hibernate version is 2.1.7c

What seems to be happening (according to the stack trace) is that the query to get the next id number is failing:

Caused by: java.sql.SQLException: Table not found: user in statement [select max
(user_id) from "user"]

I did check tha the table does exist.

I see that version 2.1.8 has been released but the release notes are still for 2.1.7. Will upgrading fix this issue?




Hibernate version:
2.1.7.c

Mapping documents:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping package="com.scottwalter.yoursite.security.db">

<class name="DBUser" table="user">

<id name="id" column="user_id"
unsaved-value="-1">
<generator class="increment"/>
</id>

<property name="username"
column="username"
not-null="true"/>

<!-- password is a keyword in some
databases, so quote it -->
<property name="password"
column="`password`"
not-null="true"/>

<property name="emailAddress"
column="email"/>

<!-- Mapping for the component class Name -->
<!--
<component name="name">
<property name="firstName" column="name" />
</component>
-->

<property name="name"
column="name"/>

<property name="rememberPasswordQuestion"
column="remember_question"/>

<property name="rememberPasswordAnswer"
column="remember_answer"/>

<property name="nickname"
column="nickname"/>

<property name="url"
column="url"/>

<property name="country"
column="country"/>

<property name="zipcode"
column="zipcode"/>

<property name="userType"
column="user_type"/>

<property name="userStatus"
column="user_status"/>

<property name="createDate"
column="create_date"
update="false"
not-null="true"/>

<!-- A many-to-many association modelled using
a composite-element mapping (this lets us
keep track of the time of creation for the
link. We use an <idbag>, since that gives
us a nice surrogate key. -->
<idbag name="userRoles"
lazy="true"
table="user_role"
cascade="save-update">
<!-- the surrogate primary key -->
<collection-id column="user_role_id"
type="integer">
<generator class="increment"/>
</collection-id>
<!-- the foreign key of the User -->
<key column="user_id"/>
<!-- a composite-element holding the
associated Role and User, and the
creation time of the link -->
<composite-element class="DBUserRole">
<!-- for convenience, a backpointer
to the User -->
<parent name="user"/>
<!-- an "extra" column -->
<property name="websiteId" column="website_id"/>
<!-- the foreign key of the Role -->
<many-to-one name="role" class="DBRole"
cascade="save-update"
column="role_id"/>
<!-- since Roles are cached, disable
outerjoining! -->
</composite-element>
</idbag>

</class>

<query name="findUserByUsernameAndPassword">
<![CDATA[
select distinct u from DBUser u where u.username = :username and password = :password
]]>
</query>

<query name="findUserByUsername">
<![CDATA[
select distinct u from DBUser u where u.username = :username
]]>
</query>

<query name="findAllUsers">
<![CDATA[
select distinct u from DBUser u left outer join fetch u.userRoles ur left outer join fetch ur.role
]]>
</query>

<query name="findUserById">
<![CDATA[
select distinct u from DBUser u left outer join fetch u.userRoles ur left outer join fetch ur.role where u.id = :userid
]]>
</query>

<query name="findUsersByType">
<![CDATA[
select distinct u from DBUser u left outer join fetch u.userRoles ur left outer join fetch ur.role where u.userType = :usertype
]]>
</query>

<query name="findUsersBySite">
<![CDATA[
select distinct u from DBUser u left outer join fetch u.userRoles ur left outer join fetch ur.role where ur.websiteId = :websiteid
]]>
</query>


</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:
com.scottwalter.yoursite.util.exceptions.MyDaoException: net.sf.hibernate.except
ion.SQLGrammarException: Could not save object
at com.scottwalter.yoursite.persistence.hibernate.UserDaoHibernate.saveU
ser(UserDaoHibernate.java:220)
at com.scottwalter.yoursite.security.db.DBSecurityManagerFactory.saveUse
r(DBSecurityManagerFactory.java:271)
at com.scottwalter.yoursite.service.YourSiteService.saveUser(YourSiteSer
vice.java:948)
at com.scottwalter.yoursite.service.AppManager.dbSetup(AppManager.java:7
54)
at com.scottwalter.yoursite.YourSiteListener.contextInitialized(YourSite
Listener.java:99)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContex
t.java:3827)
at org.apache.catalina.core.StandardContext.start(StandardContext.java:4
343)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase
.java:823)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:80
7)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:595)

at org.apache.catalina.core.StandardHostDeployer.addChild(StandardHostDe
ployer.java:903)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.apache.commons.beanutils.MethodUtils.invokeMethod(MethodUtils.jav
a:216)
at org.apache.commons.digester.SetNextRule.end(SetNextRule.java:256)
at org.apache.commons.digester.Rule.end(Rule.java:276)
at org.apache.commons.digester.Digester.endElement(Digester.java:1058)
at org.apache.catalina.util.CatalinaDigester.endElement(CatalinaDigester
.java:76)
at org.apache.xerces.parsers.AbstractSAXParser.endElement(Unknown Source
)
at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanEndElement(
Unknown Source)
at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl$FragmentContent
Dispatcher.dispatch(Unknown Source)
at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanDocument(Un
known Source)
at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
at org.apache.xerces.parsers.XMLParser.parse(Unknown Source)
at org.apache.xerces.parsers.AbstractSAXParser.parse(Unknown Source)
at org.apache.commons.digester.Digester.parse(Digester.java:1567)
at org.apache.catalina.core.StandardHostDeployer.install(StandardHostDep
loyer.java:488)
at org.apache.catalina.core.StandardHost.install(StandardHost.java:863)
at org.apache.catalina.startup.HostConfig.deployDescriptors(HostConfig.j
ava:483)
at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:427
)
at org.apache.catalina.startup.HostConfig.start(HostConfig.java:983)
at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java
:349)
at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(Lifecycl
eSupport.java:119)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1091)

at org.apache.catalina.core.StandardHost.start(StandardHost.java:789)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1083)

at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:478
)
at org.apache.catalina.core.StandardService.start(StandardService.java:4
80)
at org.apache.catalina.core.StandardServer.start(StandardServer.java:231
3)
at org.apache.catalina.startup.Catalina.start(Catalina.java:556)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:287)
at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:425)
Caused by: net.sf.hibernate.exception.SQLGrammarException: Could not save object

at net.sf.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConver
ter.java:69)
at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionH
elper.java:30)
at net.sf.hibernate.impl.SessionImpl.convert(SessionImpl.java:4110)
at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier(Session
Impl.java:792)
at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:747)
at net.sf.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:1397)

at com.scottwalter.yoursite.persistence.hibernate.UserDaoHibernate.saveU
ser(UserDaoHibernate.java:217)
... 48 more
Caused by: java.sql.SQLException: Table not found: user in statement [select max
(user_id) from "user"]
at org.hsqldb.jdbc.jdbcUtil.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
at net.sf.hibernate.id.IncrementGenerator.getNext(IncrementGenerator.jav
a:65)
at net.sf.hibernate.id.IncrementGenerator.generate(IncrementGenerator.ja
va:42)
at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier(Session
Impl.java:774)
... 51 more


Name and version of the database you are using:
Hypersonic 1.7.3

The generated SQL (show_sql=true):
2.1.7c

Debug level Hibernate log excerpt: