Thanks!! Yes, I agree, that sounds like the easiest way to go and would definitely resolve the challenge I'm facing.
I'm (still) working on putting together an example. It turned out to be a more challenging learning experience than I thought! š But it's really good for me - I'm going to get the hang of this if it kills me! š Yes, I would think that the metaClosure would be called for each result producing a resultSet - but of course for cases where there is no result (hasResults false) then of course there would be no metaClosure call. I'm thinking that your question about "each" resultSet is considering those situations (database vendors?) that can produce more than one resultSet for a call. I seem to recall that SQLServer and Sybase can do that but I don't think that comes up with others... might be another learning experience for me though. Thanks, 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 > > > > > >
