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 http://jira.codehaus.org/browse/GROOVY-3048 is ever completed

GroovySqlWithOutputsAndResultSetRows.groovy

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
}

GroovySqlWithOutputsExample.groovy

new Sql(dataSource).call("{call ABC.GetACount($lastName, ${Sql.INTEGER})}") { num ->
  println "Num returned $num"
}

GroovySqlWithResultSetRows.groovy

List results = new Sql(dataSource).rows("{call ABC.FindByFirst($firstName)}")

SqlHelper.groovy

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
 */
@Log4j
class SqlHelper extends Sql {

	SqlHelper(DataSource dataSource) {
		super(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 http://jira.codehaus.org/browse/GROOVY-3048
	 */
	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)
				}
			}

			return closure.call(outputResults.toArray())
		} catch (SQLException e) {
			log.error("Failed to execute $queryWithParams", e)
			throw e
		} finally {
			closeResources(conn, cstmt)
		}
	}
}

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
    sql.call("""
             DECLARE
                return_orig_chgs      number := 0;
                return_non_orig_chgs  number := 0;
                return_net_inst_chgs  number := 0;
             BEGIN
               SCHEMA.NAME_PROCEDURE(id         => ${map.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
}

Thanks, and yes callWithRows was pulled into Groovy 2.3 and higher
https://jira.codehaus.org/browse/GROOVY-3048

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.

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

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

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

Same problem as noted here:
https://issues.apache.org/jira/browse/GROOVY-7768
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.

shawn

@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...