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 Mon, 25 May 2015 20:01:28 GMT
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



Mime
View raw message