MySql stored procedure not found in PHP
- by kaupov
Hello,
I have a trouble with MySql stored procedure that calls itself recursively using PHP (CakePHP). Calling it I receive following error:
SQL Error: 1305: FUNCTION dbname.GetAdvertCounts does not exist
The procedure itself is following:
delimiter //
DROP PROCEDURE IF EXISTS GetAdvertCounts//
CREATE PROCEDURE GetAdvertCounts(IN category_id INT)
BEGIN
DECLARE no_more_sub_categories, advert_count INT DEFAULT 0;
DECLARE sub_cat_id INT;
DECLARE curr_sub_category CURSOR FOR
SELECT id FROM categories WHERE parent_id = category_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_sub_categories = 1;
SELECT COUNT(*) INTO advert_count FROM adverts WHERE category_id = category_id;
OPEN curr_sub_category;
FETCH curr_sub_category INTO sub_cat_id;
REPEAT
SELECT advert_count + GetAdvertCounts(sub_cat_id) INTO advert_count;
FETCH curr_sub_category INTO sub_cat_id;
UNTIL no_more_sub_categories = 1
END REPEAT;
CLOSE curr_sub_category;
SELECT advert_count;
END //
delimiter ;
If I remove or comment out the recursive call, the procedure is working. Any idea what I'm missing here? The categories are 2 level deep.
