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
[email protected]:
> 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
>  
>