Search code examples
grailsgrails-controller

Call Stored Procedure in Grails (Step by step)

Can anyone tell me how to call stored procedure from Grails which is present in User-defined path.

Solution

  • For that purpose you can use Groovy Sql.

    To use Groovy SQL:

    1. import groovy.sql.Sql
    2. Request a reference to the datasource with def dataSource or def sessionFactory for transactions
    3. Create an Sql object using def sql = new Sql(dataSource) or def sql = new Sql(sessionFactory.currentSession.connection())
    4. Use Groovy SQL as required

    Grails will manage the connection to the datasource automatically.

    Note: dataSource and sessionFactory are beans that you would have to inject in your pojo/bean class.

    So to execute sql code written on your file:

    String sqlFilePath = grailsApplication.parentContext.servletContext.getRealPath("/data/proc.sql")
String sqlString = new File(sqlFilePath).text

Sql sql = new Sql(sessionFactory.currentSession.connection())
sql.execute(sqlString)

    This will execute any sql statements written in your file on your sql server.