Parse out parmeters from groovy sql

[Rajesh Santhanakrishnan]

Hi
I am calling a stored procedure using groovy sql to fetch the output result set and output parameters. Below is my high level code. When i am trying to print my output parameter,I am not getting the output as a readable object. Any thoughts on how can I parse the output parameter.

def params = [
        Sql.INTEGER(500), //input parm to stored proc
        Sql.SMALLINT(1),  //input parm to stored proc
        Sql.INTEGER       //output parm to stored proc
    ]
def sqlCall = "{CALL SP1234(?,?,?)}" //prepare stored proc call statement
List<GroovyRowResult> rows = sql.execute(sqlCall, params) //invoke stored proc

for (GroovyRowResult rs : rows) {
//logic to iterate result set - able to get output records
}

println "params1 "+params[2].value //expecting integer output from stored proc but not able to get the output value
println "params2 "+params.get(2) //expecting integer value from store proc output but getting a data not in readable format.

[Knute Snortum]

What is the output you get from the two printlns?

[Rajesh Santhanakrishnan]

For params1 , I getting the value as groovy.sql.Sql$7@3ec4d8f6
For params2 , I am getting the below exception

stack_trace":"groovy.lang.MissingPropertyException: 
No such property: value for class: groovy.sql.Sql$7\n\tat org.codehaus.groovy.runtime.ScriptBytecodeAdapter.unwrap(ScriptBytecodeAdapter.java:53) 
~[groovy-all-2.4.6-indy.jar:2.4.6]\n\tat org.codehaus.groovy.vmplugin.v7.IndyGuardsFiltersAndSignatures.unwrap(IndyGuardsFiltersAndSignatures.java:177) 
~[groovy-all-2.4.6-indy.jar:2.4.6]\n\tat org.codehaus.groovy.vmplugin.v7.IndyInterface.selectMethod(IndyInterface.java:228)
 ~[groovy-all-2.4.6-indy.jar:2.4.6]\n\tat com.etc.MyDAO.getSPValues(MyDAO.groovy:61) 

[Knute Snortum]

For params1 , I getting the value as groovy.sql.Sql$7@3ec4d8f6

This means that the object groovy.sql.Sql does not have its own toString() method and it is using Object's (all classes inherit from Object).

For params2 , I am getting the below exception

This means that you're calling the method get() on an array, and there is no such method.

It will help to know exactly what you're trying to do, in English (that is, not in "programmer talk").

[Rajesh Santhanakrishnan]

Thanks for the response Knute.
The stored procedure is returning the value 5 in the output parameter . I am trying to get the value from my groovy dao layer.
I tried different parsing options for the params , but nothing seems to work out

[Knute Snortum]

for (GroovyRowResult rs : rows) {
//logic to iterate result set - able to get output records
}

I believe you would use rs inside the for-loop. Or am I missing something?

[Rajesh Santhanakrishnan]

My stored procedure, gives output both as result set and in output parameter.
Yes, I am using rs inside the for loop to get the result set output , which is working fine.
To get the output parameter values, I am not able to get it.
Note:When I print input parameter values using .value , it works fine.

[Rajesh Santhanakrishnan]

Got a solution. It looks like the groovy version 2.4.6 does not support both output parms and resultset.
The fix is available in 2.4.7 with callwithRows() method.

https://objectpartners.com/2014/01/24/simpler-stored-procedures-with-groovy/
http://docs.groovy-lang.org/latest/html/gapi/groovy/sql/Sql.html

Thanks knute for the help provided so far