Groovy SQL – or SQL using the groovy.sql.Sql class – has many benefits, but it has some notably unexpected behaviors as well.
GStrings and parameter markers
In order to create code that is protected from SQL injection a GString is treated differently than a String where a SQL statement is supplied to a groovy.sql.Sql method. In particular, if there is a variable embedded in a GString SQL statement it is stripped out and replaced with a parameter marker (“?”). The variable value is then supplied for the parameter (with a setXxx method of PreparedStatement) after the SQL statement with parameter markers is prepared under the covers.
Embedding variables in a GString SQL statement in places that a parameter marker is not valid will cause the statement preparation to fail. This tends to exhibit itself as something like:
java.sql.SQLException: [SQL0104] Token ? was not valid. Valid tokens: ( TABLE LATERAL .
It may raise an exception regarding invalid use of a parameter marker instead if it recognizes an attempt to use a parameter marker but the use is not supported. The following code demonstrates the first condition, where the schema and table name substitutions are replaced with parameter markers.
iSeries.eachRow(“”"
select * from ${imageSchema}.${table}
“”") {
println it
}
This example is easily corrected by forcing it to a String like so.
iSeries.eachRow(“”"
select * from ${imageSchema}.${table}
“”".toString()) {
println it
}
Unfortunately there does not seem to be any way to explicitly create a GString from a String, so combining a resolved GString with a substitutable GString for such purposes as the below SQL expression does not seem possible as the result remains stubbornly a String.
iSeries.eachRow(“”"
select * from ${imageSchema}.${table}
“”".toString() +
“”"
where exportfiletype = ${exportFileType}
“”") {
println it
}
Parameter markers – not really Groovy related
Where you have parameter markers, whether created by Groovy in response to a GString with variables or coded by hand, you can get into some ambiguous situations. It is not necessarily clear what the type of a value being substituted should be, and so you can end up with something like:
java.sql.SQLException: [SQL0417] Combination of parameter markers not valid.
One such situation might be:
iSeries.eachRow(“”"
select * from efplusint.idmpolexp
where (exportfiletype is null and ? is null or exportfiletype = ?)
“”", [row.exportfiletype, row.exportfiletype]) {
println it
}
SQL gets upset because it does not know what the type of the parameter marker is that is testing for “is null”. Here the CAST expression can make SQL happy to do what you want.
iSeries.eachRow(“”"
select * from efplusint.idmpolexp
where (exportfiletype is null and cast(? as varchar(30)) is null or exportfiletype = ?)
“”", [row.exportfiletype, row.exportfiletype]) {
println it
}
