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 20:11:50 GMT
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
>
>



Mime
View raw message