CODEWITHSUNDEEP
groovy
Drake
Drake

Reputation: 2451

how to run stored procedure from groovy that returns multiple resultsets

I couldnt find any good example of doing this online.

Can someone please show how to run a stored procedure (that returns multiple resultsets) from groovy?

Basically I am just trying to determine how many resultsets the stored procedure returns..

Upvotes: 3

Views: 3776

Answers (3)

lance-java
lance-java

Reputation: 27994

I have written a helper which allows me to work with stored procedures that return a single ResultSet in a way that is similar to working with queries with groovy.sql.Sql. This could easily be adapted to process multiple ResultSets (I assume each would need it's own closure).

Usage:

Sql sql = Sql.newInstance(dataSource)
SqlHelper helper = new SqlHelper(sql);
helper.eachSprocRow('EXEC sp_my_sproc ?, ?, ?', ['a', 'b', 'c']) { row ->
    println "foo=${row.foo}, bar=${row.bar}, baz=${row.baz}"
}

Code:

class SqlHelper {
    private Sql sql;

    SqlHelper(Sql sql) {
        this.sql = sql;
    }

    public void eachSprocRow(String query, List parameters, Closure closure) {
        sql.cacheConnection { Connection con ->
            CallableStatement proc = con.prepareCall(query)
            try {
                parameters.eachWithIndex { param, i ->
                    proc.setObject(i+1, param)
                }

                boolean result = proc.execute()
                boolean found = false
                while (!found) {
                    if (result) {
                        ResultSet rs = proc.getResultSet()
                        ResultSetMetaData md = rs.getMetaData()
                        int columnCount = md.getColumnCount()
                        while (rs.next()) {
                            // use case insensitive map
                            Map row = new TreeMap(String.CASE_INSENSITIVE_ORDER)
                            for (int i = 0; i < columnCount; ++ i) {
                                row[md.getColumnName(i+1)] = rs.getObject(i+1)
                            }
                            closure.call(row)
                        }
                        found = true;
                    } else if (proc.getUpdateCount() < 0) {
                        throw new RuntimeException("Sproc ${query} did not return a result set")
                    }
                    result = proc.getMoreResults()
                }
            } finally {
                proc.close()
            }
        }
    }
}

Upvotes: 2

Greg Stewart
Greg Stewart

Reputation: 686

I just stumbled across what could possibly be a solution to your problem, if an example was what you were after, have a look at the reply to this thread

Upvotes: 0

Andrey Adamovich
Andrey Adamovich

Reputation: 20663

All Java classes are usable from Groovy. If Groovy does not give you a way to do it, then you can do it Java-way using JDBC callable statements.

Upvotes: 0

Related Questions