Turning On Multiple Statements In ColdFusion 8 MySQL 4/5 Datasource

Thanks for the tip!
I recently converted a site from MS SQL to MySQL 5 and ran into that issue. At the time I just tweaked the CF code to work around it, but I know I will use this in the future.

Lest someone get the wrong idea from your post, ColdFusion developers *definitely* need to worry about SQL injection attacks. CF is just as vulnerable to poorly coded pages as PHP or any other language. CF does however have the best weapon in this battle: the CFQUERYPARAM tag. Correct use of CFQUERYPARAM can protect your apps from SQL injection better than this MySQL connection parameter ever could.

@Ben,

Yes.. probably too far. I know I use the technique often for grabbing autogenerated primary key values directly after inserts with SELECT LAST_INSERT_ID().

That being said, I think there is something to be said for safe defaults in software, letting the advanced user enable capabilities that only they will know to look for. Hopefully the JDBC connector threw a very helpful error message. Did it at least tell you how to enable what you wanted to do, or did it just flat out fail with no explanation?

Brians suggestion of how to use the SELECT LAST_INSERT_ID() would be very helpful. Up to this point I've always had one or a combination of fields that i knew were distinct but i may not always have that luxury. It didn't occur to me that i could put 2 queries together. I am curious how the results from the second query are stored... same name as the first query or no?

@Ben,

I posted a more thorough explanation of my technique to my blog, if you'll allow the link:

http://ghostednotes.com/index.cfm/2008/4/25/CFQUERY-and-autonumber-primary-keys

So this means multiple queries can be run in one cfquery, delimited by (I assume) a semicolin?

Is using multiple statements in one cfquery block faster than making individual cfquery calls. I assume this would be the case.

Thanks for the tidbit Ben. I have always worked around this limitation in the past, but I absolutely need multiple statements for something I'm working on. I did a google search for "mysql 5 allow multiple statements" and your post came up.

More string parameter can be found in the MySQL Jconnector documentation. http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html

I am not sure this is the good place for this question but it could be related to the jconnector. I have a new trouble now with the new MySQL 4/5 connector with decimal,flaot,numeric notation. This connector return scientific notation with a MySQL 5.1. Do you have an idea to convert scientific notation to decimal notation ?

By the way like other said, you must be 100% sure your CF web application is secure to turn on this kind of settings. I could imagine how destructive multiple queries could be.

This doesn't seem to be the case for CF 7, however, CF 8 works just as you desribe. I tried adding the line to the Connection String in each server's datasource and only CF 8 would honor the request for multiple statements. CF 7 errors out.

Another Kinky-archive post to the rescue! I had no idea this was fixable, just knew it didn't...quite... work... as ... expected. Not hard to work around but this is better.

You rock, Ben. (but you know that already)

lost an L! all better now.

Ben, this works fine on my production server, but one weird thing I'm running into is it won't work on my development machine. I'm using CF8/MySQl5.

I set the connection string just like on production, and it errors out on my multi statement. Weird!

Thanks!

Ben, I figured out how to fix it. I simply deleted the existing DSN, then recreated it using the connection setting. That seemed to fix it.

Thanks!

This trick doesn't return multiple resultsets.
........................................

<cfset query={}/>
<cfquery name="query.resultSet" result="query.details" datasource="dev">
select 0;
select 1;
</cfquery>
<div>query: <cfdump var="#query#"/></div>
........................................

You can only return multiple resultsets from cfstoredproc.

Luckily, there are some easy ways to do this.

You can also maintain your parameters if you want to get fancy. I just demonstrate the basics.

Microsoft SQL Server (MSSQL)
http://msdn.microsoft.com/en-us/library/aa933299%28SQL.80%29.aspx
........................................

<cfset query={}/>
<cfsavecontent variable="query.sql">
select 0;
select 1;
</cfsavecontent>
<cfset query.resultSets={}/>
<cfstoredproc result="query.details" returncode="true" datasource="dev" procedure="[dbo].[sp_executesql]">
<cfprocparam dbvarname="@stmt" value="#query.sql#" cfsqltype="CF_SQL_LONGVARCHAR"/>
<cfprocresult name="query.resultSets.i1" resultset="1"/>
<cfprocresult name="query.resultSets.i2" resultset="2"/>
</cfstoredproc>
<div>query: <cfdump var="#query#"/></div>
........................................

It's a little harder in MySql, because you'd have to create your own procedure to do it.

MySQL
http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html

@ben

> stored procedures

The example I've shown using prepared statements is just directly using the same method ColdFusion/Java uses to do parameterized queries. This isn't really using stored procedures. This is just a way to do multiple statements/resultsets in a single database call.

Usually, you make a stored procedure to handle some database stuff you want to encapsulate, or hide, do frequently, or optimize.

They can also be part of an authorization/access scheme. You can grant execution on specific stored procedures and grant select on specific views, without granting access to the underlying tables. This is especially helpful if there are diverse needs, e.g., administrator/editor, guest/reader.

The main thing they're good for is create/update/delete scripts, since these almost never change.

They are harder to use for custom queries like searches or report builders. Albeit not impossible, their performance benefits are lessened.

The big benefit is cached execution plans. The database server can cache how it executed a certain statement, so the next time the statement is run, it knows exactly what to do.

Another benefit is transactions for multiple statements.

Also beneficial are a custom return value, input/output parameters, and multiple resultsets.

hi i have followed your example and done as you instructed but i still get error. maybe am missing some thing. please guide. here is my code.

<cfcomponent>
<cffunction name="listProperty" access="remote" returntype="string">
<cfargument name="ctry_name" type="string" required="yes"/>
<cfargument name="info" type="struct">
<!---<cfargument name="location" type="string" required="yes"/>
<cfargument name="plot_number" type="string" required="yes"/>--->

<!--- Query's Where statement is Checking to see if the username already exists in the database --->
<cfquery name="getproperty" datasource="realestate">
SELECT location, plot_number
FROM homes
WHERE location = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.info.location#">
AND plot_number = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.info.plot_number#">
</cfquery>
<!--- If our query shows a username match then using recordCount we can tell the user that his username already exists in our database --->
<cfif getproperty.recordCount GT "0">
<cfreturn "Sorry that Property is already listed.">
<!--- If our recordCount is not greater than "0" we continue on and process the new member--->
<cfelse>
<!--- Insert Data (preferably after cleaning it) see my "advanced form checking tutorial" if you are not sure how to approach this task--->
<cftransaction>
<cfquery name="ctry_list" datasource="realestate">
SELECT ctry_id, ctry_name
FROM country
WHERE ctry_name = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.ctry_name#">
</cfquery>

<cfquery name="galleryPics" datasource="realestate">
INSERT INTO homeImages(img1, img2, img3, img4)
VALUES(<cfqueryparam value="#arguments.info.img1#">, <cfqueryparam value="#arguments.info.img2#">, <cfqueryparam value="#arguments.info.img3#">, <cfqueryparam value="#arguments.info.img4#">)
</cfquery>

<cfquery name="homeimgID" datasource="realestate">
<!---SELECT max(img_id) AS newimg_id--->
SELECT LAST_INSERT_ID() AS newimg_id
FROM homeImages
</cfquery>
<!---set newimg_id = last_insert_id();--->

<cfquery name="list" datasource="realestate">
INSERT INTO homes(ctryid_home, city, location, price, type, square_feet, date_listed, bedrooms, bathrooms, pool, backyard, closets, img, lat, lng, description, tour, plot_number, kitchen, living_room, garage, roof_type, memid_home, category, imgid_home, garages, tile_floor, hardwood_floor, laminate_floor, balcony)
VALUES("#ctry_list.ctry_id#",
<cfqueryparam value="#arguments.info.city#">,
<cfqueryparam value="#arguments.info.location#">,
<cfqueryparam value="#arguments.info.price#">,
<cfqueryparam value="#arguments.info.type#">,
<cfqueryparam value="#arguments.info.square_feet#">,
<cfqueryparam value="#arguments.info.date_listed#">,
<cfqueryparam value="#arguments.info.bedrooms#">,
<cfqueryparam value="#arguments.info.bathrooms#">,
<cfqueryparam value="#arguments.info.pool#">,
<cfqueryparam value="#arguments.info.backyard#">,
<cfqueryparam value="#arguments.info.closets#">,
<cfqueryparam value="#arguments.info.img#">,
<cfqueryparam value="#arguments.info.lat#">,
<cfqueryparam value="#arguments.info.lng#">,
<cfqueryparam value="#arguments.info.description#">,
<cfqueryparam value="#arguments.info.tour#">,
<cfqueryparam value="#arguments.info.plot_number#">,
<cfqueryparam value="#arguments.info.kitchen#">,
<cfqueryparam value="#arguments.info.living_room#">,
<cfqueryparam value="#arguments.info.garage#">,
<cfqueryparam value="#arguments.info.roof_type#">,
<cfqueryparam value="#arguments.info.memid_home#">,
<cfqueryparam value="#arguments.info.category#">,
"#homeimgID.newimg_id#",
<cfqueryparam value="#arguments.info.garages#">,
<cfqueryparam value="#arguments.info.tile_floor#">,
<cfqueryparam value="#arguments.info.hardwood_floor#">,
<cfqueryparam value="#arguments.info.laminate_floor#">,
<cfqueryparam value="#arguments.info.balcony#">
)
</cfquery>
</cftransaction>
</cfif>
</cffunction>
</cfcomponent>

Just what I was looking for. Thanks!

@Will,
Thank you! Saved me a huge headache from beating my head on my desk. Recreating the DSN resolved my issue.

And thanks, Ben, for your great site. It's saved my butt many times.