Groovy Apache Commons CSV printHeaders - unable to cast input to ResultSet

My Groovy code to read from a database table and write to a CSV file is working -

Sql dbConnection = Sql.newInstance(dbConnectionParameters)

new FileWriter("items.csv").withWriter { writer ->

    CSVPrinter printer = new CSVPrinter(writer, CSVFormat.RFC4180)

    List resultSet = dbConnection.rows("select * from item limit 5") 

    println "there are ${resultSet.size()} items in the item table"

    resultSet.each {row ->
        printer.printRecord(row.values())
    }
}

But I'm trying to add a header to my CSV file containing the column names from the database table. And Commons CSV provides a printHeaders() method and the Groovy rows() method provides a closure that understands query result metadata (column names, etc).

But when I try this -

Sql dbConnection = Sql.newInstance(dbConnectionParameters)

new FileWriter("items.csv").withWriter { writer ->

    CSVPrinter printer = new CSVPrinter(writer, CSVFormat.DEFAULT)

    List resultSet = dbConnection.rows("select * from item limit 5") { meta ->
        printer.printHeaders(meta as ResultSet)
    }

    println "there are ${resultSet.size()} items in the item table"

    resultSet.each {row ->
        printer.printRecord(row.values())
    }
}

I get a MissingMethodException and looking for some help to understand how I might handle this.

Here's the exception -

Caught: groovy.lang.MissingMethodException: No signature of method: com.denodo.vdb.jdbcdriver.VDBJDBCResultSetMetaData.getMetaData() is applicable for argument types: () values: [] Possible solutions: getMetaClass() groovy.lang.MissingMethodException: No signature of method: com.denodo.vdb.jdbcdriver.VDBJDBCResultSetMetaData.getMetaData() is applicable for argument types: () values: [] Possible solutions: getMetaClass()

My (lame?) analysis - printHeaders() accepts a java.sql.ResultSet class as an argument and I'm providing a VDBJDBCResultSet object. Some background - I'm using a (required) database driver that extends/implements the java.sql.ResultSet interface, hence the VDBJDBCResultSet object. I thought that I could cast that VDBJDBCResultSet object to a ResultSet and that would be the right approach for printHeaders() but I'm clearly lost as it doesn't work. I tried a number of different approaches to achieving that cast but none of them worked.

Initially I tried no cast,

List resultSet = dbConnection.rows("select * from item limit 5") { meta ->
    printer.printHeaders(meta)
}

Then using Groovy syntax I tried casting meta to a ResultSet,

List resultSet = dbConnection.rows("select * from item limit 5") { meta ->
    printer.printHeaders(meta as ResultSet)
}

And then in desperation I tried things like,

List resultSet = dbConnection.rows("select * from item limit 5") { meta ->
    printer.printHeaders((ResultSet) meta)
}

None of them worked and further complicating matters for me, apparently ResultSet has a "parallel" class, ResultSetMetaData, which actually holds the metadata but this parallel aspect seems to be (hopefully?) transparent as I notice that the signature for Commons CSV printHeaders() is public void printHeaders( java.sql.ResultSet resultSet ) and my assumption is that it completely works and it's the fact that mine is passing a VDBJDBCResultSet that is creating my challenge. Since printHeaders() accepts a ResultSet that must somehow (magic?) give it access to the ResultSetMetaData.

Any help is appreciated.

CSVPrinter.printHeaders requires ResultSet as a parameter (not metadata)

https://commons.apache.org/proper/commons-csv/apidocs/org/apache/commons/csv/CSVPrinter.html#printHeaders-java.sql.ResultSet-

There is another method CSVPrinter.printRecords(ResultSet resultSet, boolean printHeader) that could do all work for you

so, we have to find how to get ResultSet from groovy Sql object

the easiest way Sql.query( query, Closure ):

https://docs.groovy-lang.org/latest/html/api/groovy/sql/Sql.html#query(groovy.lang.GString,groovy.lang.Closure)

the following code should work:

CSVPrinter printer = new CSVPrinter(writer, CSVFormat.DEFAULT)

dbConnection.query("select * from item limit 5") { rs ->
    printer.printRecords(rs, true)
}

unfortunately this does not give you ability to print rows count

also you can extend your code to this:

    CSVPrinter printer = new CSVPrinter(writer, CSVFormat.RFC4180)

    List resultSet = dbConnection.rows("select * from item limit 5") 

    println "there are ${resultSet.size()} items in the item table"
    if(resultSet.size()>0){
        printer.printRecord(resultSet[0].keySet())  //print names from the first row
    }

    resultSet.each {row ->
        printer.printRecord(row.values())
    }

Sql.rows() returns list of GroovyRowResult and each row is represented like a map where each key-value pair represents column name-value