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 15:21:50 GMT
No.

defsql =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 = ?',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 <mailto: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 <mailto: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
>>>>     <mailto: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 <http://log.info>(it.value.toString()
>>>>>>     }
>>>>>>
>>>>>>     Hope this helps.
>>>>>>
>>>>>>     Cheers,
>>>>>>     Keith
>>>>>>
>>>>>>     On May 25, 2015, at 3:07 PM, arthur_ramsey@mediture.com
>>>>>>     <mailto: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