Error 1414 OUT or INOUT argument is not a variable or NEW pseudo-variable in BEFORE trigger
Hi all!
I've been trying to implement a solution to modelling a hierarchical structure with MySQL. In a book by KOFLER there are a couple of stored procedures SPs for this. Two of these SPs get the subcategories of books under a given category. But when I try to run them I get this error message:
1414 OUT or INOUT argument 4 for routine find_subcategories is not a variable or NEW pseudo-variable in BEFORE trigger.
Does anybody know how to fix this?
Below you see the procedures
Thanks
Jorge
#########################################################################
DELIMITER $$
DROP PROCEDURE IF EXISTS `db`.`get_subcategories`$$
CREATE DEFINER=`jorge`@`%` PROCEDURE `get_subcategories`(IN startid INT, OUT n INT)
BEGIN
DECLARE cnt INT;
DECLARE cname VARCHAR(60);
DROP TABLE IF EXISTS __subcats;
CREATE TABLE __subcats
(rank INT, level INT, catID INT, catname VARCHAR(60)) ENGINE = HEAP;
SELECT COUNT(*) FROM categories WHERE catID=startID INTO cnt;
IF cnt=1 THEN
SELECT catname FROM categories WHERE catID=startID INTO cname;
INSERT INTO __subcats VALUES(0, 0, startid, cname);
CALL find_subcategories(startid, cname, 1, 0);
END IF;
SELECT COUNT(*) FROM __subcats INTO n;
END$$
DELIMITER ;
##############################################################################
DELIMITER $$
DROP PROCEDURE IF EXISTS `db`.`find_subcategories`$$
CREATE DEFINER=`jorge`@`%` PROCEDURE `find_subcategories`(IN id INT, IN cname VARCHAR(60), IN catlevel INT, INOUT catrank INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE subcats CURSOR FOR
SELECT catID, catName FROM categories WHERE parentCatID=id
ORDER BY catname;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN subcats;
subcatloop: LOOP
FETCH subcats INTO id, cname;
IF done=1 THEN LEAVE subcatloop; END IF;
SET catrank = catrank+1;
INSERT INTO __subcats VALUES (catrank, catlevel, id, cname);
CALL find_subcategories(id, cname, catlevel+1, catrank);
END LOOP subcatloop;
CLOSE subcats;
END$$
DELIMITER ;