How to start a stored procedure from groovy that returns multiple result sets

Question

I could not find a good example of this online.

Can anyone show how to start a stored procedure (which returns multiple result sets) from groovy?

Basically, I'm just trying to determine how many results a stored procedure returns.

Answer 1

I wrote a helper that allows me to work with stored procedures that return a single ResultSet in a way that is similar to working with queries using groovy.sql.Sql. This can be easily adapted to handle multiple ResultSets (I assume that each of them will require its own closure).

Using:

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

Answer 2

Java Groovy. Groovy , Java-, JDBC .

Answer 3

I just stumbled on what could be the solution to your problem, if you were an example, look at the answer to this question thread