Groovy SQL eachRow requires ResultSet to be returned

Question

Groovy's SQL provides an eachRow() method but it seems to generate an uncatchable error if the SQL statement being executed doesn't produce a ResultSet, e.g. an INSERT statement. Of course Groovy SQL also provides an execute() method for SQL statements that don't produce a ResultSet. My challenge is that if the SQL statements are provided by users then I'm wondering how to know which method to use? I was hoping that eachRow() would throw an exception that could be caught/swallowed if there was no ResultSet but that doesn't seem to be the case.

Here's a snippet showing what I tried. In the example, it reads SQL statements from stdin, and attempts to process them using eachRow().

System.in.readLines().each {
    dbsql = it
    try {
        println "processing sql"
        dbconn.eachRow(dbsql) {
            n++
        }
        println "processed result ($n rows)"
    } catch(SQLException ex) {
      rslt = 2
      println ">>> query failed against ${dbhost}/${dbname}:")
      println "  ${ex}"
    } catch(Exception ex) {
      rslt = 255
      println ">>> general failure"
      println "  ${ex}"
    }

Instead of either catch block executing, I get error messages that look like,

Dec 01, 2023 12:45:13 AM groovy.sql.Sql eachRow
WARNING: Failed to execute:  insert into mytable (name) values ('John Doe'); because: This SQL statement does not return a single ResultSet
>>> query failed against dbhost/wrk:
  java.sql.SQLException: This SQL statement does not return a single ResultSet

Hoping someone can help with Groovy SQL usage where the SQL statements are not known ahead of time as in this case where they are coming from a file.

Note: everything works smoothly for SELECT statements (since they return ResultSets).

Answer 1

for single resultset or rowcount the groovy code could be like this:

@Grab(group='com.microsoft.sqlserver', module='mssql-jdbc', version='12.4.2.jre8')
@GrabConfig(systemClassLoader=true)

import groovy.sql.Sql

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver")
def connectionUrl = "jdbc:sqlserver://...:1433;databaseName=...;user=...;password=...";  

def rsWorker = {hasResult, rs->
    if(hasResult){
        println "resultset:"
        rs.each{ println "  row: ${it}" }
    }else{
        println "update count: ${rs}"
    }
}

Sql.withInstance(connectionUrl){dbconn->
    dbconn.execute("select getdate() as today, 'hello' as world", rsWorker)
    dbconn.execute("create table #tmp(i int)", rsWorker)
    dbconn.execute("select getdate() as today, 'goodby' as world", rsWorker)
    dbconn.execute("update deleteme set iAnnualSum=0", rsWorker)
}

output example:

resultset:
  row: [today:2023-12-01 15:14:54.303, world:hello]
update count: 0
resultset:
  row: [today:2023-12-01 15:14:54.363, world:goodby]
update count: 28

databases like microsoft sql server supporting multiple resultsets and i don't see how to process them in groovy.

however here is a good java example that could be easily ported to groovy: https://learn.microsoft.com/en-us/sql/connect/jdbc/using-multiple-result-sets?view=sql-server-ver16