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 20:37:04 GMT
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: 
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 = 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 atprivacyofficer@mediture.com.




Mime
View raw message