Groovy SQL is a wonderful tool but some of its details are not always obvious from casual inspection – at least circa 1.5.6. (See http://6by9.wordpress.com/2009/05/01/groovy-sql-musings.) One of these is how the Map behavior does not quite match a HashMap.
First it is important to realize whether you are working with a groovy.sql.GroovyResultSet or a groovy.sql.GroovyRowResult, because GroovyResultSet is functionally just delegating property retrieval to the underlying java.sql.ResultSet while the GroovyRowResult actually implements the java.util.Map interface. These are two very different implementations into a very similar syntax.
So which one are you dealing with?
To get a GroovyResultSet user sql.eachRow. To get a GroovyRowResult use sql.firstRow or you can get a List with sql.rows.
It is not really related to the topic, but you can also get the java.sql.ResultSet if you would like with sql.query.
The difference in Map behavior is highlighted when you try something like:
sql.eachRow("""
...
""") { row ->
println row.noColumnNamedThis
}
This uses a GroovyResultSet and will give you an SqlException appropriate to the underlying database. In DB2 it looks like this.
Exception thrown: com.ibm.db2.jcc.a.SqlException: [jcc][10150][10300][3.51.90] Invalid parameter: Unknown column name noColumnNamedThis. ERRORCODE=-4460, SQLSTATE=null
If you use a GroovyRowResult you get something slightly different.
def rows = sql.rows("""
...
""")
rows.each { row ->
println row.noColumnNamedThis
}
Now it will give you MissingPropertyException rather than an SqlException.
Exception thrown: groovy.lang.MissingPropertyException: No such property: noColumnNamedThis for class: groovy.sql.GroovyRowResult
If you want it to behave like a HashMap, one approach is to just turn it into one. I’m not saying this is the best way to do so, but to provide an example:
def rows = sql.rows("""
...
""")
rows.each { row ->
def rowMap = [:]
row.keySet().each { column ->
rowMap[column] = row[column]
}
println rowMap.noColumnNamedThis
}
This will nicely return null for each row as a good Map should.
