groovy-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Keith Suderman <suder...@anc.org>
Subject Re: Groovy SQL stored procedure result processing
Date Tue, 26 May 2015 14:02:57 GMT

On May 25, 2015, at 6:30 PM, Arthur Ramsey <arthur_ramsey@mediture.com> wrote:

> I'm using Groovy 2.4.3.  I saw that too.  I think it was merged in as callWithRows, callWithAllRows,
so I'm basically using a deviate of that work...
> 
> I read Sql.groovy and I'm pretty sure I'm correct in my understanding of the data available
in closure.
> 
> I think I should be able to replace the method with the attached to get what I want,
but get an error like I replaced the whole class rather just one method.  I'm totally new
to Groovy and Java, so I'm probably making a simple mistake.

> Exception in thread "main" groovy.lang.GroovyRuntimeException: Could not find matching
constructor for: com.mediture.truchart.analytics.Sql(org.codehaus.groovy.runtime.GStringImpl,
java.lang.String, java.lang.String, java.lang.String)


I suspect the GStringImpl object is the problem and I am willing to bet the constructor expects
a String as the first parameter.

In Java the String class cannot be extended so a Groovy String (GString) is not *really* a
Java String.   In "Groovy space" this typically isn't a problem, but it can lead to exceptions
like this when a Groovy String makes its way into Java-land (i.e. JDBC).  Fortunately the
solution is simple, just call the toString() method on the GString in the call to the constructor.
E.g.

def sql = new com.mediture.truchart.analytics.Sql("${my_var}".toString(), string, string,
string)

Keith


>     at groovy.lang.MetaClassImpl.invokeConstructor(MetaClassImpl.java:1723)
>     at groovy.lang.MetaClassImpl.invokeConstructor(MetaClassImpl.java:1526)
>     at org.codehaus.groovy.runtime.InvokerHelper.invokeConstructorOf(InvokerHelper.java:945)
>     at org.codehaus.groovy.runtime.DefaultGroovyMethods.newInstance(DefaultGroovyMethods.java:15623)
>     at org.codehaus.groovy.runtime.dgm$447.doMethodInvoke(Unknown Source)
>     at org.codehaus.groovy.runtime.callsite.StaticMetaMethodSite.invoke(StaticMetaMethodSite.java:43)
>     at org.codehaus.groovy.runtime.callsite.StaticMetaMethodSite.call(StaticMetaMethodSite.java:88)
>     at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:45)
>     at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:110)
>     at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:146)
> Thanks,
> Arthur
> 
> On 5/25/2015 4:00 PM, Keith Suderman wrote:
>> Unfortunately, you've reached the end of my limited SQL knowledge...
>> 
>> However, looking at this article (https://objectpartners.com/2014/01/24/simpler-stored-procedures-with-groovy/)
which has since been merged into Groovy (v2.3.0) it would seem that you should be able to
get output from an output parameters OR a ResultSet.  Maybe the article will lead you to a
solution.
>> 
>> Cheers,
>> Keith
>> 
>> On May 25, 2015, at 4:11 PM, Arthur Ramsey <arthur_ramsey@mediture.com> wrote:
>> 
>>> That's what I was trying initially, but all I get is null.  I think the closure
only gets output from an OUT parameter of the stored procedure.  I created a procedure that
used an OUT parameter and got values in the closure.  I don't maintain the procedures and
I'm trying to port the perl wrapper without changing the SQL.
>>> 
>>> I tried...
>>> 
>>> sql.callWithAllRows('{call spLoad_whse_subjectarea(?,?,?,?,?,?,?)}', [in_step,
run_step, in_step_title, in_procedure, in_current_stmt, in_start_data, in_run_count]) {
>>>     log.info(it.value.toString())
>>> }
>>> And...
>>> 
>>> sql.callWithAllRows '{call spLoad_whse_subjectarea(?,?,?,?,?,?,?)}', [in_step,
run_step, in_step_title, in_procedure, in_current_stmt, in_start_data, in_run_count], {
>>>     log.info(it.value.toString())
>>> }
>>> Either produce...
>>> java.lang.NullPointerException: Cannot get property 'value' on null object
>>>     at org.codehaus.groovy.runtime.NullObject.getProperty(NullObject.java:57)
>>>     at org.codehaus.groovy.runtime.InvokerHelper.getProperty(InvokerHelper.java:169)
>>>     at org.codehaus.groovy.runtime.callsite.NullCallSite.getProperty(NullCallSite.java:44)
>>>     at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callGetProperty(AbstractCallSite.java:293)
>>> On 5/25/2015 3:01 PM, Keith Suderman wrote:
>>>> The last parameter to sql.callWithAllRows() is a closure (empty in your example).
 I suspect this is where you want to include the logging.  The way you have it written you
are iterating over the list returned by the stored procedure (the list of list of GroovyRowResult),
which of course is only available after the stored procedure is complete.
>>>> 
>>>> Try
>>>> 
>>>> String proc = '{call spLoad_whse_subjectarea(?,?,?,?,?,?,?)}'
>>>> Object[] params = [ ... ] as Object[]
>>>> sql.callWithAllRows(proc, params) {
>>>>  log.info(it.value.toString()
>>>> }
>>>> 
>>>> Hope this helps.
>>>> 
>>>> Cheers,
>>>> Keith
>>>> 
>>>> On May 25, 2015, at 3:07 PM, arthur_ramsey@mediture.com wrote:
>>>> 
>>>>> Hello,
>>>>> 
>>>>> I'm new to Groovy and I'm trying to port the following perl DBI code
to Groovy SQL.  The code executes a stored procedure that produces multiple logging statements
as results (not an OUT parameter).
>>>>> 
>>>>> my $sth = $dbh->prepare("CALL spLoad_whse_subjectarea(?,true,?,?,?,?,?)");
>>>>> $sth->execute($_[0],$_[1],$_[2],$_[3],$_[4],$_[5]);
>>>>> do {
>>>>> 	while (@row = $sth->fetchrow_array) {
>>>>> 		foreach (@row) {
>>>>> 			print "$_ ";
>>>>> 		}
>>>>> 		print "\n";
>>>>> 	}
>>>>> } while ($sth->more_results);
>>>>> 
>>>>> The following Groovy code captures everything, but it blocks until the
stored procedure completes unlike the perl code which processes results prior to completion.
 The effect is delayed logging.
>>>>> 
>>>>> sql.execute('SET @data_history_units = ?', data_history_units)
>>>>> List<List<GroovyRowResult>> results = sql.callWithAllRows
'{call spLoad_whse_subjectarea(?,?,?,?,?,?,?)}', [in_step, run_step, in_step_title, in_procedure,
in_current_stmt, in_start_data, in_run_count], {}
>>>>> results.each { result ->
>>>>>     result.each { row ->
>>>>>         row.each {
>>>>>             log.info(it.value.toString())
>>>>>         }
>>>>>     }
>>>>> }
>>>>> I also tried sql.eachRow, but that seems to only get the first result.
 I had a similar outcome with perl DBI before I added the "while ($sth->more_results)".
 Is there anyway I can better emulate the perl code even if I have to interact with jdbc more
closely?
>>>>> 
>>>>> 
>>>>> 
>>>>> 
>>>>> 
>>>> 
>>>> ------------------------------
>>>> Research Associate
>>>> Department of Computer Science
>>>> Vassar College
>>>> Poughkeepsie, NY
>>>> 
>>>> 
>>> 
>>> 
>>> 
>> 
>> ------------------------------
>> Research Associate
>> Department of Computer Science
>> Vassar College
>> Poughkeepsie, NY
>> 
>> 
> 
> 
> 
> <Sql.groovy>

------------------------------
Research Associate
Department of Computer Science
Vassar College
Poughkeepsie, NY



Mime
View raw message