CODEWITHSUNDEEP
javagroovy
Tomas Greif
Tomas Greif

Reputation: 22661

Determine data types of Oracle query results in groovy.sql

Is there a way how I could determine data types of individual columns in query results before I start processing these?

import groovy.sql.*

rdb = Sql.newInstance('jdbc:oracle:thin:@(conn)',
                      'usr',
                      'pwd','oracle.jdbc.driver.OracleDriver')

String query = '''select * from sample_data'''

// Need to know data types of columns here so I can prepare
// for the row processing

rdb.eachRow(query) {
    // do something with each row 
}

rdb.close()

I was thinking to query Oracle data dictionary, but this is not the best way if you have generic queries - I would have to create some table from the query first.

Upvotes: 1

Views: 941

Answers (1)

Emmanuel Rosa
Emmanuel Rosa

Reputation: 9885

To get the SQL types out of the query result set you'll need the result set's ResultSetMetaData. Luckily, Groovy Sql provides a way to get it. It's a bit odd, but it works:

def columnTypes = [:]
def metaClosure = { metaData ->
    /* I'm called once by Sql.eachRow() with a ResultSetMetaData. */
    columnTypes = (1..metaData.columnCount).collectEntries { 
            [(metaData.getColumnName(it)): metaData.getColumnType(it)] 
        }
}

rdb.eachRow('SELECT * FROM person', metaClosure) { row ->
    /* 
     * The result set SQL types and row values are available here.
     * Examples:
     * def value = row['column_name']
     * def type = columnTypes['column_name']
     */
}

In my example, I used the column names as keys, but it's possible (and more reliable) use column indices instead. The returned SQL types are those in java.sql.Types.

Upvotes: 4

Related Questions