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 Tue, 26 May 2015 22:21:58 GMT
See responses below...

On 05/26/2015 04:05 PM, Nelson, Erick [HDS] wrote:
> What Perl database (DBD) is this?
DBD::mysql
> Perl drivers (DBD) can be more robust that jdbc thin drivers as they link directly to
binaries and because of this can  perform more 'magic' than jdbc thin connections.
> Example:  Perl Oracle DBD could easily grab dbms_output data, while I'm not so sure jdbc
thin can.
> Your code looks to me like you are calling a stored procedure that somehow returns a
result set. DB2 maybe? If so I'm not sure this translates well to straight sql on a thin connection.
Correct, it returns multiple result sets (single column logging statements).
> Also, your statement...
>     sql.execute('SET @data_history_units = ?',data_history_units)
> ... looks very unsql to me.
Indeed, I'm passing variables set by a property file to the stored 
procedure via a session variable.  Its pretty kludgey, but it works.  
The whole thing is pretty kludgey...
>
> -----Original Message-----
> From: Paul King [mailto:paulk@asert.com.au]
> Sent: Tuesday, May 26, 2015 1:52 PM
> To: users@groovy.incubator.apache.org
> Subject: Re: Groovy SQL stored procedure result processing
>
>
> Can you show us how you used eachRow?
>
> Cheers, Paul.
>
> On 27/05/2015 1:05 AM, Arthur Ramsey wrote:
>> I tried eachRow and only get the first row.  No there are no OUT params.
>>
>> On 5/26/2015 3:20 AM, Paul King wrote:
>>> I am not a Perl expert but from what I see here, you should only need to use
>>> rows or eachRow. Does the stored procedure definition have any OUT params?
>>> It doesn't look like it from the Perl.
>>>
>>> Cheers, Paul.
>>>
>>> On 26/05/2015 5:07 AM, 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?
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>> ---
>>> This email has been checked for viruses by Avast antivirus software.
>>> https://urldefense.proofpoint.com/v2/url?u=http-3A__www.avast.com&d=BQICaQ&c=_8VcuiJ--MukFqz6Sy5gel64o52_IbhiNdatg8Zb5Gs&r=X18JEC7WoH6AE_Xb-Yc-vOlw3Sre-zHIm9sFtf9hiJM&m=0gI06Sp_4lI6SDZUZG4FMT4QZtIDKHJOK5GpFFyB_is&s=2aQb0vBM1q4paWexQkRSBHgTXcIItpePxT89a3m8qTU&e=
>>>
>>
>>
>>
>
> ---
> This email has been checked for viruses by Avast antivirus software.
> https://urldefense.proofpoint.com/v2/url?u=http-3A__www.avast.com&d=BQICaQ&c=_8VcuiJ--MukFqz6Sy5gel64o52_IbhiNdatg8Zb5Gs&r=X18JEC7WoH6AE_Xb-Yc-vOlw3Sre-zHIm9sFtf9hiJM&m=0gI06Sp_4lI6SDZUZG4FMT4QZtIDKHJOK5GpFFyB_is&s=2aQb0vBM1q4paWexQkRSBHgTXcIItpePxT89a3m8qTU&e=
>

-- 
Arthur Ramsey
Systems Administrator
Mediture
arthur_ramsey@mediture.com
952.400.0323

This e-mail and any attachments may contain CONFIDENTIAL information, including PROTECTED
HEALTH INFORMATION. If you are not the intended recipient, any use or disclosure of this information
is STRICTLY PROHIBITED; you are requested to delete this e-mail and any attachments, notify
the sender immediately, and notify the Mediture Privacy Officer at privacyofficer@mediture.com.




Mime
View raw message