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 17:34:25 GMT
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
>>>>
>>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>
>
>
>




Mime
View raw message