Multiple queries with CFMX-MySQL JDBC connector
I found a little snippet the other day and included it in the tail end of a previous post - however, on reflection I thought it deserved a post of it's own.
If you want to utilise multiple queries per sql statement and you are using the MYSQL JDBC connector you need to add the following to your jdbc connector querystring.
allowMultiQueries=true
So you querystring would look like: This will allow you to do queries like the following to return autoincrement values:
<cfquery name="qInsert" datasource="mydsn">
INSERT INTO tbl_demo (name)
VALUES ( <cfqueryparam value="#myName#">);
SELECT last_insert_id() as newID
</cfquery>
<cfoutput>ID of value is: #qInsert.newID#</cfoutput>
INSERT INTO tbl_demo (name)
VALUES ( <cfqueryparam value="#myName#">);
SELECT last_insert_id() as newID
</cfquery>
<cfoutput>ID of value is: #qInsert.newID#</cfoutput>
Please note however that this functionality is disabled as it can leave the door open for SQL Injection attacks. However, as long as you always use cfqueryparam for all the dynamic parts of your query you will be fine.
Yes and No - you are opening a potential way that sql injection queries can happen, by appending a semi-colon and writing a new query. But if you are correctly using cfqueryparam's in all your queries then it won't happen.
And conversely by not allowing multiple queries, you are not protecting yourself from all potential SQL injection's attacks.
Cheers,
Mark
Additionally, bulk insertion of data should be quicker too.
Thanks
Martin