in Grails can you use both sql.newInstance and Domain class calls in the same method?

Question

Can you have code that looks like this?

def methodname ()
{
proc = "sql to call a stored proc"
def conn = Sql.newInstance(DB_CONN, DB_USERNAME, DB_PASSWORD, "org.postgresql.Driver")
def result1 = conn.rows(proc)
def result2 = MyClass.Find("from MyClass where foo='bar'")
return [result1, result2]
}

If so are they using different connections? Is there any way to get the call to the stored proc to use the same connection as the MyClass call?

Answer 1

They will use different connections. Sql will grab a new one and the GORM call will use one from the connection pool managed by the DataSource. But you can get Sql to use the same connection as the GORM call by using sessionFactory.currentSession.connection():

class MyControllerOrMyService {

   def sessionFactory

   def methodname() {
      proc = "sql to call a stored proc"
      def conn = new Sql(sessionFactory.currentSession.connection())
      def result1 = conn.rows(proc)
      def result2 = MyClass.Find("from MyClass where foo='bar'")
      return [result1, result2]
   }
}

Answer 2

See http://jira.codehaus.org/browse/GRAILS-5454 .

Answer 3

They will be different connections. This is a standard issue with Grails in that you can only have one DataSource.groovy and so only one linked to GORM. There are ways around this but it is mostly a matter of what you need at the time.

If you wanted to connect to a entirely different DataSource you could set up something like this in your Config.groovy (not perfect but effective enough).

environments {
    production {
        grails.serverURL = "http://localhost:8080/${appName}"
        grails.databaseDriverClassName = "oracle.jdbc.driver.OracleDriver"
        grails.databaseURL = "jdbc:oracle:thin:@<servername>:1521:<sid>"
        grails.databaseUsername = "<username>"
        grails.databasePassword = "<password>"
    }
    development {
        grails.serverURL = "http://localhost:8080/${appName}"
        grails.databaseDriverClassName = "oracle.jdbc.driver.OracleDriver"
        grails.databaseURL = "jdbc:oracle:thin:@<servername>:1521:<sid>"
        grails.databaseUsername = "<username>"
        grails.databasePassword = "<password>"
    }
    test {
        grails.serverURL = "http://localhost:8080/${appName}"
        grails.databaseDriverClassName = "oracle.jdbc.driver.OracleDriver"
        grails.databaseURL = "jdbc:oracle:thin:@<servername>:1521:<sid>"
        grails.databaseUsername = "<username>"
        grails.databasePassword = "<password>"
   }
}

Then in your service simply call it like so:

import org.codehaus.groovy.grails.commons.*


class SomeService {

    boolean transactional = true
    def config = ConfigurationHolder.config

    // set up the Sql object
    def sql = groovy.sql.Sql.newInstance(
            config.grails.databaseURL,
            config.grails.databaseUsername,
            config.grails.databasePassword,
            config.grails.databaseDriverClassName)