Create Table in 2nd DataStore in beforeInsert Method of Domain Failed by NPE

Question

I use run-time datasource plugin to have 2nd datasource and create dynamic table in run-time by having a domain named TableDefinition to keep table information in primary datasource, but before saving this domain instance I want to create actual table on 2nd datasource, but it failed with NullPointerException, while the actual table is created in 2nd DB but domain instance is not persisted.

2nd DataSource Service Execution

class DataConnectionService {

  def runtimeDataSource 
  static transactional = true

  def execute( String query, String dsName )
  {
    try
    {
        Sql sql =  runtimeDataSource.getSql( dsName )
        return sql.execute( query )
    }
    catch ( Exception e )
    {
        log.error( "Error in executing query $query [$e.message]", e )
        return null
    }
    finally
    {
        sql.close()
    }
  }
}

beforeInsert method of domain

def beforeInsert()
{
    try {
        String q = "Create Table test ...."
        dataConnectionService.execute( q, dsName )
    }
    catch( e )
    {
        log.error "Creating table in 2nd db failed [$e.message]"
        return false
    }
}

The exception stack is like:

->>  163 | removeBatchLoadableEntityKey    in org.hibernate.engine.spi.BatchFetchQueue
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
|    388 | addEntity                       in org.hibernate.engine.internal.StatefulPersistenceContext
|    461 | addEntity . . . . . . . . . . . in     ''
|    143 | makeEntityManaged               in org.hibernate.action.internal.AbstractEntityInsertAction
|    203 | addResolvedEntityInsertAction . in org.hibernate.engine.spi.ActionQueue
|    181 | addInsertAction                 in     ''
|    216 | addAction . . . . . . . . . . . in     ''
|    324 | addInsertAction                 in org.hibernate.event.internal.AbstractSaveEventListener
|    288 | performSaveOrReplicate . . . .  in     ''
|    194 | performSave                     in     ''
|    125 | saveWithGeneratedId . . . . . . in     ''
|    209 | saveWithGeneratedOrRequestedId  in org.hibernate.event.internal.DefaultSaveOrUpdateEventListener
|    194 | entityIsTransient . . . . . . . in     ''
|    114 | performSaveOrUpdate             in     ''
|     90 | onSaveOrUpdate . . . . . . . .  in     ''
|    684 | fireSaveOrUpdate                in org.hibernate.internal.SessionImpl
|    676 | saveOrUpdate . . . . . . . . .  in     ''
|    671 | saveOrUpdate                    in     ''
|     58 | doInHibernate . . . . . . . . . in org.codehaus.groovy.grails.orm.hibernate.metaclass.SavePersistentMethod$1
|    188 | doExecute                       in org.codehaus.groovy.grails.orm.hibernate.GrailsHibernateTemplate
|    132 | execute . . . . . . . . . . . . in     ''
|     56 | performSave                     in org.codehaus.groovy.grails.orm.hibernate.metaclass.SavePersistentMethod
|    215 | doInvokeInternal . . . . . . .  in org.codehaus.groovy.grails.orm.hibernate.metaclass.AbstractSavePersistentMethod
|     69 | invoke                          in org.codehaus.groovy.grails.orm.hibernate.metaclass.AbstractDynamicPersistentMethod

Answer 1

After bruteforcing different options about transaction and query, the following combination allowed successfull query execution and saving the domain instance:

static transactional = false

sql.executeUpdate( query )

It will be great if anybody can provide some reason about this behavior or any better optimized alternative.