groovy-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Arthur Ramsey <arthur_ram...@mediture.com>
Subject Re: Groovy SQL stored procedure result processing
Date Mon, 25 May 2015 22:30:20 GMT
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)
     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 
> <mailto: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 
>>> <mailto: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
>
>



Mime
View raw message