mySql Query works in query browser but fails when I run in CFquery

Question

At first I was thinking I was running into an issue with cfqueryparam and mysql. However when I change substitute them with static values I get the same error. This is stumping me, I'm too used to Microsoft SQL Server I guess. Any help would be greatly appreciated.

Here's the query, this works perfectly in mySql query browser, but fails when I run in cfquery:

LOCK TABLE categories WRITE;

SELECT @myRight := rgt FROM categories WHERE catid = <cfqueryparam cfsqltype="cf_sql_integer" value="#parentCategoryId#">;

UPDATE categories SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE categories SET lft = lft + 2 WHERE lft > @myRight;

INSERT INTO categories(categoryName, lft, rgt) VALUES(<cfqueryparam cfsqltype="cf_sql_varchar" value="#newCatName#">, @myRight, @myRight + 2);

UNLOCK TABLES;

I'm getting 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 'SELECT @myRight := rgt FROM categories WHERE catid = 5; UPDATE categories SE' at line 2

VENDORERRORCODE:1064 SQL State: 42000

Answer 1

Unless you've explicitly allowed it on your MySQL server, you can't have multiple sql statements in the same cfquery. MySQL denies this by defualt, so each of your update and insert statements have to be in their own cfquery tags.

Answer 2

CFGears is right, you can't put multiple queries in a single statement.

To do this from cf you'll need to eschew mySQL variables. (That's good anyhow because if you stick to more-or-less standard SQL your app will port easier to some other brand of table server.) Something like this? You'll need to put lock tables and unlock tables stuff around this if you're dealing with myISAM tables. Doing it in a transaction will work fine if you're working with InnoDB.

Something like this may work.

<cfquery name ="getright" datasource="#whatever#">
SELECT rgt 
  FROM categories 
 WHERE catid = <cfqueryparam cfsqltype="cf_sql_integer" value="#parentCategoryId#">
</cfquery>

<cfquery name="rgt_up_2" datasource="#whatever#">
UPDATE categories 
   SET rgt = rgt + 2 
 WHERE rgt >
  (SELECT rgt 
     FROM categories 
    WHERE catid = <cfqueryparam cfsqltype="cf_sql_integer" value="#parentCategoryId#">)
</cfquery>

<cfquery name="lft_up_2" datasource="#whatever#">
UPDATE categories 
   SET lft = lft + 2 
 WHERE lft >
  (SELECT rgt 
     FROM categories 
    WHERE catid = <cfqueryparam cfsqltype="cf_sql_integer" value="#parentCategoryId#">)
</cfquery>

<cfquery name="insrgt" datasource="#whatever#">
INSERT INTO categories(categoryName, lft, rgt) VALUES(
<cfqueryparam cfsqltype="cf_sql_varchar" value="#newCatName#">,
<cfqueryparam cfsqltype="cf_sql_integer" value="#getright.rgt#">,
<cfqueryparam cfsqltype="cf_sql_integer" value="#getright.rgt#"> + 2)
</cfquery>