Error while implementing SQL Tree

Question

Hi I'm getting the error while executing following code in MySQL :

BEGIN
DECLARE right_most_sibling INTEGER;
SET right_most_sibling
= (SELECT rgt
FROM Personnel
WHERE emp = 'Albert');
UPDATE Personnel
SET lft = CASE WHEN lft > right_most_sibling
THEN lft + 2
ELSE lft END,
rgt = CASE WHEN rgt >= right_most_sibling
THEN rgt + 2
ELSE rgt END
WHERE rgt >= right_most_sibling;
INSERT INTO Personnel (emp, lft, rgt)
VALUES ('Bert', right_most_sibling,
(right_most_sibling + 1))
END;

I'm getting the following error :

1064 - 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 'DECLARE right_most_sibling INTEGER' at line 2

Any ideas, how do I fix it ?

I was following this tutorial

link text

Thank you

Answer 1

It looks like you're using stored procedure syntax, without declaring a stored procedure header.

You can't use DECLARE unless it's in a compound statement in the body of a stored procedure.

http://dev.mysql.com/doc/refman/5.1/en/declare.html says:

DECLARE is allowed only inside a BEGIN ... END compound statement...

http://dev.mysql.com/doc/refman/5.1/en/begin-end.html says:

BEGIN ... END syntax is used for writing compound statements, which can appear within stored programs.

The tutorial you're linking to was written by Joe Celko, and I'd assume he's writing code that works on Oracle or maybe IBM DB2. It's unlikely that he ever uses MySQL for his example code.

---

Re your comment, I got it to at least accept the syntax by declaring the block within a procedure:

DELIMITER //
CREATE PROCEDURE insert_new_node()
BEGIN
DECLARE right_most_sibling INTEGER;
. . . 
END//
DELIMITER ;

I have not tested the procedure, because I'm not going to run through the entire tutorial.

Answer 2

Don't try to just copy/paste code -- read & understand the concepts, then put them to use on your specific problem.

In my answer to your previous question, I edited and added a second link to page showing how to implement this stuff at the PHP level (since you're using PHP).

The Celko stuff is good -- as Bill says, you're not going to be able to just drop it into mySQL without thinking about what's going on.

First understand the concepts, then go ahead an build your own implementation.