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#execu > > 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 > > > > > > > > > >
