HSQL org.hsqldb.HsqlException: invalid schema name

Question

I am using HSQL to run a number of unit tests on my java application. I am using Spring + Hibernate. I am having a problem when switching from MySQL to HSQL. The tests run perfectly on MySQL but whenever I change to HSQL I get the following exception:

Caused by: org.hsqldb.HsqlException: invalid schema name: LMS
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.SchemaManager.getSchemaHsqlName(Unknown Source)
at org.hsqldb.SchemaManager.getSchemaName(Unknown Source)
at org.hsqldb.Session.getSchemaName(Unknown Source)
at org.hsqldb.SchemaManager.getTable(Unknown Source)
at org.hsqldb.ParserDQL.readTableName(Unknown Source)
at org.hsqldb.ParserDQL.readSimpleRangeVariable(Unknown Source)
at org.hsqldb.ParserDML.compileInsertStatement(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
at org.hsqldb.Session.compileStatement(Unknown Source)
at org.hsqldb.StatementManager.compile(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)

My Spring configuration is the following:

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="org.hsqldb.jdbc.JDBCDriver" />
    <property name="url" value="jdbc:hsqldb:file:lms" />
    <property name="username" value="SA"/>
    <property name="password" value=""/>
</bean>


<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />

    <property name="packagesToScan">
        <list>
            <value>com.dreamteam.lms.**.*</value>
        </list>
    </property>

    <property name="hibernateProperties">
        <props>
            <!--<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>-->
            <prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop>
            <prop key="hibernate.generate_statistics">true</prop>
            <prop key="hibernate.show_sql">false</prop>
            <prop key="hibernate.cache.use_second_level_cache">true</prop>
            <prop key="hibernate.cache.use_query_cache">true</prop>
            <prop key="hibernate.cache.provider_class">net.sf.ehcache.hibernate.SingletonEhCacheProvider</prop>
        </props>
    </property>
</bean>

Sample Annotation on one of my classes:

@Entity
@Table(name = "answer", catalog = "lms")
public class Answer implements Cloneable, Serializable, IPojoGenEntity, IAnswer {
.
.

Any insight would be appreciated.

Regards Chris

Maybe a case issue ? `LMS` vs `lms` – ndeverge Feb 25 '12 at 18:07 — ndeverge, Feb 25 '12 at 18:07

박찬신 · Answer 1 · 2013-08-15T14:26:31.490

make "create-schema.sql" file

CREATE SCHEMA lms;

add "dataSourceInitializer" bean

<bean id="dataSourceInitializer" class="org.springframework.jdbc.datasource.init.DataSourceInitializer">
    <property name="dataSource" ref="dataSource" />
    <property name="databasePopulator">
        <bean class="org.springframework.jdbc.datasource.init.ResourceDatabasePopulator">
            <property name="continueOnError" value="true" />
            <property name="scripts">
                <list>
                    <value>classpath:SQL/create-schema.sql</value>
                </list>
            </property>
        </bean>
    </property>
</bean>

set "depends-on" attribute to "sessionFactory" bean

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean" depends-on="dataSourceInitializer">
...

score 5 · Answer 2 · edited Jun 30 '14 at 08:45

I use following bean to create schema during tests.

public class HSQLSchemaCreator {

    private String schemaName;

    private DataSource dataSource;

    public HSQLSchemaCreator(String schemaName, DataSource dataSource) {
        this.schemaName = schemaName;
        this.dataSource = dataSource;
    }


    @PostConstruct
    public void postConstruct() throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.execute("CREATE SCHEMA " + schemaName + " AUTHORIZATION DBA");
    }

}

spring configuration:

  <bean id="hsqlSchemaCreator" class="....HSQLSchemaCreator">
       <constructor-arg name="schemaName" value="..."/>
       <constructor-arg name="dataSource" ref="dataSource"/>
  </bean>

<!-- Override entityManagerFactory to depend on hsqlSchemaCreator for tests  -->
<bean id="entityManagerFactory"  class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" depends-on="hsqlSchemaCreator">

and so on...

Hi, is there a way to move the xml configuration into java-config (annotations)? Maybe for Spring Data? Thx! — user2722077, Mar 12 '18 at 21:31

score 4 · Answer 3 · answered Apr 01 '12 at 01:00

4

Just for the record I managed to solve this one by simply removing the 'catalog' attribute from my Hibernate entities. Hence,

@Entity
@Table(name = "answer", catalog = "lms")

became

@Entity
@Table(name = "answer")

answered Apr 01 '12 at 01:00

Kros

848
1
10
24

Thank you. I had exact same issue and this resolved it. My catalog params were in lower case, but it tells me invalid schema name in upper case, therefore perhaps there was a case conflict. – Psyrus Mar 18 '14 at 15:43

score 2 · Answer 4 · edited Aug 31 '22 at 11:18

2

Names for schemas, tables, columns, etc. are not (at least not by default) case sensitive in MySQL. HSQLDB is case sensitive, but it also converts all identifiers in query that are not quoted to the uppercase.

You can quickly test is this your problem by changing schema name to LMS everywhere (first in database). You can find more detailed story about HSQLDB and Hibernate from here: HSQLDB No such table Exception

edited Aug 31 '22 at 11:18

Dave Moten

11,957
2
40
47

answered Feb 25 '12 at 19:14

Mikko Maunu

41,366
10
132
135

Thanks for your comment however, this was not successful. I have changed all references to 'LMS'. I am starting to believe that the error message might be a misleading one. Next step, when I get some time will be to download the HSQL source code and debug. – Kros Feb 27 '12 at 00:34

score 0 · Answer 5 · answered Aug 14 '18 at 21:18

0

I am not sure why this worked, but for me, at least, adding square brackets around the table names and schema did the trick for me:

@Table(name = "schema.tableName")

became

@Table(name = "[schema].[tableName]")

answered Aug 14 '18 at 21:18

user7396627

105
2
8

HSQL org.hsqldb.HsqlException: invalid schema name

5 Answers5