grails app war pointing to two different datasources(oracle and SQL)

Question

I have my application in grails2.2.0. I want to deploy this application war deployed on apache tomcat7 with following settings :

First WAR with datasource to Oracle

Second WAR with data source to SQL.

While i do this with most supposed , setting it in the app-config.properties file, I get the following error for the WAR pointing to SQL when i run-app .

Caused by

BeanCreationException: Error creating bean with name 'sessionFactory':
 Cannot resolve reference to bean 'lobHandlerDetector' while setting bean proper
ty 'obHandler';

nested exception is org.springframework.beans.factory.BeanCreat
ionException: Error creating bean with name 'lobHandlerDetector': Invocation of
init method failed;

nested exception is org.springframework.jdbc.support.MetaDat
aAccessException: Error while extracting DatabaseMetaData;

nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot load JDBC driver     class 'com.mi
crosoft.sqlserver.jdbc.SQLServerDriver

Please assist as to how to fix this.

Answer 1

I would configure the DataSource for different environments. I understand you want the application instance to access only one database at a time. So in one war, the app will connect do SQL Server, and in the other instance war, the app will connect to Oracle. If this is the correct understanding, I would do the following in the DataSource.groovy file:

environments {
    sqlserver {
        dataSource {
            dbCreate = "none"
            url = "jdbc:mysql://localhost:3306/mydb"
            driverClassName = "com.mysql.jdbc.Driver"
            dialect = "org.hibernate.dialect.MySQL5InnoDBDialect"
            username = "dbowner"
            password = "xxxxx"
            logSql = false
            pooled = true
            properties {
                maxActive = 30 // -1 para sem limite
                minIdle = 1
                minEvictableIdleTimeMillis=1800000
                timeBetweenEvictionRunsMillis=1800000
                numTestsPerEvictionRun=3
                testOnBorrow=true
                testWhileIdle=true
                testOnReturn=true
                validationQuery="SELECT 1"
            }
        }
    }
    oracle {
        dataSource {
            dbCreate = "none" 
            url = "jdbc:mysql://localhost:3306/myotherdb"
            driverClassName = "com.mysql.jdbc.Driver"
            dialect = "org.hibernate.dialect.MySQL5InnoDBDialect"
            username = "dbowner"
            password = "xyz"
            logSql = false
            pooled = true
            properties {
                maxActive = 30 // -1 para sem limite
                minIdle = 1
                minEvictableIdleTimeMillis=1800000
                timeBetweenEvictionRunsMillis=1800000
                numTestsPerEvictionRun=3
                testOnBorrow=true
                testWhileIdle=true
                testOnReturn=true
                 validationQuery="SELECT 1"
            }
        }
    }
}

Now, when you want to generate the war file, just run the command:

grails -Dgrails.env=oracle war

or

grails -Dgrails.env=sqlserver war

Just make sure you include your drivers dependencies (oracle and sql server) in BuildConfig.groovy file:

dependencies {
    runtime 'your:sqlserver:dependency','your:oracle:dependency'
}