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 0728718121 for ; Tue, 26 May 2015 16:11:20 +0000 (UTC) Received: (qmail 68418 invoked by uid 500); 26 May 2015 16:11:16 -0000 Delivered-To: apmail-groovy-users-archive@groovy.apache.org Received: (qmail 68382 invoked by uid 500); 26 May 2015 16:11:16 -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 68372 invoked by uid 99); 26 May 2015 16:11:16 -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 16:11:16 +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 6445EC0922 for ; Tue, 26 May 2015 16:11:16 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd4-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 4.751 X-Spam-Level: **** X-Spam-Status: No, score=4.751 tagged_above=-999 required=6.31 tests=[HTML_MESSAGE=3, KAM_INFOUSMEBIZ=0.75, KAM_LAZY_DOMAIN_SECURITY=1, URIBL_BLOCKED=0.001] autolearn=disabled Received: from mx1-eu-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 pe5iJvO7R7Ru for ; Tue, 26 May 2015 16:11:02 +0000 (UTC) Received: from mail-ob0-f178.google.com (mail-ob0-f178.google.com [209.85.214.178]) by mx1-eu-west.apache.org (ASF Mail Server at mx1-eu-west.apache.org) with ESMTPS id 04BF420A93 for ; Tue, 26 May 2015 16:11:00 +0000 (UTC) Received: by obbgf1 with SMTP id gf1so37087250obb.2 for ; Tue, 26 May 2015 09:10:54 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:content-type; bh=ctJBMdG2iZTtwnVlhFuZ1BVdvdXROX7WYgIR/7Qz00Y=; b=VFk15nGHivuWupP4ulcg8vFwHnahe0P8cKYzzgiQm7hdxCe1BHj8xobwMwQpp2bH+r DxQk5jz0DfCFBHN3txv+Nz7umgVioNZV2a4E4gaJmXUaU+3AQiDWint8SwgDuL0ySijD F7TXl87lgFCWjYojAQrkFrtHAx5kAPFDKoR7znbZMggpMYbvP0WYsjPVO3a5LM0ja5Hk m2zdY4hjvlBnOXoZ21oU/dcbPD9yqPqX5vfo95y5hZykIJz83U8sJJ3TGP+0OyrT3G0M 1zFdA6/zSPNzHHeNmrYUhlvc3ZrtYMksntGvfv7KxEsAnWou6tO2PN9bBE9n4hscwf1K 3ecw== X-Gm-Message-State: ALoCoQmwn9+5nAM8mUD23mes9U3ByhxhXzPFNJEUUM1WTvVj9Z+h/pbPUWaiKU3KlWCU3vT2dF70 X-Received: by 10.202.106.197 with SMTP id f188mr21212067oic.128.1432656654374; Tue, 26 May 2015 09:10:54 -0700 (PDT) MIME-Version: 1.0 Received: by 10.76.147.74 with HTTP; Tue, 26 May 2015 09:10:24 -0700 (PDT) X-Originating-IP: [209.140.6.138] In-Reply-To: <55648F8E.6010002@mediture.com> 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> From: Paco Zarate Date: Tue, 26 May 2015 10:10:24 -0600 Message-ID: Subject: Re: Groovy SQL stored procedure result processing To: users Content-Type: multipart/alternative; boundary=001a1141bdb2f833dd0516fe5ef8 --001a1141bdb2f833dd0516fe5ef8 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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/subprojects/groovy-sql/sr= c/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. > > def sql =3D Sql.newInstance("jdbc:mysql://${db_host}/${target_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_history_units) > List> results =3D sql.callWithAllRows '{call spLoad= _whse_subjectarea(?,?,?,?,?,?,?)}', [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 > 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.String, java.lang.String, >> java.lang.String, java.lang.String) >> >> 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 jus= t >> 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.GStringI= mpl, >> 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 typica= lly >> 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 soluti= on >> 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}".toString(), string, stri= ng, >> string) >> >> Keith >> >> at groovy.lang.MetaClassImpl.invokeConstructor(MetaClassImpl.java= :1723) >> at groovy.lang.MetaClassImpl.invokeConstructor(MetaClassImpl.java:15= 26) >> at org.codehaus.groovy.runtime.InvokerHelper.invokeConstructorOf(Inv= okerHelper.java:945) >> at org.codehaus.groovy.runtime.DefaultGroovyMethods.newInstance(Defa= ultGroovyMethods.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(St= aticMetaMethodSite.java:88) >> at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(Ca= llSiteArray.java:45) >> at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(Abstra= ctCallSite.java:110) >> at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(Abstra= ctCallSite.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-gro= ovy/) >> 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 per= l >> 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_dat= a, 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_dat= a, in_run_count], { >> log.info(it.value.toString()) >> } >> >> Either produce... >> >> java.lang.NullPointerException: Cannot get property 'value' on null obje= ct >> at org.codehaus.groovy.runtime.NullObject.getProperty(NullObject.jav= a:57) >> at org.codehaus.groovy.runtime.InvokerHelper.getProperty(InvokerHelp= er.java:169) >> at org.codehaus.groovy.runtime.callsite.NullCallSite.getProperty(Nul= lCallSite.java:44) >> at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callGetProp= erty(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 =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 procedure that produces multiple lo= gging 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 s= tored procedure completes unlike the perl code which processes results prio= r to completion. The effect is delayed logging. >> >> sql.execute('SET @data_history_units =3D ?', data_history_units) >> List> results =3D sql.callWithAllRows '{call spLoa= d_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->mor= e_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 >> >> >> >> >> >> >> >> >> ------------------------------ >> Research Associate >> Department of Computer Science >> Vassar College >> Poughkeepsie, NY >> >> >> >> >> >> > > > > --001a1141bdb2f833dd0516fe5ef8 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Seems like the extension is not findin= g the constructor. Can you please try to include in your extension the cons= tructor :

Sql(String=C2=A0url, String=C2= =A0user, String=C2=A0password, String=C2=A0driverClassName)= {
=C2=A0=C2=A0 super (url, user, password, driverCla= ssName);
}
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 <= /code>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 result= set.

Also you can try to create a proof of concept gist i= n github using this code as a base: https://github.com/groovy/groovy-core/blob/master/subproje= cts/groovy-sql/src/test/groovy/groovy/sql/SqlCallTest.groovy (it uses a= n in memory database, creates a person table, then creates some stored proc= edures, and then it uses them to test these functions. This way you can rep= roduce the error in a more isolated way and you can share it to review or r= aise a bug report if needed).
=C2=A0
<= br>

On Tue, May 26, 2015 at 9:21 AM, Arthur Ramsey <<= a href=3D"mailto:arthur_ramsey@mediture.com" target=3D"_blank">arthur_ramse= y@mediture.com> wrote:
=20 =20 =20
No.
=20 
def sql =3D Sql.=
newInstance("jdbc:mysql://${db_host}/${target=
_database}".toString(), db_username, db_password, <=
span style=3D"color:#008000;font-weight:bold">'com.mysql.jdbc.Driver=
9;)
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_history_units)
List<List<GroovyRowResult>> results =3D sql.callWithAllRows '{call spLoad_whse_sub=
jectarea(?,?,?,?,?,?,?)}', [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 <arthur_ramsey@mediture.com> wrote:
I think you're right.=C2=A0 I don't see a constructor that uses GStringI= mpl.

newInstan= ce(String=C2=A0url, String=C2=A0user, Str= ing=C2=A0password, String=C2=A0driverClassName)

I tried adding .toString()

Exception in thread "main" groovy.lang.GroovyRuntimeException: Could not find matching constructor for: com.mediture.truchart.analytics.Sql(java.lang.String, java.lang.String, java.lang.String, java.lang.String)

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 <arthur_ramsey@me= diture.com> wrote:

I'm usin= g Groovy 2.4.3.=C2=A0 I saw that too.=C2=A0 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 cor= rect 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 J= ava, so I'm probably making a simple mistake.

Exception in thread "main" groovy.lang.GroovyRuntimeExce= ption: Could not find matching constructor for: com.mediture.truchart.analytics.Sql(org.codehaus.groo= vy.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. =C2=A0 In "Groovy space" this typical= ly 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.truchart.analytics.Sql("${my_var}&quo= t;.toString(), string, string, string)

Keith

    at groovy.lang.MetaClassImpl.invokeConstruct=
or(MetaClassImpl.java:1723)
    at groovy.lang.MetaClassImpl.invokeConstructor(MetaClassImpl.java:1526)
    at org.codehaus.groovy.runtime.InvokerHelper.invokeConstructorOf(Invoke=
rHelper.java:945)
    at org.codehaus.groovy.runtime.DefaultGroovyMethods.newInstance(Default=
GroovyMethods.java:15623)
    at org.codehaus.groovy.runtime.dgm$447.doMethodInvoke(Unknown Source)
    at org.codehaus.groovy.runtime.callsite.StaticMetaMethodSite.invoke(Sta=
ticMetaMethodSite.java:43)
    at org.codehaus.groovy.runtime.callsite.StaticMetaMethodSite.call(Stati=
cMetaMethodSite.java:88)
    at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallS=
iteArray.java:45)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractC=
allSite.java:110)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractC=
allSite.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-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_ramsey@mediture.com> wrote:

That's what I was trying initially, but all I get is null.=C2=A0 I think the closur= e 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...

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_subje=
ctarea(?,?,?,?,?,?,?)}', [in_step, run_step, in_step_title, in_p=
rocedure, 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:5=
7)
    at org.codehaus.groovy.runtime.InvokerHelper.getProperty(InvokerHelper.=
java:169)
    at org.codehaus.groovy.runtime.callsite.NullCallSite.getProperty(NullCa=
llSite.java:44)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callGetPropert=
y(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).=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.callWithAll= Rows(proc, params) {
log.info(it.value.toString()
}

Hope this helps.

Cheers,
Keith

On May 25, 2015, at 3:07 PM, arthur_ramsey@me= diture.com wrote:

Hello,

I'm new to Groovy and I'm trying to port the following perl DBI cod=
e to Groovy SQL.  The code executes a stored procedure that produces multip=
le logging 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 stor=
ed procedure completes unlike the perl code which processes results prior t=
o completion.  The effect is delayed logging.

sql.execute('SET @data_h=
istory_units =3D ?', data_history_units)
List<List<GroovyRowResult>> results =3D sql.callWithAllRows '{call spLoad_whse_subjecta=
rea(?,?,?,?,?,?,?)}', [in_step, run_step, in_step_title, in_proc=
edure, 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.eac=
hRow, 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 int=
eract with jdbc more closely?




-= -----------------------------
R= esearch Associate
Department of Computer Science
Vassar College
Poughkeepsie, NY





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




<Sql.groovy>

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






=20


--001a1141bdb2f833dd0516fe5ef8--