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)
		}
	}
}

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