[GROOVY-3048] Groovy Sql API should support stored procedures that have output parameters and return a result set

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.6-beta-1
• Fix Version/s: 2.3.0-beta-1
• Component/s: SQL processing
• Assignee: Cédric Champeau
• Reporter: Benjamin Williams
• Created: 19/Sep/08 13:51
• Updated: 05/Apr/14 00:01
• Resolved: 03/Feb/14 04:00

Description

If you have a stored procedure that has output parameters AND returns one or more result sets, you can't get all of the output using the Groovy SQL API. You can get just the output parameters by doing something like:

sql.call("{ call someProc(?,?) }",
            [Sql.out(Sql.INTEGER.type),Sql.out(Sql.VARCHAR.type)]) { param1, param2 ->
    println "returned ${param1} ${param2}"
}

or you can get a single resultset but not the output parameters by doing something like this:

sql.query("{ call someProc(?,?) }",
            [null, null]) { resultSet ->
    println "returned ${resultSet}"
}

but you can't do both, and if the stored proc returns multiple result sets you are out of luck as well. The Sql.call method should probably use the CallableStatement getResultSet and getMoreResult methods to get all of the result sets and and pass them as parameters to the closure.