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

Posted April 24, 2008 at 2:31 PM by Ben Nadel

Tags: ColdFusion, SQL

Reader Comments

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.

@Terry,

Glad to help in some way :)

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.

@Brian,

Yes, very true. Sorry if I came off a bit too cocky in my post. It just seemed like a precaution that was swinging WAY too far in the other direction.

@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?

@Brian,

The error message was actually very little use. All it told me was that I had a syntax error near INSERT. A useful error would have been something like:

"You are attempting to use multiple statements as defined by ';' This option needs to be enabled in your database connection."

Now, something like that would have been sweeeeeet.

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?

@Eric,

Oh heck yeah.

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

@Michael,

I like to think so, but I have no numbers on that.

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.

@Brad,

I might be the MySQL drivers. I think in CF7 there is not built-in MySQL 4/5 driver. Maybe earlier versions of MySQL don't support this?

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.

@Michael,

No worries, I updated and added the L :)

Just had to figure out how to do this on ColdFusion MX7 and MySQL 3/4:

http://www.bennadel.com/index.cfm?dax=blog:1542.view

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!

@Will,

Hmm, that is odd. Are you sure it's MySQL 5? I can't think of any reason why it wouldn't be the same if you use the same connection string?

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!

@Will,

Ah gotcha. Good thought. I would have never thought of that!

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

@Alex,

To be honest, I've never used a stored procedure in my life; but, I have heard that the one big benefit that they offer is the return of multiple result sets and multiple values. I am not sure how often I would use such a feature, though.

@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.

@Alex,

Ahh, I see what you're doing - executing the SQL statement. I was distracted by the use of the storedproc tags to see what it was actually doing.

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.

Post A Comment

Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>
	Remember my information
	Subscribe to comments
	Send me a copy of this comment