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 17:39:55 GMT
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>
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> 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> wrote:
>>
>>>  No.
>>>
>>> def sql = 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> 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