Awesome, super exciting to see progress so quickly, thanks! It's odd though, using your updated code with hsqldb I tried running the eachrow test and it still fails to iterate over the columns correctly for me - weird. I noticed that you copied the code from exampleEachRow() to exampleExecute() so I see what you meant that the eachRow() column iteration was working (and I can see from your output that it worked for you with exampleExecute()), but when I use the actual exampleEachRow() code path I am not getting proper iteration over the columns. And this is using your updated code with the hsqldb database and driver. I'll be scratching my head on this one for a while! :)
I noticed that you mentioned I could get early access to the updated Groovy
version by using a snapshot version. I use sdkman to manage my Groovy
installation and not sure how to get the snapshot version. Could you explain
how to get the snapshot?
Here's what I see from sdkman -
$ sdk list groovy
================================================================================
Available Groovy Versions
================================================================================
5.0.0-alpha-7 3.0.6 2.4.11 2.0.0
5.0.0-alpha-6 3.0.5 2.4.10 1.8.9
5.0.0-alpha-5 3.0.4 2.4.9 1.8.8
5.0.0-alpha-4 3.0.3 2.4.8 1.8.7
5.0.0-alpha-3 3.0.2 2.4.7 1.8.6
5.0.0-alpha-2 3.0.1 2.4.6 1.8.5
5.0.0-alpha-1 3.0.0 2.4.5 1.8.4
> * 4.0.20 2.6.0-alpha-4 2.4.4 1.8.3
* 4.0.19 2.6.0-alpha-3 2.4.3 1.8.2
* 4.0.18 2.6.0-alpha-2 2.4.2 1.8.1
* 4.0.17 2.6.0-alpha-1 2.4.1 1.8.0
* 4.0.16 2.5.23 2.4.0 1.7.11
4.0.15 2.5.22 2.3.11 1.7.10
Here is my execution of the (unmodified) SqlExamples2.groovy eachrow test using
the hsqldb where you can see that it iterates over the rows but only displays
COL1 but not COL2 values.
$ groovy SqlExamples2.groovy eachrow
--- main():
SQL: CREATE TABLE MYTABLE (COL1 integer, COL2 integer)
--- eachRow():
--- main():
SQL: INSERT INTO MYTABLE (COL1, COL2) VALUES (0, 1)
--- eachRow():
--- main():
SQL: DELETE FROM MYTABLE WHERE COL1=1
--- eachRow():
--- main():
SQL: INSERT INTO MYTABLE (COL1, COL2) VALUES (0, 1)
--- eachRow():
--- main():
SQL: SELECT * FROM MYTABLE
--- eachRow():
row: 0
column 0: column COL1='0' (width=11, type=4)
row: 1
column 0: column COL1='0' (width=11, type=4)
--- main():
SQL: DELETE FROM MYTABLE WHERE COL1=0
--- eachRow():
But even stranger is that SqlExamples2.groovy using eachrow() with hsqldb does
NOT seem to encounter any issues with queries that do not produce resultSets!
Not sure how that is possible but I've run it several times and it's
consistent. My original SqlExamples.groovy eachrow consistently fails with the
"did not produce a resultSet".
$ groovy -cp $DBJAR SqlExamples.groovy eachrow
--- main():
SQL: SELECT 1 AS "COL1", 11 AS "COL2" UNION ALL SELECT 2 AS "COL1", 12 AS
"COL2" UNION ALL SELECT 3 AS "COL1", 13 AS "COL2" ORDER BY 1 DESC
--- eachRow():
row: 0
column 0: column COL1='3' (width=11, type=4)
row: 1
column 0: column COL1='2' (width=11, type=4)
row: 2
column 0: column COL1='1' (width=11, type=4)
--- main():
SQL: INSERT INTO MYTABLE (COL1, COL2) VALUES (0, 1)
--- eachRow():
Mar 28, 2024 1:08:49 PM groovy.sql.Sql eachRow
WARNING: Failed to execute: INSERT INTO MYTABLE (COL1, COL2) VALUES (0, 1)
because: This SQL statement does not return a single ResultSet
SQL did not produce a resultSet but unable to suppress warning
java.sql.SQLException: This SQL statement does not return a single ResultSet
So the code accessing the column information for the three different examples -
println " column $colid: column ${colName[colid]}='${element[colid]}'
(width=${colWidth[colid]}, type=${colType[colid]})" // from exampleExecute()
println " column $colid: column ${colName[colid]}='${element.value}'
(width=${colWidth[colid]}, type=${colType[colid]})" // from exampleEachRow()
println " column $colid: column ${colName[colid]}='${element.value}'
(width=${colWidth[colid]}, type=${colType[colid]})" // from exampleRows()
is identical except that exampleEachRow() uses ${element[colid]} versus the
${element.value} for the other two. I tried converting exampleEachRow() to use
${element.value} but that (surprisingly) generated an error -
--- eachRow():
row: 0
Mar 28, 2024 4:04:21 PM groovy.sql.Sql eachRow
WARNING: Failed to execute: SELECT * FROM MYTABLE because: Column not
found: value
Given the strangeness of the two examples I have attached both versions, being
careful not to modify anything. My version uses a Denodo database, which is
the target database that I need to use. I've worked with Denodo for a few
years now and it's JDBC interface has seemed to be pretty standard from what
I've seen. We use it with probably about 60-75 developers using Java and
Python (and PowerBI using ODBC) and have never run into any driver anomalies,
though Python has been somewhat painful with its weird JayDeBeApi driver
(basically it can be slow with very large resultSets, 1B+ rows). It was that
JayDeBeApi driver that initially led me to Groovy since Groovy has a smooth
natural Java database driver integration. These developers ported their
skillsets over to Denodo from earlier Oracle/Teradata/SQLServer/MySQL
environments and have basically found Denodo to be a plug-n-play drop-in
replacement for their earlier databases.
I was happy to see that apparently I understood how to handle the two closure
calls correctly! And you didn't (seem to) throw up when you saw my metaClosure
definition with its three parallel lists! If there's a better/groovier way to
accomplish what I'm doing in that closure I'm open to learning. :)
Apologies for the lengthy email and thanks again,
Steve
-----Original Message-----
From: Paul King <pa...@asert.com.au>
Sent: Thursday, March 28, 2024 7:39 AM
To: users@groovy.apache.org
Subject: Re: SQL enhancement request (GROOVY-11342)
Here is the result of running a slightly modified version of your script (I
attached it to the issue) after applying the PR I just
created:
> groovy SqlExamples2.groovy execute
--- main():
SQL: CREATE TABLE MYTABLE (COL1 integer, COL2 integer)
--- execute() [no metadata available]:
successful execution (with no resultSet)
rows updated = 0
--- main():
SQL: INSERT INTO MYTABLE (COL1, COL2) VALUES (0, 1)
--- execute() [no metadata available]:
successful execution (with no resultSet)
rows updated = 1
--- main():
SQL: DELETE FROM MYTABLE WHERE COL1=1
--- execute() [no metadata available]:
successful execution (with no resultSet)
rows updated = 0
--- main():
SQL: INSERT INTO MYTABLE (COL1, COL2) VALUES (0, 1)
--- execute() [no metadata available]:
successful execution (with no resultSet)
rows updated = 1
--- main():
SQL: SELECT * FROM MYTABLE
--- execute() [no metadata available]:
query produced a resultSet
row 0:
column 0: column COL1='0' (width=11, type=4)
column 1: column COL2='1' (width=11, type=4) row 1:
column 0: column COL1='0' (width=11, type=4)
column 1: column COL2='1' (width=11, type=4)
--- main():
SQL: DELETE FROM MYTABLE WHERE COL1=0
--- execute() [no metadata available]:
successful execution (with no resultSet)
rows updated = 2
The eachRow variant worked fine for me. You could try yourself using the
modified script - it uses hsqldb, so no need to add your database driver. Maybe
the issue you were having is specific to your database/driver. To test the
execute path, you'd need to use a snapshot version.
Paul.
On Thu, Mar 28, 2024 at 10:46 AM <steve.etchel...@gmail.com> wrote:
>
> Paul, I created that example (attached) attempting to illustrate my
> situation with the various (3 anyway) Sql methods. Hopefully my code
> makes some sense, happy to answer any questions that it raises. :)
>
> Running the example with 'execute' which does not provide metadata but does
> handle SQL that does not produce a resultSet.
>
> $ groovy -cp $DBJAR SqlExamples.groovy execute
> --- main():
> SQL: SELECT 1 AS "COL1", 11 AS "COL2" UNION ALL SELECT 2 AS "COL1", 12
> AS "COL2" UNION ALL SELECT 3 AS "COL1", 13 AS "COL2" ORDER BY 1 DESC
> --- execute() [no metadata available]:
> query produced a resultSet
> row 0:
> col COL1='3' (width=?, type=?)
> col COL2='13' (width=?, type=?)
> row 1:
> col COL1='2' (width=?, type=?)
> col COL2='12' (width=?, type=?)
> row 2:
> col COL1='1' (width=?, type=?)
> col COL2='11' (width=?, type=?)
> --- main():
> SQL: INSERT INTO MYTABLE (COL1, COL2) VALUES (0, 1)
> --- execute() [no metadata available]:
> successful execution (with no resultSet)
> rows updated = 1
> --- main():
> SQL: DELETE FROM MYTABLE WHERE COL1=1
> --- execute() [no metadata available]:
> successful execution (with no resultSet)
> rows updated = 0
> --- main():
> SQL: INSERT INTO MYTABLE (COL1, COL2) VALUES (0, 1)
> --- execute() [no metadata available]:
> successful execution (with no resultSet)
> rows updated = 1
> --- main():
> SQL: DELETE FROM MYTABLE WHERE COL1=0
> --- execute() [no metadata available]:
> successful execution (with no resultSet)
> rows updated = 2
>
> ===============================
> And then here's an execution with 'rows' which does provide metadata but
> cannot handle SQL that does not produce a resultSet.
>
> $ groovy -cp $DBJAR SqlExamples.groovy rows
> --- main():
> SQL: SELECT 1 AS "COL1", 11 AS "COL2" UNION ALL SELECT 2 AS "COL1", 12
> AS "COL2" UNION ALL SELECT 3 AS "COL1", 13 AS "COL2" ORDER BY 1 DESC
> --- rows():
> row 0:
> column 0: column COL1='3' (width=11, type=4)
> column 1: column COL2='13' (width=11, type=4) row 1:
> column 0: column COL1='2' (width=11, type=4)
> column 1: column COL2='12' (width=11, type=4) row 2:
> column 0: column COL1='1' (width=11, type=4)
> column 1: column COL2='11' (width=11, type=4)
> --- main():
> SQL: INSERT INTO MYTABLE (COL1, COL2) VALUES (0, 1)
> --- rows():
> Mar 27, 2024 7:01:46 PM groovy.sql.Sql$AbstractQueryCommand execute
> WARNING: Failed to execute: INSERT INTO MYTABLE (COL1, COL2) VALUES
> (0, 1) because: This SQL statement does not return a single ResultSet
> SQL did not produce a resultSet but unable to suppress warning
>
> Hopefully this is what you meant when you said an example would be helpful.
> The example should work with all/most JDBC-compliant databases though the
> SELECT query might need a little tweaking as some databases don't allow
> queries without a FROM clause.
>
> Putting together the example was an educational experience and ended up
> helping me to improve my understanding of Groovy - still have a long way to
> go! For example, I never could get the exampleEachRow() to completely work;
> it processes the rows but I couldn't get it to iterate over the columns the
> way that the other two methods do.
>
> And any educational feedback you might have would be greatly
> appreciated! After all I'm sure you have plenty of free time to
> donate! :D
>
> So the approach you mentioned of adding a new variant to execute() that adds
> a metaClosure would be perfect, though I've really gotten comfortable with
> the rows() method - not sure how it scales when resultSets go to billions of
> rows though... I expect execute() would perform like a streaming interface
> and not have any problems at scale.
>
> Thanks for all your help, couldn't have made it this far otherwise,
> Steve -----Original Message-----
> From: Paul King <pa...@asert.com.au>
> Sent: Monday, March 25, 2024 8:03 PM
> To: users@groovy.apache.org
> Subject: Re: SQL enhancement request
>
> Adding a metaClosure to execute seems the easiest change. I created
> GROOVY-11342 to track here:
>
> https://issues.apache.org/jira/browse/GROOVY-11342
>
> Would the expectation be that the metaClosure is called for each result
> producing a ResultSet?
>
> Paul.
>
> On Sat, Mar 23, 2024 at 3:40 AM <steve.etchel...@gmail.com> wrote:
> >
> > Thanks Jörg,
> >
> > Yes, that's one of the approaches I tried but the execute() method doesn't
> > appear to provide metadata (column names, types, etc) and my application
> > needs that information.
> >
> > That's why my request was to either
> >
> > - add the "hasResults" argument to the rows() and eachRow()
> > methods
> >
> > OR
> >
> > - add metadata results to the execute() method.
> >
> > The relevant variants that I'm seeing are:
> >
> > eachRow(String sql, Closure metaClosure, Closure rowClosure)
> > eachRow(GString gstring, Closure metaClosure, Closure
> > rowClosure)
> >
> > rows(String sql, Closure metaClosure)
> > rows(GString gstring, Closure metaClosure)
> >
> > execute(String sql, Closure processResults)
> > execute(GString gstring, Closure processResults)
> >
> > where for the eachRow() and rows() methods -
> >
> > sql - the sql statement
> > metaClosure - called for metadata (only once after sql execution)
> > rowClosure - called for each row with a GroovyResultSet
> >
> > and for the execute() method -
> >
> > sql - the SQL to execute
> > processResults - a Closure which will be passed two parameters: either
> > true plus a list of GroovyRowResult values derived from
> > statement.getResultSet() or false plus the update count from
> > statement.getUpdateCount(). The closure will be called for each result
> > produced from executing the SQL.
> >
> > The processResults() closure that execute() calls is passed two parameters,
> > while the rowClosure() closure that eachRow() and rows() calls only appears
> > to pass the one parameter, the GroovyResultSet.
> >
> > My enhancement request is to align these three method "families" (eachRow,
> > rows, and execute) so that they all have a variant with a metaClosure
> > and/or a two-parameter processResults closure.
> >
> > Thanks,
> > Steve
> >
> > -----Original Message-----
> > From: Jörg Prante <joergpra...@gmail.com>
> > Sent: Friday, March 22, 2024 12:03 PM
> > To: users@groovy.apache.org
> > Subject: Re: SQL enhancement request
> >
> > Hi Steve,
> >
> > just use this Sql.execute method
> >
> > https://docs.groovy-lang.org/latest/html/api/groovy/sql/Sql.html#exe
> > cu
> > te(java.lang.String,groovy.lang.Closure)
> >
> > to send arbitrary statements and decide by the isResultSet flag in the
> > closure whether you have to obtain a result set from a select query, or an
> > update counter or something from a non-result set query (update, insert,
> > delete).
> >
> > There is a short example in the documentation.
> >
> > Best regards,
> >
> > Jörg
> >
> > Am Donnerstag, dem 21.03.2024 um 14:18 -0500 schrieb
> > steve.etchel...@gmail.com:
> > > Groovy team,
> > >
> > > It is my understanding (which can always be improved!) that Groovy
> > > SQL supports about 3 “families” of interaction methods – execute()
> > > and its variants, rows() and eachRow() for submitting SQL
> > > statements and processing any results generated.
> > >
> > > Each of them has a variety of signatures and they are for the most
> > > part really “groovy” and a pleasure to work with. I really like
> > > Groovy and don’t understand why it hasn’t taken the world by storm
> > > given its super compatibility with Java. 😊
> > >
> > > However, I’ve run across one area that I feel like could benefit
> > > from a change/enhancement in the Groovy Sql package. The
> > > execute() methods accept a closure to process the results that
> > > come back from the database/driver and that closure accepts two
> > > arguments – the first argument specifies whether or not the result
> > > set has any results and then the second argument processes any
> > > results. It is that first argument that does not seem to be
> > > consistently available in the other methods. For example, if you
> > > were to use the rows() method and the SQL statement was say in
> > > INSERT statement then you’ll get an exception stating that the
> > > request does not produce a resultSet and there does not appear to be any
> > > way to work around it.
> > >
> > > Of course I could switch from the rows() method to the execute()
> > > method but then I (appear) to lose the metadata results (column
> > > names, types, etc).
> > >
> > > My situation is that I do not know in advance what SQL statements
> > > are going to be processed, they come from user input. And I need
> > > the metadata information – for those statements that generate
> > > results. I thought maybe I could just use the rows() method and
> > > catch any exceptions for statements that do not generate results
> > > and then resubmit those statements via execute() but that approach
> > > is pretty ugly and seems to generate error messages that are
> > > difficult/impossible to suppress.
> > >
> > > If the other SQL methods supported the “hasResults” flag and/or if
> > > the
> > > execute() methods supported metadata results I feel like the
> > > overall implementation would be improved.
> > >
> > > Thanks,
> > > Steve
> > >
> > >
> >
> >
SqlExamples.groovy
Description: Binary data
SqlExamples2.groovy
Description: Binary data
