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

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