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 > > > > > >
SqlExamples.groovy
Description: Binary data
