Running multiple SQL statements from Groovy

Question

I'm having trouble running multiple SQL statements in one action from Groovy.

sql = Sql.newInstance("jdbc:mysql://localhost/", "usre", "pass", "com.mysql.jdbc.Driver") sql.execute("USE foo; "); // this works sql.execute("USE foo; USE foo;"); // this fails miserably 

The error I get is "You have an error in the SQL syntax." What gives?

Answer 1

You can simply add the following jdbc url parameter to the connection string

http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html#allowMultiQueries

From the docs:

Allow the use of ';' delimit multiple requests during a single statement (true / false), defaults to "false"

For instance:

 Sql.newInstance("jdbc:mysql://localhost?allowMultiQueries=true", "usre", "pass", "com.mysql.jdbc.Driver") 

Answer 2

The problem is that groovy uses JDBC Statement.execute (), which expects from the statement. Here is a replacement class for groovy Sql that works around this problem (but lacks functionality)

 /** * Not related to mysql, just to distinguish it from Groovy Sql class * Created to solve this problem: http://stackoverflow.com/questions/4286483/running-multiple-sql-statements-from-groovy */ public class MySql { private final String password; private final String connectionString; private final String user; public static newInstance(String connectionString, String user, String password, String driverName) { Class.forName(driverName).newInstance(); return new MySql(connectionString, user, password); } public MySql(String connectionString, String user, String password) { this.connectionString = connectionString; this.user = user; this.password = password; } void execute(String query) { Connection conn = DriverManager.getConnection(connectionString, user, password); try { Statement statement = conn.createStatement(); for (String subQuery : query.split(";")) { if (subQuery.trim() == '') continue; statement.addBatch subQuery } statement.executeBatch(); } finally { conn.close(); } } } 

Answer 3

Paul King, one of the Groovy developers, commented on a problem that I opened so you could tell mysql to resolve several statements (this is not necessarily supported by other RDBMSs)