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 18:37:36 GMT
No, it blocks at boolean hasResultSet = 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 
> <arthur_ramsey@mediture.com <mailto:arthur_ramsey@mediture.com>> 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, ClassNotFoundException {
>              loadDriver(driverClassName)
>              return newInstance(url, user, password)
>          }
>
>          public static Sql newInstance(String url, String user, String password) throws
SQLException {
>              Connection connection = 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
>>     <arthur_ramsey@mediture.com <mailto:arthur_ramsey@mediture.com>>
>>     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 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:
>>>         Seems like the extension is not finding the constructor. Can
>>>         you please try to include in your extension the constructor :
>>>
>>>         Sql(|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)
>>>         {
>>>         |
>>>         |   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─Ź 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/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
>>>         <arthur_ramsey@mediture.com
>>>         <mailto:arthur_ramsey@mediture.com>> wrote:
>>>
>>>             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
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>
>
>
>

-- 
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