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#execute(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
>
>
