4 Replies - 12442 Views - Last Post: 03 January 2007 - 09:06 AM Rate Topic: -----

#1 Skinnyarms  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 18-November 06

Multiple queries in a cfquery

Posted 28 December 2006 - 09:37 AM

I've recently changed jobs and am now doing ColdFusion w/ MySQL instead of MS SQL.

With MS SQL I used to be able to do something like this:

	 <cfquery ...>
			INSERT INTO a (
				 ...
			) VALUES (
				 ...
			);

			INSERT INTO b (
				 ...
			) VALUES (
				 ...
			);

			DELETE FROM c;
	 </cfquery>



This doesn't seem to be working for me with MySQL. I'm able to run the queries together in the workbench by separating them with semi-colons, but not when I run them though cfquery, any help from you MySQL/CF programmers would be fantastic!

*PS: I know this looks like a prime example for a stored procedure, but I won't always be able to set those up for our clients.

As Always, Thanks!

Is This A Good Question/Topic? 0
  • +

Replies To: Multiple queries in a cfquery

#2 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1875
  • View blog
  • Posts: 20,284
  • Joined: 17-March 01

Re: Multiple queries in a cfquery

Posted 28 December 2006 - 09:54 AM

Any reason for not doing them in seperate CFQuerys and wrapping with a cftransaction??
Was This Post Helpful? 0
  • +
  • -

#3 Skinnyarms  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 18-November 06

Re: Multiple queries in a cfquery

Posted 28 December 2006 - 03:29 PM

I had always heard that each cfquery tag opened and closed a connection to the database and was therefore less efficient. However, this might be an old wives tale, especially as I'm having a hard time googling it.

I know about the "Maintain Database Connections", but I won't always have access to that.

PS: I found the solution to my problem while searching to find out if reducing the number of cfquery tags is important, and here it is according to this site, although sadly I won't always be able to access this for my clients either. =(

Quote

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

Was This Post Helpful? 0
  • +
  • -

#4 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1875
  • View blog
  • Posts: 20,284
  • Joined: 17-March 01

Re: Multiple queries in a cfquery

Posted 28 December 2006 - 03:35 PM

Wow, didn't know that. Good info! I wonder why it's allowed using the MSSQL driver?! Or at all in a cfquery.
Was This Post Helpful? 0
  • +
  • -

#5 psykoprogrammer  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 2
  • View blog
  • Posts: 72
  • Joined: 09-October 06

Re: Multiple queries in a cfquery

Posted 03 January 2007 - 09:06 AM

Actually Skinny, the default behavior when you setup a ColdFusion DSN is to maintain connections in a pool for a certain time limit. ColdFusion server will keep a pool of open connections to use, and will close them and reopen after a certain timeout period, which can be specified in the DSN settings.

That JDBC driver info is cool. I didn't know that either.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1