Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Extend Groovy Sql with callWithRows method to call a Stored Procedure and process both Output Parameters and Rows from the ResultSet in the closure handler.Could be replaced if is ever completed
SqlHelper sql = new SqlHelper(dataSource)
List results = sql.callWithRows("{call ABC.FINDBYLAST($lastName, ${Sql.INTEGER}, ${Sql.VARCHAR})}") {
List<GroovyRowResult> rows, int status, String errorMessage ->
if (status != 0) {
throw new RuntimeException("Error received from stored proc $status : $errorMessage")
return rows
new Sql(dataSource).call("{call ABC.GetACount($lastName, ${Sql.INTEGER})}") { num ->
println "Num returned $num"
List results = new Sql(dataSource).rows("{call ABC.FindByFirst($firstName)}")
package com.sheetsj.sql
import groovy.sql.GroovyRowResult
import groovy.sql.OutParameter
import groovy.sql.Sql
import groovy.util.logging.Log4j
import java.sql.CallableStatement
import java.sql.Connection
import java.sql.SQLException
import javax.sql.DataSource
* An extension of groovy Sql class to add a method that can handle
* stored procedure calls with
* both output parameters and a ResultSet being returned
class SqlHelper extends Sql {
SqlHelper(DataSource dataSource) {
* Given a sql with params as GString variables,
* will call the given Closure with List of GroovyRowResults similar to Sql.rows()
* and the Output variables,
* and return the result of the closure
* @see
def callWithRows(GString queryWithParams, Closure closure) {
log.debug "Calling query $queryWithParams"
List params = getParameters(queryWithParams)
String query = asSql(queryWithParams, params)
Connection conn
CallableStatement cstmt
try {
conn = createConnection()
cstmt = conn.prepareCall(query);
setParameters(params, cstmt)
def hasResultSet = cstmt.execute()
//Copy resultSet into groovy resultSet rows
List<GroovyRowResult> rows = []
if (hasResultSet || cstmt.getMoreResults()) {
rows = asList(query, cstmt.getResultSet())
//Set output parameters with rows first
def outputResults = [rows]
params.eachWithIndex { param, i ->
if (param instanceof OutParameter) {
outputResults << cstmt.getObject(i + 1)
} catch (SQLException e) {
log.error("Failed to execute $queryWithParams", e)
throw e
} finally {
closeResources(conn, cstmt)
Copy link

I believe its available in Grails 2.4, both callWithRows and callWithAllRows
In case anyone needs it for older grails, here is an inline-procedure that makes parameter handling a little easier

def calculateTotals(map) {
//initialize variables
Double returnTotalOriginalOut = 0
Double returnTotalOtherOut = 0
Double returnTotalNetOut = 0

    def sql = new Sql(sessionFactory.currentSession.connection())
    //calculate the totals"""
                return_orig_chgs      number := 0;
                return_non_orig_chgs  number := 0;
                return_net_inst_chgs  number := 0;
               SCHEMA.NAME_PROCEDURE(id         => ${},
                                     term_in        => ${map.term},
                                     orig_chgs      => return_orig_chgs,
                                     non_orig_chgs  => return_non_orig_chgs,
                                     net_inst_chgs  => return_net_inst_chgs);
             ${Sql.DOUBLE} := return_orig_chgs;
             ${Sql.DOUBLE} := return_non_orig_chgs;
             ${Sql.DOUBLE} := return_net_inst_chgs;
            END ;
    """) { return_orig_chgs, return_non_orig_chgs, return_net_inst_chgs ->
        returnTotalOriginalOut = return_orig_chgs
        returnTotalOtherOut = return_non_orig_chgs
        returnTotalNetOut = return_net_inst_chgs

    def returnMap = [:]
    returnMap = [returnTotalOriginal: returnTotalOriginalOut, returnTotalOther: returnTotalOtherOut, returnTotalNet: returnTotalNetOut]
    return returnMap

Copy link

Thanks, and yes callWithRows was pulled into Groovy 2.3 and higher

Copy link

richelm commented Jun 2, 2016

I stumbled across this repo and have not found solution to my problem with callWithRows and callWithAllRows from Groovy Sql class. If there is a better place to ask question, let me know.

def rows = sql.callWithRows("sp_who ?",['32'],{})

I get:
Caught: java.lang.IndexOutOfBoundsException: Index: 0, Size: 0

def rowsList = sql.callWithAllRows("sp_who ?",['32'],{})
[ ]

Same problem as noted here:
groovy.sql.Sql callWithAllRows returns blank result when passing params

DBMS is Sybase ASE 15.7
jConnect JDBC driver: com.sybase.jdbc4.jdbc.SybDriver

sp_who is used for example. Any stored procedure with parameters gives same results.


Copy link

@richelm interesting, i wasn't aware of any bug until reading this now, though it has been a long time since I worked on this. Sorry that I don't have an answer and that I just stumbled across this now...

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment