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

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.