groovy-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Paco Zarate <conta...@nazcasistemas.com>
Subject Re: Groovy SQL stored procedure result processing
Date Tue, 26 May 2015 15:12:10 GMT
Arthur,
Just to confirm, is this the calling code that is returning the error?

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())
        }
    }
}



On Tue, May 26, 2015 at 9:08 AM, Arthur Ramsey <arthur_ramsey@mediture.com>
wrote:

>  I think you're right.  I don't see a constructor that uses GStringImpl.
>
> *newInstance
> <http://docs.groovy-lang.org/latest/html/api/groovy/sql/Sql.html#newInstance%28java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String%29>*
> (String
> <http://docs.oracle.com/javase/8/docs/api/java/lang/String.html?is-external=true>
url,
> String
> <http://docs.oracle.com/javase/8/docs/api/java/lang/String.html?is-external=true>
user,
> String
> <http://docs.oracle.com/javase/8/docs/api/java/lang/String.html?is-external=true>
password,
> String
> <http://docs.oracle.com/javase/8/docs/api/java/lang/String.html?is-external=true>
>  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 <arthur_ramsey@mediture.com>
> 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 = new
> com.mediture.truchart.analytics.Sql("${my_var}".toString(), string, string,
> string)
>
>  Keith
>
>        at groovy.lang.MetaClassImpl.invokeConstructor(MetaClassImpl.java:1723)
>     at groovy.lang.MetaClassImpl.invokeConstructor(MetaClassImpl.java:1526)
>     at org.codehaus.groovy.runtime.InvokerHelper.invokeConstructorOf(InvokerHelper.java:945)
>     at org.codehaus.groovy.runtime.DefaultGroovyMethods.newInstance(DefaultGroovyMethods.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(StaticMetaMethodSite.java:88)
>     at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:45)
>     at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:110)
>     at org.codehaus.groovy.runtime.callsite.AbstractCallSite.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-stored-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 <arthur_ramsey@mediture.com>
> 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 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 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
>
>
>
>
>
>
>  ------------------------------
> Research Associate
> Department of Computer Science
> Vassar College
> Poughkeepsie, NY
>
>
>
>
>
>  <Sql.groovy>
>
>
>  ------------------------------
> Research Associate
> Department of Computer Science
> Vassar College
> Poughkeepsie, NY
>
>
>
>
>
>

Mime
View raw message