2

I've just run into a confusing issue in Groovy while trying to modify a MySQL database. What appears to be identical code throws an Exception unless my GroovyString is explicitly converted to a java.lang.String:

import groovy.sql.Sql
def sql = Sql.newInstance('jdbc:mysql://localhost/test?useUnicode=yes&characterEncoding=UTF-8', 'user', 'pass', 'com.mysql.jdbc.Driver')
def tableName = 'my_table'
sql.execute "truncate $tableName"

throws:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 ''my_table'' at line 1

Whereas the following works without problems:

sql.execute "truncate $tableName".toString()

This is surprising. Should I have expected this problem, and if so in what situations are GroovyString and String instances likely to be treated differently?

Armand
  • 23,463
  • 20
  • 90
  • 119

1 Answers1

5

The difference here is that the Groovy Sql class explicitly works with GStrings to ensure parameters are properly quoted (as explained in the documentation).

So it converts the first example to

truncate 'my_table'

Which is wrong (as the error explains)

You can also use:

sql.execute "truncate ${Sql.expand(tableName)}"
Armand
  • 23,463
  • 20
  • 90
  • 119
tim_yates
  • 167,322
  • 27
  • 342
  • 338
  • +1 I had the same problem though simply with 'asc' and 'desc' and this resolved it. It should be marked as an accepted answer. And it was driving us crazy because printing the string to console showed a perfectly valid SQL string! – msanjay Jan 28 '14 at 14:42