Wednesday, June 10, 2009

Groovy & MySQL: ALTER TABLE

I was having trouble executing a query similar to the following with in Groovy against a MySQL database:
    ALTER TABLE MAP AUT0_INCREMENT = 109
The code:
    import groovy.sql.Sql
...
def setAutoIncrement(table, value) {
def sql = Sql.newInstance(jdbcUrl, jdbcUsername, jdbcPassword, jdbcDriver)
def query = "ALTER TABLE $table AUTO_INCREMENT = $value"
sql.execute(query)
}
I was getting the folowing error:
    com.mysql.jdbc.exceptions.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 ''MAP' AUTO_INCREMENT = '109'' at line 1
Seems like Groovy was adding single quotes around the table name and the value. I resolved this by strong-typing the query variable to a String to subvert the GString interpolation:
   String query = "ALTER TABLE $table AUTO_INCREMENT = $value"
Quoting the table and value in MySQL is invalid when running the MySQL command-line tool as well.

No comments:

Post a Comment