CODEWITHSUNDEEP
hibernategrailsh2
Steve
Steve

Reputation: 1467

Grails throws Table "xxx" not found

In Grails I can create domain objects to a H2 in memory dataSource in BootStrap and get results back ok, but once the app is up (eg query from GSP or controller) and I try to run a query I get this: 

org.h2.jdbc.JdbcSQLException: Table "FUNCTIONAL_DOC_TYPE" not found; SQL statement:
select this_.id as id1_0_, this_.version as version1_0_, this_.direction_id as direction3_1_0_, this_.functional_group_id as functional4_1_0_, this_.type_name as type5_1_0_ from functional_doc_type this_ [42102-147]
 at org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
 at org.h2.message.DbException.get(DbException.java:167)
 at org.h2.message.DbException.get(DbException.java:144)
 at org.h2.command.Parser.readTableOrView(Parser.java:4562)
 at org.h2.command.Parser.readTableFilter(Parser.java:1020)
 at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1622)
 at org.h2.command.Parser.parseSelectSimple(Parser.java:1729)
 at org.h2.command.Parser.parseSelectSub(Parser.java:1616)
 at org.h2.command.Parser.parseSelectUnion(Parser.java:1461)
 at org.h2.command.Parser.parseSelect(Parser.java:1449)
 at org.h2.command.Parser.parsePrepared(Parser.java:401)
 at org.h2.command.Parser.parse(Parser.java:275)
 at org.h2.command.Parser.parse(Parser.java:247)
 at org.h2.command.Parser.prepare(Parser.java:201)
 at org.h2.command.Parser.prepareCommand(Parser.java:214)
 at org.h2.engine.Session.prepareLocal(Session.java:425)
 at org.h2.engine.Session.prepareCommand(Session.java:374)
 at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1056)
 at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:71)
 at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:233)
 at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:281)
 at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:313)
 at org.grails.datastore.gorm.GormStaticApi.findAll(GormStaticApi.groovy:374)

Some source:

Datasource.groovy

// "Parent" datasource def

dataSource_messages {
    pooled = true
    driverClassName = "net.sourceforge.jtds.jdbc.Driver"
    username = "user"
    password = "secret"
    readOnly = "true"
}


// environment specific settings
environments {
    development {
        dataSource {
            dbCreate = "create-drop" // one of 'create', 'create-drop', 'update', 'validate', ''
            url = "jdbc:h2:mem:app_data;MVCC=TRUE"
            pooled = true
            driverClassName = "org.h2.Driver"
            username = "sa"
            password = ""
            readOnly = false
        }
        dataSource_messages {
            url = "jdbc:jtds:sqlserver://localhost:1433;DatabaseName=Messages"
        }

Domain class:

class FunctionalDocType {
    String typeName
    FunctionalGroup functionalGroup
    DocDirection direction

    static constraints = {
        functionalGroup(nullable: true)
    }

}

Bootstrap.groovy:

   new FunctionalDocType(typeName: 'Order',   direction: buyerToSeller, functionalGroup: orders).save().save(flush: true)

   //  insert into functional_doc_type (id, version, direction_id, functional_group_id, type_name) values (null, ?, ?, ?, ?)


   FunctionalDocType.findAll() runs:

    // DEBUG SQL - select this_.id as id1_0_, this_.version as version1_0_, this_.direction_id as direction3_1_0_, this_.functional_group_id as functional4_1_0_, this_.type_name as     type5_1_0_ from functional_doc_type this_

and all correct values are returned.

So its like the in memory tabes are being dropped after bootstrap somewhere but this is not being logged.

Any help appreciated.

Thanks,

Steve

Upvotes: 2

Views: 4660

Answers (2)

Thomas Mueller
Thomas Mueller

Reputation: 50087

H2 closes the database when the last connection is closed. For an in-memory database, closing the connection means the data is lost...

So if you keep one connection open all the time, then you should be fine. You could call this a 'sentinel' connection.

Another option is to use a persistent database (database URL jdbc:h2:~/test/app_data;MVCC=TRUE)

Upvotes: 5

Firo
Firo

Reputation: 30803

most inmemory database tie the database to the connection used. Hibernate opens a connection for each session so only the session with the SchemaExport has the database with the correct schema and is disposed immediatly. to solve this you have to use the same connection for all sessions which access the inmemory database.

connection = session.getConnection();

sessionfactory.openSession(connection);

Upvotes: 2

Related Questions