How could I execute both a query and an DML statement with the same code in groovy?

Question

I'm the author of SQL Fiddle. That knowledge may help frame this question:

I am attempting to write some Groovy code which will execute any arbitrary query/DML code in my database. Based on my reading of the Groovy Sql API, all of these functions expect something very particular. For example, "eachRow" expects there to be a resultset returned; if it isn't returned (as in the case of an UPDATE statement, for example) then using it will throw an error. I am able to call "execute" with any type of statement, however I can't get back a resultset for my SELECT statements when using that (which is definitely a requirement).

At this point I'm thinking I might have to abandon Groovy's Sql library in favor of some lower-level JDBC implementation. This would be a shame, I think, but I'm willing to go there if necessary. I would greatly prefer to keep this as Groovy-esqe as possible, though. How might I go about doing that?

Answer 1

Have a look at the varieties of execute() methods which returns a boolean. The return type as per doc:

true if the first result is a ResultSet object; false if it is an update count or there are no results

CREATE, DROP and INSERT has been mentioned in the example. I hope it works the same way for UPDATE.

For SELECT, the flag has to be checked if there is a ResultSet present. If yes, then a second query can be fired to get the rows. For example:

//If statement qualifies for select statements
//because .execute() will return true if there is a ResultSet
//present. It will be false for CREATE, UPDATE and DROP.
if( sql.execute("select name from Foo") ) {

    //Fire the second query to get the rows in case of SELECT query
    result = sql.rows "select name from Foo"
}

return result

UPDATE
If there is a concern about executing query twice for performance reasons, then the below approach can be tried:

def queryString = "update Foo set name = 'hello' where name = 'baz'"

try {
    sql.query queryString, { ResultSet rs ->
        //Result set returned for select query only
        //For all other cases exception is thrown
    }
} catch( java.sql.SQLException ) {
    //Throws exception for any other type of query
    //By now you should be smart enough exception is not 
    //thrown for any other cause.

    sql.execute queryString
}