Be careful using GStrings for SQL

If you are like me, you love the ease with which you can create Groovy scripts to manipulate your databases. Gone are the days of writing utility classes to make these changes, thanks to Groovy and the SQL package. But if you aren't careful, you can run into a couple of strange errors if you use the GStrings.

For the below example, I have a table named LOCATION in my 'test' database that has two columns: location_id and location_name. The example below works fine and you notice that I am not using any variables in the SQL statement

import groovy.sql.Sql

db = Sql.newInstance('jdbc:mysql://127.0.0.1:3306/test', 'root', 'root', 'com.mysql.jdbc.Driver')

def sqlStatement = """select * from location where location_id > 3"""

db.eachRow (sqlStatement){ 
   println it
}

println "Done!"

Next, try making the SQL a bit more dynamic by specifying the table name, column name and key value as variables within the GString and watch what happens.

import groovy.sql.Sql

db = Sql.newInstance('jdbc:mysql://127.0.0.1:3306/test', 'root', 'root', 'com.mysql.jdbc.Driver')

def tableName = 'location'
def columnName = 'location_id'
def key = 3
def sqlStatement = """select * from $tableName where $columnName > $key"""

db.eachRow (sqlStatement){ 
   println it
}

println "Done!"

WARNING: Failed to execute: select * from ? where ? > ? because: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''location' where 'location_id' > 3' at line 1

The quick fix I found was to call the toString() method on the GString and this resolves the problem. I found a line in "Groovy In Action" which helps explain the problem:

The special use of GStrings as SQL statements limits the use of placeholders to places where a question mark would otherwise be allowed in a prepared statement.

import groovy.sql.Sql

db = Sql.newInstance('jdbc:mysql://127.0.0.1:3306/test', 'root', 'root', 'com.mysql.jdbc.Driver')

def tableName = 'location'
def columnName = 'location_id'
def key = 3
def sqlStatement = """select * from $tableName where $columnName > $key""".toString()

db.eachRow (sqlStatement){ 
   println it
}

println "Done!"

Keep this in mind the next time you use the Groovy SQL class!