Return-Path: X-Original-To: apmail-groovy-users-archive@minotaur.apache.org Delivered-To: apmail-groovy-users-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id F3AB618F3C for ; Tue, 26 May 2015 20:37:48 +0000 (UTC) Received: (qmail 72737 invoked by uid 500); 26 May 2015 20:37:48 -0000 Delivered-To: apmail-groovy-users-archive@groovy.apache.org Received: (qmail 72700 invoked by uid 500); 26 May 2015 20:37:48 -0000 Mailing-List: contact users-help@groovy.incubator.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: users@groovy.incubator.apache.org Delivered-To: mailing list users@groovy.incubator.apache.org Received: (qmail 72690 invoked by uid 99); 26 May 2015 20:37:48 -0000 Received: from Unknown (HELO spamd4-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 26 May 2015 20:37:48 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd4-us-west.apache.org (ASF Mail Server at spamd4-us-west.apache.org) with ESMTP id 3DDBCC093B for ; Tue, 26 May 2015 20:37:48 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd4-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 2.991 X-Spam-Level: ** X-Spam-Status: No, score=2.991 tagged_above=-999 required=6.31 tests=[HTML_MESSAGE=3, T_RP_MATCHES_RCVD=-0.01, URIBL_BLOCKED=0.001] autolearn=disabled Received: from mx1-us-west.apache.org ([10.40.0.8]) by localhost (spamd4-us-west.apache.org [10.40.0.11]) (amavisd-new, port 10024) with ESMTP id bSt1UamZ9Yad for ; Tue, 26 May 2015 20:37:33 +0000 (UTC) Received: from mail01.mediture.dom (zimbra.mediture.com [74.113.249.189]) by mx1-us-west.apache.org (ASF Mail Server at mx1-us-west.apache.org) with ESMTPS id E5754230F9 for ; Tue, 26 May 2015 20:37:32 +0000 (UTC) Received: from mail01.mediture.dom (localhost [127.0.0.1]) by mail01.mediture.dom (Postfix) with ESMTPS id 43A257A065C for ; Tue, 26 May 2015 15:36:56 -0500 (CDT) Received: from localhost (localhost [127.0.0.1]) by mail01.mediture.dom (Postfix) with ESMTP id 3484C7A052F for ; Tue, 26 May 2015 15:36:56 -0500 (CDT) X-Amavis-Modified: Mail body modified (using disclaimer) - mail01.mediture.dom Received: from mail01.mediture.dom ([127.0.0.1]) by localhost (mail01.mediture.dom [127.0.0.1]) (amavisd-new, port 10026) with ESMTP id LxiR0Hs21wQU for ; Tue, 26 May 2015 15:36:55 -0500 (CDT) Received: from [192.168.221.28] (unknown [216.70.56.165]) by mail01.mediture.dom (Postfix) with ESMTPSA id 9EF607A003C for ; Tue, 26 May 2015 15:36:55 -0500 (CDT) Message-ID: <5564D970.4040507@mediture.com> Date: Tue, 26 May 2015 15:37:04 -0500 From: Arthur Ramsey User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:31.0) Gecko/20100101 Thunderbird/31.7.0 MIME-Version: 1.0 To: users@groovy.incubator.apache.org Subject: Re: Groovy SQL stored procedure result processing References: <180275923.254769.1432580860878.JavaMail.zimbra@mediture.com> <3662F5B0-E0B5-4F7A-86DA-2626EEC149FA@anc.org> <55638206.1010800@mediture.com> <5563A27C.1060008@mediture.com> <55648C85.4010609@mediture.com> <55648F8E.6010002@mediture.com> <5564A146.7080804@mediture.com> <5564AEA1.6020802@mediture.com> <5564BD70.8020409@mediture.com> In-Reply-To: <5564BD70.8020409@mediture.com> Content-Type: multipart/alternative; boundary="------------050601070305070402070007" This is a multi-part message in MIME format. --------------050601070305070402070007 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: quoted-printable I tried adding... sql.withStatement() { stmt -> stmt.setFetchSize(Integer.MIN_VALUE) } sql.setResultSetType(ResultSet.TYPE_FORWARD_ONLY) sql.setResultSetConcurrency(ResultSet.CONCUR_READ_ONLY) Before sql.callWithAllRow to no effect Reference:=20 http://dev.mysql.com/connector-j/en/connector-j-reference-implementation-= notes.html On 5/26/2015 1:37 PM, Arthur Ramsey wrote: > No, it blocks at boolean hasResultSet =3D statement.execute(). > > On 05/26/2015 12:39 PM, Paco Zarate wrote: >> Great! >> How about your log? Is it working now as expected? >> >> On Tue, May 26, 2015 at 11:34 AM, Arthur Ramsey=20 >> > wrote= : >> >> This seems to work. >> >> @InheritConstructors >> class Sql extends groovy.sql.Sql { >> public static Sql newInstance(String url, String user, String= password, String driverClassName) throws SQLException, ClassNotFoundExce= ption { >> loadDriver(driverClassName) >> return newInstance(url, user, password) >> } >> >> public static Sql newInstance(String url, String user, String= password) throws SQLException { >> Connection connection =3D DriverManager.getConnection(url= , user, password) >> return new Sql(connection) >> } >> >> >> On 5/26/2015 12:20 PM, Paco Zarate wrote: >>> Hey Arthur, >>> are you able to create the github sample to play with? i think >>> it would be a more straightforward route, and this should be >>> quick to create. I can help you creating it, just let me know. >>> >>> Paco. >>> >>> On Tue, May 26, 2015 at 10:37 AM, Arthur Ramsey >>> > >>> wrote: >>> >>> Closer... >>> >>> Could not find matching constructor for: >>> groovy.sql.Sql(java.lang.String, java.lang.String, >>> java.lang.String, java.lang.String) >>> >>> Or alternatively... >>> >>> Sql (String url, String user, String password, String driverC= lassName) { >>> newInstance(url, user, password, driverClassName) >>> } >>> >>> Exception in thread "main" java.lang.IllegalAccessError: >>> tried to access method groovy.sql.Sql.()V from class >>> com.mediture.truchart.analytics.Sql >>> >>> >>> On 5/26/2015 11:10 AM, Paco Zarate wrote: >>>> Seems like the extension is not finding the constructor. >>>> Can you please try to include in your extension the >>>> constructor : >>>> >>>> Sql(|String >>>> url, >>>> String >>>> user, >>>> String >>>> password, >>>> String >>>> driverClassName) >>>> { >>>> | >>>> | super (url, user, password, driverClassName); >>>> } >>>> | >>>> |just to see if that fixes this error. I think it is not >>>> related to the other one, so I guess you will have a new >>>> |issue. >>>> >>>> Just in case, have you take a look at >>>> https://github.com/dsrkoc/groovy-sql-stream-extension ? it >>>> is an extension to the Sql class from Dinko Srko=C4=8D that >>>> allows you to use a stream for the resultset. >>>> >>>> Also you can try to create a proof of concept gist in >>>> github using this code as a base: >>>> https://github.com/groovy/groovy-core/blob/master/subproject= s/groovy-sql/src/test/groovy/groovy/sql/SqlCallTest.groovy >>>> (it uses an in memory database, creates a person table, >>>> then creates some stored procedures, and then it uses them >>>> to test these functions. This way you can reproduce the >>>> error in a more isolated way and you can share it to review >>>> or raise a bug report if needed). >>>> >>>> || >>>> | >>>> | >>>> >>>> On Tue, May 26, 2015 at 9:21 AM, Arthur Ramsey >>>> >>> > wrote: >>>> >>>> No. >>>> >>>> defsql =3DSql.newInstance("jdbc:mysql://${db_host}/${tar= get_database}".toString(),db_username,db_password,'com.mysql.jdbc.Driver'= ) >>>> >>>> On 5/26/2015 10:12 AM, Paco Zarate wrote: >>>>> Arthur, >>>>> Just to confirm, is this the calling code that is >>>>> returning the error? >>>>> >>>>> sql.execute('SET @data_history_units =3D ?',data_histor= y_units) >>>>> List> results =3D sql.callWithAll= Rows'{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()) >>>>> } >>>>> } >>>>> } >>>>> >>>>> >>>>> On Tue, May 26, 2015 at 9:08 AM, Arthur Ramsey >>>>> >>>> > wrote: >>>>> >>>>> I think you're right. I don't see a constructor >>>>> that uses GStringImpl. >>>>> >>>>> |*newInstance >>>>> *(String >>>>> url, >>>>> String >>>>> user, >>>>> String >>>>> password, >>>>> String >>>>> driverClassName)| >>>>> >>>>> I tried adding .toString() >>>>> >>>>> Exception in thread "main" >>>>> groovy.lang.GroovyRuntimeException: Could not find >>>>> matching constructor for: >>>>> com.mediture.truchart.analytics.Sql(java.lang.Strin= g, >>>>> java.lang.String, java.lang.String, java.lang.Strin= g) >>>>> >>>>> I also tried using the @InheritConstructors >>>>> instead of the following. >>>>> >>>>> Sql(groovy.sql.Sql parent) { >>>>> super(parent) >>>>> } >>>>> >>>>> On 5/26/2015 9:02 AM, Keith Suderman wrote: >>>>>> >>>>>> On May 25, 2015, at 6:30 PM, Arthur Ramsey >>>>>> >>>>> > 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 =3D new >>>>>> com.mediture.truchart.analytics.Sql("${my_var}".to= String(), >>>>>> string, string, string) >>>>>> >>>>>> Keith >>>>>> >>>>>>> at groovy.lang.MetaClassImpl.invokeConstruct= or(MetaClassImpl.java:1723) >>>>>>> at groovy.lang.MetaClassImpl.invokeConstruct= or(MetaClassImpl.java:1526) >>>>>>> at org.codehaus.groovy.runtime.InvokerHelper= .invokeConstructorOf(InvokerHelper.java:945) >>>>>>> at org.codehaus.groovy.runtime.DefaultGroovy= Methods.newInstance(DefaultGroovyMethods.java:15623) >>>>>>> at org.codehaus.groovy.runtime.dgm$447.doMet= hodInvoke(Unknown Source) >>>>>>> at org.codehaus.groovy.runtime.callsite.Stat= icMetaMethodSite.invoke(StaticMetaMethodSite.java:43) >>>>>>> at org.codehaus.groovy.runtime.callsite.Stat= icMetaMethodSite.call(StaticMetaMethodSite.java:88) >>>>>>> at org.codehaus.groovy.runtime.callsite.Call= SiteArray.defaultCall(CallSiteArray.java:45) >>>>>>> at org.codehaus.groovy.runtime.callsite.Abst= ractCallSite.call(AbstractCallSite.java:110) >>>>>>> at org.codehaus.groovy.runtime.callsite.Abst= ractCallSite.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-s= tored-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 >>>>>>>> >>>>>>> > 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 S= QL. >>>>>>>>> >>>>>>>>> I tried... >>>>>>>>> >>>>>>>>> sql.callWithAllRows('{call spLoad_whse_subjecta= rea(?,?,?,?,?,?,?)}', [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_subjectar= ea(?,?,?,?,?,?,?)}', [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 prop= erty 'value' on null object >>>>>>>>> at org.codehaus.groovy.runtime.NullObject.= getProperty(NullObject.java:57) >>>>>>>>> at org.codehaus.groovy.runtime.InvokerHelp= er.getProperty(InvokerHelper.java:169) >>>>>>>>> at org.codehaus.groovy.runtime.callsite.Nu= llCallSite.getProperty(NullCallSite.java:44) >>>>>>>>> at org.codehaus.groovy.runtime.callsite.Ab= stractCallSite.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 comple= te. >>>>>>>>>> >>>>>>>>>> Try >>>>>>>>>> >>>>>>>>>> String proc =3D '{call >>>>>>>>>> spLoad_whse_subjectarea(?,?,?,?,?,?,?)}' >>>>>>>>>> Object[] params =3D [ ... ] 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 proced= ure that produces multiple logging statements as results (not an OUT para= meter). >>>>>>>>>>> >>>>>>>>>>> my $sth =3D $dbh->prepare("CALL spLoad_whse_s= ubjectarea(?,true,?,?,?,?,?)"); >>>>>>>>>>> $sth->execute($_[0],$_[1],$_[2],$_[3],$_[4],$= _[5]); >>>>>>>>>>> do { >>>>>>>>>>> while (@row =3D $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 logg= ing. >>>>>>>>>>> >>>>>>>>>>> sql.execute('SET @data_history_units =3D ?',d= ata_history_units) >>>>>>>>>>> List> results =3D sql.c= allWithAllRows'{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 o= nly 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 em= ulate 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 >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> ------------------------------ >>>>>> Research Associate >>>>>> Department of Computer Science >>>>>> Vassar College >>>>>> Poughkeepsie, NY >>>>>> >>>>>> >>>>> >>>>> >>>>> >>>>> >>>> >>>> >>>> >>>> >>> >>> >>> >>> >> >> >> >> > > --=20 > Arthur Ramsey > Systems Administrator > Mediture > arthur_ramsey@mediture.com > 952.400.0323 > > This e-mail and any attachments may contain CONFIDENTIAL information, i= ncluding PROTECTED HEALTH INFORMATION. If you are not the intended recipi= ent, any use or disclosure of this information is STRICTLY PROHIBITED; yo= u are requested to delete this e-mail and any attachments, notify the sen= der immediately, and notify the Mediture Privacy Officer atprivacyofficer= @mediture.com. =0A --------------050601070305070402070007 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable I tried adding...
sql.=
withStatement() { stmt ->
    stmt.setFetchSize(Integer.MIN_VALUE)
}
sql.setResultSe=
tType(ResultSet.TYPE_FORWARD_ONLY)
sql.setResultSetConcurrency(ResultS=
et.CONC=
UR_READ_ONLY)
Before sql.callWithAllRow to no effect

Reference: http://dev.mysql.c= om/connector-j/en/connector-j-reference-implementation-notes.html

On 5/26/2015 1:37 PM, Arthur Ramsey wrote:
No, it blocks at boolean hasResultSet =3D statement.execute().

On 05/26/2015 12:39 PM, Paco Zarate wrote:
Great!
How about your log? Is it working=C2=A0 now as expected?

On Tue, May 26, 2015 at 11:34 AM, Arthur Ramsey <arthur_ramsey@mediture.com> wrote:
This seems to wo= rk.
@InheritConstructors
class Sql extends groovy.sql.Sql {
    public static Sql newInstance(String url, String user, String passwor=
d, String driverClassName) throws SQLException, ClassNotFoundException {
        loadDriver(driverClassName)
        return newInstance(url, user, password)
    }

    public static Sql newInstance(String url, String user, String passwor=
d) throws SQLException {
        Connection connection =3D DriverManager.getConnection(url, user, =
password)
        return new Sql(connection)
    }

On 5/26/2015 12:20 PM, Paco Zarate wrote:
Hey Arthur,
are you able to create the github sample to play with? i think it would be a more straightforward route, and this should be quick to create. I can help you creating it, just let me know.

Paco.

On Tue, May 26, 2015 a= t 10:37 AM, Arthur Ramsey <<= a moz-do-not-send=3D"true" href=3D"mailto:arthur_ramsey@mediture.com" target=3D"_blank">arthur_ramsey@mediture.co= m> wrote:
Closer...

Could not find matching constructor for: groovy.sql.Sql(java.lang.String, java.lang.String, java.lang.String, java.lang.String)

Or alternatively...

Sql (String url, Stri=
ng user, String password, String driverClassName) {
    newInstance(url, user=
, password, driverClassName)
}
Exception in thread "main" java.lang.IllegalAccessError: tried to access method groovy.sql.Sql.<init>()V from class com.mediture.truchart.analytics.Sql


On 5/26/2015 11:10 AM, Paco Zarate wrote:
Just in case, have you take a look at https://githu= b.com/dsrkoc/groovy-sql-stream-extension ? it is an extension to the Sql class from Dinko Srko=C4=8D that allows you to use a stream for the resultset.

Also you can try to create a proof of concept gist in github using this code as a base: https://githu= b.com/groovy/groovy-core/blob/master/subprojects/groovy-sql/src/test/groo= vy/groovy/sql/SqlCallTest.groovy (it uses an in memory database, creates a person table, then creates some stored procedures, and then it uses them to test these functions. This way you can reproduce the error in a more isolated way and you can share it to review or raise a bug report if needed).
=C2=A0


On Tue, May 26, 2015 at 9:21 AM, Arthur Ramsey <arthur_rams= ey@mediture.com> wrote:
No.
def sql =3D Sql.n=
ewInstance("jdbc:my=
sql://${db_host}/${target_database}".toString(), db_username, db_password, 'com.mysql.jdbc.Driver')
On 5/26/2015 10:12 AM, Paco Zarate wrote:<= br>
Arthur,
Just to confirm, is this the calling code that is returning the error?<= br> 
sql.execute('SET @data_history_units =
=3D ?', dat=
a_history_units)
List<List<GroovyRowResult>> results =3D sql.callWithAllRows <=
span style=3D"color:rgb(0,128,0);font-weight:bold">'{call spLoad_whse_sub=
jectarea(?,?,?,?,?,?,?)}', [in_step, run_step, in_step_title, in_p=
rocedure, in_current_stmt, in_start_data, in_run_count], {}
results.each { result ->
    result.each =
{ row ->
        row.each {
            log.info(it.value.toString(=
))
        }
    }
}


On Tue, May 26, 2015 at 9:08 AM, Arthur Ramsey <<= a moz-do-not-send=3D"true" href=3D"mailto:arthur_ramsey@mediture.com" target=3D"_blan= k">arthur_ramsey@mediture.com> wrote:
I think you're right.=C2=A0 I don't see a constructor that uses GStringImpl.
<= a moz-do-not-send=3D"true" href=3D"http://docs.groovy-lang.org/latest/html/api/groovy/sql/Sql.html#n= ewInstance%28java.lang.String,%20java.lang.String,%20java.lang.String,%20= java.lang.String%29" target=3D"_blank">newInstance(String=C2=A0url, String=C2=A0user, String=C2=A0password, String=C2=A0driverClassName)

I tried adding .toString()

Exception in thread "main" groovy.lang.Gro= ovyRuntimeException: Could not find matching constructor for: com.mediture.tr= uchart.analytics.Sql(java.lang.String, java.lang.Strin= g, java.lang.String, java.lang.String)

I also tried using the @InheritConstru= ctors instead of the following.
    Sql(gr=
oovy.sql.Sql parent) {
        super(parent)
    }
On 5/26/2015 9:02 AM, Keith Suderman wrote:
=
On May 25, 2015, at 6:30 PM, Arthur Ramsey <arthur_ramse= y@mediture.com> wrote:

I'm using Groovy 2.4.3.=C2= =A0 I saw that too.=C2=A0 I th= ink 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.=C2=A0 I'm totally new to Groovy and Java, so I'm probably making a simple mistake.

Exception in thread "main" groovy.lang.Gro= ovyRuntimeException: Could not find matching constructor for: com.mediture.tr= uchart.analytics.Sql(org.codehaus.groovy.runtime.GStringImpl, java.lang.Strin= g, 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. =C2= =A0 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).=C2= =A0 Fortunately the solution is simple, just call the toString() method on the GString in the call to the constructor. E.g.

def sql =3D new com.mediture.tr= uchart.analytics.Sql("${my_var}".toString(), string, string, string)

Keith 

    at gro=
ovy.lang.MetaClassImpl.invokeConstructor(MetaClassImpl.java:1723)
    at groovy.lang.MetaClassImpl.invokeConstructor(MetaClassImpl.java:152=
6)
    at org.codehaus.groovy.runtime.InvokerHelper.invokeConstructorOf(Invo=
kerHelper.java:945)
    at org.codehaus.groovy.runtime.DefaultGroovyMethods.newInstance(Defau=
ltGroovyMethods.java:15623)
    at org.codehaus.groovy.runtime.dgm$447.doMethodInvoke(Unknown Source)
    at org.codehaus.groovy.runtime.callsite.StaticMetaMethodSite.invoke(S=
taticMetaMethodSite.java:43)
    at org.codehaus.groovy.runtime.callsite.StaticMetaMethodSite.call(Sta=
ticMetaMethodSite.java:88)
    at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(Cal=
lSiteArray.java:45)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(Abstrac=
tCallSite.java:110)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(Abstrac=
tCallSite.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 (<= a moz-do-not-send=3D"true" href=3D"https://objectpartners.com/2014/01/24/simpler-stored-procedures-w= ith-groovy/" target=3D"_blank">https://objectpartners.com/2014/01/24/simpler-stored-pr= ocedures-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.=C2= =A0 Maybe the article will lead you to a solution.

Cheers,
Keith

On May 25, 2015, at 4:11 PM, Arthur Ramsey <arthur_ramse= y@mediture.com> wrote:

That's what I was trying initially, but all I get is null.=C2=A0 I t= hink the closure only gets output from an OUT parameter of the stored procedure.=C2=A0= I created a procedure that used an OUT parameter and got values in the closure.=C2= =A0 I don't maintain the procedures and I'm trying to port the perl wrapper without changing the SQL.

I tried...

callWithAllRows('{call spLoad_whse_subjectare=
a(?,?,?,?,?,?,?)}', [in_step, run_step, in_step_title, in_procedur=
e, in_current_stmt, in_start_data, in_run_count]) {
    log.info(it.val=
ue.toString())
}
And...

'{ca=
ll spLoad_whse_subjectarea(?,?,?,?,?,?,?)}', [in_step, run_step, i=
n_step_title, in_procedure, in_current_stmt, in_start_data, in_run_count]=
, {
    log.info(it.val=
ue.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(InvokerHelpe=
r.java:169)
    at org.codehaus.groovy.runtime.callsite.NullCallSite.getProperty(Null=
CallSite.java:44)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callGetPrope=
rty(AbstractCallSite.java:293)
On 5/25/2015 3:01 PM, Keith Suderman wrote:
The last parameter to sql.callWithAll= Rows() is a closure (empty in your example).=C2=A0= I suspect this is where you want to include the logging.=C2=A0 = 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 =3D '{call spLoad_whse_subjectarea(?,?,?,?,?,?,?)}'
Object[] params =3D [ ..= . ] 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_ramse= y@mediture.com wrote:

Hello,

I'm new to Groovy and I'm trying to port the following perl DBI code to G=
roovy SQL.  The code executes a stored procedure that produces multiple l=
ogging statements as results (not an OUT parameter).

my $sth =3D $dbh->prepare("CALL spLoad_whse_subjectarea(?,true,?,?,?,?=
,?)");
$sth->execute($_[0],$_[1],$_[2],$_[3],$_[4],$_[5]);
do {
	while (@row =3D $sth->fetchrow_array) {
		foreach (@row) {
			print "$_ ";
		}
		print "\n";
	}
} while ($sth->more_results);

The following Groovy code captures everything, but it blocks until the st=
ored procedure completes unlike the perl code which processes results pri=
or to completion.  The effect is delayed logging.

sql.execute('SET @data_his=
tory_units =3D ?', =
data_history_units)
List<List<GroovyRowResult>> results =3D sql.callWithAllRows <=
span style=3D"color:#008000;font-weight:bold">'{call spLoad_whse_subjecta=
rea(?,?,?,?,?,?,?)}', [in_step, run_step, in_step_title, in_proced=
ure, in_current_stmt, in_start_data, in_run_count], {}
results.each { result -&g=
t;
    result.each { row -&g=
t;
        row.each {
            log.inf=
o(it.value.toString())
        }
    }
}
I also tried sql.e=
achRow, but that seems to only get the first result.  I had a similar out=
come with perl DBI before I added the "while ($sth->more_results)".  I=
s there anyway I can better emulate the perl code even if I have to inter=
act with jdbc more closely?




------------------------------
Research Associate
Department of Computer Science
Vassar College<= br> Poughkeepsie, NY





------------------------------
Research Associate
Department of Computer Science
Vassar College<= br> Poughkeepsie, NY




<Sql.g= roovy>

------------------------------
Research Associate
Department of Computer Science
Vassar College<= br> Poughkeepsie, NY

 


















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

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


= =0A
= --------------050601070305070402070007--