Using Groovy's ${...} with SQL

Question

I have a question about using a string in a JDBC SQL query. Here are two examples and I expect both to work, but they don't.

Working version ...

    tabl  = "Action"
    query = "SHOW FULL COLUMNS FROM `Action`;"  
    println "   "+ query
    dbConnection.eachRow( query ){

In error variant:

    tabl  = "Action"
    query = "SHOW FULL COLUMNS FROM `${tabl}`;"
    println "   "+ query
    dbConnection.eachRow( query ){

The error comes back as an SQL syntax error. As you can see the statements are textually identical.

The output show the statement, then an error:

   SHOW FULL COLUMNS FROM `Action`;
   May 20, 2013 10:52:01 AM groovy.sql.Sql eachRow
   WARNING: Failed to execute: SHOW FULL COLUMNS FROM `?`; because: 
      Parameter index out of range (1 > number of parameters, which is 0).
   May 20, 2013 10:52:01 AM groovy.sql.Sql eachRow

I think that's just Groovy trying to look for a culprit. When I feed the literal string to the JDBC connection it works just fine for the 'Action' table.

I'm hoping someone can explain the error and offer a fix.

For those reading, I found this option as a workaround:

query = "SHOW FULL COLUMNS FROM `"+ tabl.toString() +"`;" 

While there might be a less verbose option, using "+"; to me it feels as if using ${tabl} should work.

Thanks in advance,

Answer 1

I ran into this as well. Your workaround works, but a cleaner way would be to call toString() on the org.codehaus.groovy.runtime.GStringImpl version. That is, if you do not need any of the prepared statement features or protection on the execute. This is due to the Groovy SQL engine trying to turn it into a prepared statement, because it sees the original string as a GString. In order to prevent SQL injection attacks, you would want this though when using values that could be provided from the end-user. In your case and mine that was not a concern so toString() works just fine.

See: http://groovy.codehaus.org/Tutorial+6+-+Groovy+SQL

Answer 2

I think the problem is that when you use the in-string variable produces a different object type than when you don't use it. One is a String, the other a GString.

E.g., see this script and the output:

def a = "123"
def b = "abc"+a
def c = "abc${a}"
println b.class
println c.class

>>

class java.lang.String
class org.codehaus.groovy.runtime.GStringImpl

It seems that the eachRow function is sensitive to this difference for some reason. The two functions you're using are

• http://groovy.codehaus.org/api/groovy/sql/Sql.html#eachRow(groovy.lang.GString,%20groovy.lang.Closure)
• http://groovy.codehaus.org/api/groovy/sql/Sql.html#eachRow(java.lang.String,%20groovy.lang.Closure)

But I can't see why they'd behave differently..

Another workaround would be to call toString on the query variable - that will cast it back to a String:

def d = c.toString()
println d.class

>>

class java.lang.String