Re: SQL enhancement request (GROOVY-11342)
On Sat, Mar 30, 2024 at 6:21 AM wrote: > Do you have any thoughts around when this enhancement might come out in a > release? Probably in the next couple of weeks pending feedback. Paul.
RE: SQL enhancement request (GROOVY-11342)
Ah, ok, so I wasn’t missing anything, it would just have to be added to the language. I’m just super happy to be getting the metadata with the sql execute so … :) -Steve From: MG Sent: Friday, March 29, 2024 8:18 PM To: users@groovy.apache.org; steve.etchel...@gmail.com Subject: Re: SQL enhancement request (GROOVY-11342) That would be eachRowWithIndex, which would imho be nice to have here, as well as for collect (i.e. collectWithIndex)... ;-) Cheers, mg On 29/03/2024 19:17, steve.etchel...@gmail.com <mailto:steve.etchel...@gmail.com> wrote: Basically there's no way to get eachRow() to provide a counter...
Re: SQL enhancement request (GROOVY-11342)
That would be eachRowWithIndex, which would imho be nice to have here, as well as for collect (i.e. collectWithIndex)... ;-) Cheers, mg On 29/03/2024 19:17, steve.etchel...@gmail.com wrote: Basically there's no way to get eachRow() to provide a counter...
RE: SQL enhancement request (GROOVY-11342)
Paul,
I was able to download the Groovy snapshot and get it to work. The new
metadata support in execute() looks like it is working smoothly for me. Nice
learning day for me!
Do you have any thoughts around when this enhancement might come out in a
release?
Thanks,
Steve
-Original Message-
From: steve.etchel...@gmail.com
Sent: Friday, March 29, 2024 1:17 PM
To: users@groovy.apache.org
Subject: RE: SQL enhancement request (GROOVY-11342)
Thanks Paul! I thought that eachrow iteration issue had something to do with
eachRow() possibly not returning a List but when I saw it returned something
that looked like Proxy$10 I got lost as I didn't know how to understand that.
Normally I would try to look up the definition of the object, like when you
previously explained the subtleties between ResultSet and GroovyResultSet. The
fix worked smoothly (of course) -- indices() was a new one for me.
--- eachRow():
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)
I went through the snapshot process and it downloaded a zip file - it looks
like it is a Groovy 5 version, does that mean that this fix will be available
via a release once version 5 releases?
I've seen the Groovy 5 alpha releases and have been interested in the next
major release of Groovy. I wasn't sure how stable the alpha releases are so
I've stayed with version 4 up to this point. Any idea when Groovy 5 might
release?
Oh! Any idea why the SQL that doesn't produce resultSets doesn't fail with
eachRow() and rows() for hsqldb (only)? That one threw me for a loop. The
only thing I can think of is that the hsqldb driver does produce a (empty)
resultSet.
Finally (a little Java humor) - with eachRow() am I correct in thinking that my
rowid handling is the only way to handle it if I want row numbers displayed?
Basically there's no way to get eachRow() to provide a counter...
Integer rowid = 0
try {
m_connection.eachRow(sqlStatement, metaClosure) { row ->
println "row: $rowid"
colName.indices.each { colid ->
println " column $colid: column
${colName[colid]}='${row[colid]}' ...
}
rowid++
}
Thanks,
Steve
-----Original Message-----
From: Paul King
Sent: Thursday, March 28, 2024 9:05 PM
To: users@groovy.apache.org
Subject: Re: SQL enhancement request (GROOVY-11342)
Well spotted for eachRow. You can fix that with:
colName.indices.each { colid ->
println " column $colid: column
${colName[colid]}='${row[colid]}' (width=${colWidth[colid]},
type=${colType[colid]})"
}
The eachRow method returns a resultset proxy which doesn't support
eachWithIndex.
sdkman only supports releases. Snapshots are normally in various snapshot
repositories but for a PR, you need to download the snapshot artifacts produced
by the relevant GitHub action. Take a look here:
https://github.com/apache/groovy/actions/runs/8467245940/job/23197720603?pr=2070
And look for the URL under "Upload binary distribution".
Just remember, snapshots aren't releases, just useful for testing prior to
official releases.
Cheers, Paul.
On Fri, Mar 29, 2024 at 11:28 AM wrote:
>
> 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
> ==
RE: SQL enhancement request (GROOVY-11342)
Thanks Paul! I thought that eachrow iteration issue had something to do with
eachRow() possibly not returning a List but when I saw it returned something
that looked like Proxy$10 I got lost as I didn't know how to understand that.
Normally I would try to look up the definition of the object, like when you
previously explained the subtleties between ResultSet and GroovyResultSet. The
fix worked smoothly (of course) -- indices() was a new one for me.
--- eachRow():
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)
I went through the snapshot process and it downloaded a zip file - it looks
like it is a Groovy 5 version, does that mean that this fix will be available
via a release once version 5 releases?
I've seen the Groovy 5 alpha releases and have been interested in the next
major release of Groovy. I wasn't sure how stable the alpha releases are so
I've stayed with version 4 up to this point. Any idea when Groovy 5 might
release?
Oh! Any idea why the SQL that doesn't produce resultSets doesn't fail with
eachRow() and rows() for hsqldb (only)? That one threw me for a loop. The
only thing I can think of is that the hsqldb driver does produce a (empty)
resultSet.
Finally (a little Java humor) - with eachRow() am I correct in thinking that my
rowid handling is the only way to handle it if I want row numbers displayed?
Basically there's no way to get eachRow() to provide a counter...
Integer rowid = 0
try {
m_connection.eachRow(sqlStatement, metaClosure) { row ->
println "row: $rowid"
colName.indices.each { colid ->
println " column $colid: column
${colName[colid]}='${row[colid]}' ...
}
rowid++
}
Thanks,
Steve
-----Original Message-----
From: Paul King
Sent: Thursday, March 28, 2024 9:05 PM
To: users@groovy.apache.org
Subject: Re: SQL enhancement request (GROOVY-11342)
Well spotted for eachRow. You can fix that with:
colName.indices.each { colid ->
println " column $colid: column
${colName[colid]}='${row[colid]}' (width=${colWidth[colid]},
type=${colType[colid]})"
}
The eachRow method returns a resultset proxy which doesn't support
eachWithIndex.
sdkman only supports releases. Snapshots are normally in various snapshot
repositories but for a PR, you need to download the snapshot artifacts produced
by the relevant GitHub action. Take a look here:
https://github.com/apache/groovy/actions/runs/8467245940/job/23197720603?pr=2070
And look for the URL under "Upload binary distribution".
Just remember, snapshots aren't releases, just useful for testing prior to
official releases.
Cheers, Paul.
On Fri, Mar 29, 2024 at 11:28 AM wrote:
>
> 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
Re: SQL enhancement request (GROOVY-11342)
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
> 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 wer
RE: SQL enhancement request (GROOVY-11342)
prisingly) 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 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 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 =
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 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 menti
RE: SQL enhancement request (GROOVY-11342)
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 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 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) > rowClosu
