Sumo Consulting^2.0

I've answered this on a couple of mailing lists now so I thought I'd blog the solution so it has a chance in Google.

The problem: Combining more than one SQL statement in a a single CFQUERY tag. IE:

<cfquery name="myQuery" datasource="ds"> CREATE TABLE user_stuff (...); CREATE TABLE other_stuff (...); </cfquery>

The Solution: By default, as a security measure, the MySQL Connector/J JDBC driver does not allow multiple queries to be concatenated in a single database request. This behavior is in part an effort to prevent SQL Injection attacks on the database.

You can override this setting in the JDBC driver's datasource configuration to allow multiple queries by adding "allowMultiQueries=true" to the JDBC's URL string. For example, a valid JDBC URL may look like this:

jdbc:mysql://192.168.1.1:3306/databasename?allowMultiQueries=true

But wait, there's more... There are several other nifty configuration options available including failover and performance options. It's really worth taking a look at them.

• Comments (6)