We help IT Professionals succeed at work.

Why does this query work in straight MySQL but not in CF?

I'm trying to duplicate a row in a MySQL table and give it a new ID (which is the primary key column.) If I run the query directly through phpMyAdmin, it works. If I run it on my CF page, it gives me the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE tempEvent SET ID = NULL; INSERT INTO calendar SELECT * FROM tempEvent' at line 4

Do you know why this would be? (As a side note, I get the same error if I use the cfqueryparam tag or hardcode a value, and with/without semicolons.) Thanks!

<cfquery datasource="#APPLICATION.ds#" result="copiedEvent">
	CREATE TEMPORARY TABLE tempEvent 
		SELECT * FROM calendar 
		WHERE ID = <cfqueryparam value="#FORM.eventID#" cfsqltype="CF_SQL_INTEGER">;
	UPDATE tempEvent 
		SET ID = NULL;
	INSERT INTO calendar SELECT * FROM tempEvent;
	DROP TEMPORARY TABLE IF EXISTS tempEvent;
</cfquery>

Open in new window

Comment
Watch Question

Senior Software Developer
Commented:
You're actually running 4 separate statements here and most data providers won't accept multiple statements in a single call. You'll need to separate your statements and call them one at a time.

Another way of putting it is that the data provider doesn't like semicolons. Rather than sending everything as a single block of text you'll need to send each statement separately and make sure there are no semicolons in your SQL statement.

Author

Commented:
PERFECT, thank you so much! All working now.