dynamically set the db in a sql query

Question

I try to run the same query in several dbs in mysql:

def m='xxx'
def dbs =  ['DB05DEC05','DB06DEC06','DB07DEC07','DB08DEC08','DB09DEC09','DB10DEC10']
def sql =Sql.newInstance("jdbc:mysql://localhost:3306", "root","", "org.gjt.mm.mysql.Driver")
dbs.each{
 db-> sql.eachRow("select * from ${db}.mail where mid=$m", { println "\t$db ${it.mid}"} );
}

This gives an error:

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 ''DBJAN05DEC05'.mail where mid='xxx'

Groovy apparently does some custom stuff with quotes and asks you not to use quotes in the sql (notice mid=$m, if you use mid='$m' it warns you against using the quotes). The problem is that in the first $ I dont know want quotes at all, the quotes are the problem...

groovy 1.7 on vista. thanks

editing: I have found a similar question, but it does not have an accepted answer either... http://stackoverflow.com/questions/1621242/groovy-gstring-issues

Answer 1

The problem is that the SQL query method sees the GString, with its embedded variable references, and turns each reference into a ? in a prepared statement.

So:

sql.query("select * from table where col = ${value}")

... is equivalent to:

sql.query("select * from table where col = ?", [ value ])

But also:

sql.query("select * from ${db}.table where col = ${value}")

is equivalent to:

sql.query("select * from ?.table where col = ?", [ db, value ])

... which fails at the DB layer because the select statement is not valid.

The obvious workaround is to use the explicit prepared statement version of query().

dbs.each{ db-> 
    sql.eachRow("select * from ${db}.mail where mid=?", m, { 
       println "\t$db ${it.mid}"
    });
}

However, the Sql class gives you an expand() method, that appears to be designed for this purpose.

dbs.each{ db -> 
   sql.eachRow(
      "select * from ${Sql.expand(db)}.mail where mid=${m}", 
      { println "\t$db ${it.mid}"} );
}