Description:
While fetching data in Hierarchical way from a Table ( which consists of recursive calls -> please refer STEP 5 -> Procedure 3 ) ,
Table/Temporary table cannot be accessed from a function via a procedure , its is not possible to perform
operations on that table for operations line INSERT/DROP etc - it give an error saying the the table doesnt
exist .Whereas, when the procedure is called alone, the desired result is obtained.
Importantly , mu application is in Windows XP .
How to repeat:
Step-1. Table Definition
CREATE TABLE recurse2(
category_id VARCHAR(20) ,
name VARCHAR(20) NOT NULL,
parent VARCHAR(20));
Step-2. Insert-Data
INSERT INTO recurse2
VALUES('p001','ELECTRONICS',NULL),('p0010','2 WAY RADIOS','p006'),('p008','FLASH','p007'),
('p004','LCD','p002'),('p006','PORTABLE ELECTRONICS','p001'),('p005','PLASMA','p002'),
('p007','MP3 PLAYERS','p006'),('p003','TUBE','p002'),
('p002','TELEVISIONS','p001'),('p009','CD PLAYERS','p006');
Step - 3) Procedure 1 - setting the recursion depth
CREATE PROCEDURE sDepth()
BEGIN
SET @sql ="SET max_sp_recursion_depth=255";
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;
//
Step - 4) Procedure 2 - Parent Procedure
CREATE PROCEDURE Prc_001 (start_with VARCHAR(32))
BEGIN
DECLARE temporary_table_exists BOOLEAN;
BEGIN
DROP TABLE IF EXISTS Temporary_Table;
END;
BEGIN
DECLARE v_catagory_id,v_parent,v_name VARCHAR(32);
DECLARE v_level INT;
CREATE TEMPORARY TABLE Temporary_Table
(catagory_id VARCHAR(32), name VARCHAR(32), parent VARCHAR(32));
SET temporary_table_exists=TRUE;
SELECT category_id, name
INTO v_catagory_id, v_name FROM recurse2
WHERE category_id = start_with limit 1;
IF v_catagory_id IS NOT NULL THEN
SET v_level := 1;
INSERT INTO Temporary_Table VALUES
(v_catagory_id, v_name, v_parent);
CALL sDepth();
CALL Prc_002(v_catagory_id);
END IF;
-- This line is given to view the resultant Temporary Table
SELECT catagory_id,name,parent FROM Temporary_Table;
IF temporary_table_exists THEN
DROP TEMPORARY TABLE Temporary_Table;
END IF;
END;
END;
//
Step - 5) Procedure 3 - procedure with a recursive call
CREATE PROCEDURE Prc_002 (start_with VARCHAR(32))
proc:
BEGIN
DECLARE v_catagory_id,v_parent,v_name VARCHAR(32);
DECLARE v_level INT;
DECLARE done, error BOOLEAN DEFAULT FALSE;
DECLARE cs CURSOR FOR SELECT category_id, name, parent
FROM Recurse2 WHERE parent = start_with;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET error = TRUE;
OPEN cs;
IF error THEN
SELECT 'OPEN failed'; LEAVE proc; END IF;
REPEAT
SET v_catagory_id=NULL;
FETCH cs INTO v_catagory_id, v_name, v_parent;
IF done=FALSE THEN
IF v_catagory_id IS NOT NULL THEN
INSERT INTO Temporary_Table VALUES
(v_catagory_id, v_name, v_parent);
CALL Prc_002(v_catagory_id);
END IF;
END IF;
UNTIL done = TRUE
END REPEAT;
CLOSE cs;
END;
//
Step-6) Running the procedure in MySQL - WORKS FINE and PRODUCES THE DESIRED RESULT
call Prc_001('p001'); //
Step- 7) Make a function - Calling the procedure from a function - CAUSE ERROR while running the same
CREATE FUNCTION fnc_001 ()
returns VARCHAR(32)
BEGIN
DECLARE ret VARCHAR(32);
call Prc_001('p001');
return ret;
END;
//
Step-8 )
Select fnc_001 () ; //
ERROR 1146 (42S02): Table 'test.temporary_table' doesn't exist
Description: While fetching data in Hierarchical way from a Table ( which consists of recursive calls -> please refer STEP 5 -> Procedure 3 ) , Table/Temporary table cannot be accessed from a function via a procedure , its is not possible to perform operations on that table for operations line INSERT/DROP etc - it give an error saying the the table doesnt exist .Whereas, when the procedure is called alone, the desired result is obtained. Importantly , mu application is in Windows XP . How to repeat: Step-1. Table Definition CREATE TABLE recurse2( category_id VARCHAR(20) , name VARCHAR(20) NOT NULL, parent VARCHAR(20)); Step-2. Insert-Data INSERT INTO recurse2 VALUES('p001','ELECTRONICS',NULL),('p0010','2 WAY RADIOS','p006'),('p008','FLASH','p007'), ('p004','LCD','p002'),('p006','PORTABLE ELECTRONICS','p001'),('p005','PLASMA','p002'), ('p007','MP3 PLAYERS','p006'),('p003','TUBE','p002'), ('p002','TELEVISIONS','p001'),('p009','CD PLAYERS','p006'); Step - 3) Procedure 1 - setting the recursion depth CREATE PROCEDURE sDepth() BEGIN SET @sql ="SET max_sp_recursion_depth=255"; PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END; // Step - 4) Procedure 2 - Parent Procedure CREATE PROCEDURE Prc_001 (start_with VARCHAR(32)) BEGIN DECLARE temporary_table_exists BOOLEAN; BEGIN DROP TABLE IF EXISTS Temporary_Table; END; BEGIN DECLARE v_catagory_id,v_parent,v_name VARCHAR(32); DECLARE v_level INT; CREATE TEMPORARY TABLE Temporary_Table (catagory_id VARCHAR(32), name VARCHAR(32), parent VARCHAR(32)); SET temporary_table_exists=TRUE; SELECT category_id, name INTO v_catagory_id, v_name FROM recurse2 WHERE category_id = start_with limit 1; IF v_catagory_id IS NOT NULL THEN SET v_level := 1; INSERT INTO Temporary_Table VALUES (v_catagory_id, v_name, v_parent); CALL sDepth(); CALL Prc_002(v_catagory_id); END IF; -- This line is given to view the resultant Temporary Table SELECT catagory_id,name,parent FROM Temporary_Table; IF temporary_table_exists THEN DROP TEMPORARY TABLE Temporary_Table; END IF; END; END; // Step - 5) Procedure 3 - procedure with a recursive call CREATE PROCEDURE Prc_002 (start_with VARCHAR(32)) proc: BEGIN DECLARE v_catagory_id,v_parent,v_name VARCHAR(32); DECLARE v_level INT; DECLARE done, error BOOLEAN DEFAULT FALSE; DECLARE cs CURSOR FOR SELECT category_id, name, parent FROM Recurse2 WHERE parent = start_with; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error = TRUE; OPEN cs; IF error THEN SELECT 'OPEN failed'; LEAVE proc; END IF; REPEAT SET v_catagory_id=NULL; FETCH cs INTO v_catagory_id, v_name, v_parent; IF done=FALSE THEN IF v_catagory_id IS NOT NULL THEN INSERT INTO Temporary_Table VALUES (v_catagory_id, v_name, v_parent); CALL Prc_002(v_catagory_id); END IF; END IF; UNTIL done = TRUE END REPEAT; CLOSE cs; END; // Step-6) Running the procedure in MySQL - WORKS FINE and PRODUCES THE DESIRED RESULT call Prc_001('p001'); // Step- 7) Make a function - Calling the procedure from a function - CAUSE ERROR while running the same CREATE FUNCTION fnc_001 () returns VARCHAR(32) BEGIN DECLARE ret VARCHAR(32); call Prc_001('p001'); return ret; END; // Step-8 ) Select fnc_001 () ; // ERROR 1146 (42S02): Table 'test.temporary_table' doesn't exist