SQLGrammarException using Hibernate + Embedded Derby

[James Adams]

I have a Spring application which uses an embedded Derby database for unit testing.

Below is my DataSource configuration and Hibernate properties:

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="username" value="sa"/>
        <property name="password" value=""/>
        <property name="url" value="jdbc :mrgreen: erby:testderby;create=true"/>
        <property name="driverClassName" value="org.apache.derby.jdbc.EmbeddedDriver" />
    </bean>
 
    <!-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -->
    <!-- Factory bean for Hibernate Sessions -->
    <!-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -->
    <bean id="hibernateSessionFactory"
          class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
 
        <property name="dataSource" ref="dataSource"/>
 
        <!-- tell the factory which classes are annotated -->
        <property name="annotatedClasses">
            <list>
                <value>com.xxxx.network.lifecycle.persistence.entity.ServiceHost</value>
                            </list>
        </property>
 
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.dialect">org.hibernate.dialect.DerbyDialect</prop>
                <prop key="hibernate.show_sql">true</prop>
                <prop key="hibernate.format_sql">true</prop>
                <prop key="hibernate.use_sql_comments">true</prop>
                <prop key="hibernate.jdbc.batch_size">1000</prop>
                <prop key="hibernate.query.substitutions">true 1, false 0</prop>
                <prop key="hibernate.max_fetch_depth">6</prop>
            </props>
        </property>
    </bean>
    

I get the following error when I start a DAO test:

WARNING: SQL Warning: 10000, SQLState: 01J01
Dec 17, 2008 11:17:45 AM org.hibernate.util.JDBCExceptionReporter logWarnings
WARNING: Database 'testderby' not created, connection made to existing database instead.
Hibernate: 
    /* insert com.xxxx.network.lifecycle.persistence.entity.ServiceHost
        */ insert 
        into
            SERVICE_HOST
            (CONNECTION_DETAILS_ID, DESCRIPTION, HARDWARE_CONFIG_ID, HARDWARE_DESCRIPTION, HOST_STATUS, HOST_TYPE, LOM_MAC_ADDRESS, LOM_SWITCH_PORT, MAC_ADDRESS, NAME, PHYSICAL_LOCATION, SWITCH_PORT, SERVICE_HOST_ID) 
        values
            (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Dec 17, 2008 11:17:46 AM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 20000, SQLState: 42X01
Dec 17, 2008 11:17:46 AM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: Syntax error: Encountered "/" at line 1, column 1.
Dec 17, 2008 11:17:46 AM org.hibernate.event.def.AbstractFlushingEventListener performExecutions
SEVERE: Could not synchronize database state with session
org.hibernate.exception.SQLGrammarException: could not insert: [com.xxxx.network.lifecycle.persistence.entity.ServiceHost]
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2267)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2660)
        at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:56)
        at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:250)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:234)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:141)
        at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
        at org.hibernate.event.def.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:41)
        at org.hibernate.impl.SessionImpl.autoFlushIfRequired(SessionImpl.java:969)
        at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1114)
        at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
        at org.springframework.orm.hibernate3.HibernateTemplate$29.doInHibernate(HibernateTemplate.java:856)
        at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:373)
        at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:847)
        at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:839)
        at com.xxxx.network.lifecycle.persistence.dao.GenericDaoHibernateImpl.findAll(GenericDaoHibernateImpl.java:99)
        at com.xxxx.network.lifecycle.persistence.dao.ServiceHostDaoTest.testSaveDeleteFindAll(ServiceHostDaoTest.java:50)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at junit.framework.TestCase.runTest(TestCase.java:168)
        at junit.framework.TestCase.runBare(TestCase.java:134)
        at org.springframework.test.ConditionalTestCase.runBare(ConditionalTestCase.java:76)
        at junit.framework.TestResult$1.protect(TestResult.java:110)
        at junit.framework.TestResult.runProtected(TestResult.java:128)
        at junit.framework.TestResult.run(TestResult.java:113)
        at junit.framework.TestCase.run(TestCase.java:124)
        at junit.framework.TestSuite.runTest(TestSuite.java:232)
        at junit.framework.TestSuite.run(TestSuite.java:227)
        at org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.run(JUnitTestRunner.java:515)
        at org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.launch(JUnitTestRunner.java:1031)
        at org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.main(JUnitTestRunner.java:888)
    

Can anyone suggest what might be causing the above? Thanks in advance for any suggestions.

--James

[James Adams]

I ended up getting around this error by putting the following into my Hibernate properties:

<prop key="hibernate.use_sql_comments">false</prop>

It seems that the Derby Hibernate dialect can't handle comments in the SQL.

--James