Re: Cannot insert value in the Temporary Table when accessed from a function via a procedure.

[Rajarshi Biswas]

Here is the complete code with the table definition and the dummy data.


1. Table Defination

CREATE TABLE recurse2(
category_id VARCHAR(20) ,
name VARCHAR(20) NOT NULL,
parent VARCHAR(20));

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');


4. MySQL Hierarchical query procedures

i ) Procedure 1 - Dynamically setting the depth as byDefault it is set to ZERO

CREATE PROCEDURE sDepth()
BEGIN
SET @sql ="SET max_sp_recursion_depth=255";
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;
//


ii) Procedure 2

CREATE PROCEDURE hLevel1 (start_with VARCHAR(32))
proc:
BEGIN
DECLARE temporary_table_exists BOOLEAN;
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
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), level INT);

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,v_level);

CALL sDepth();

CALL hLevel2(v_catagory_id);
END IF;

-- This line is given to view the resultant Temporary Table

SELECT catagory_id,name,parent,level FROM Temporary_Table;

IF temporary_table_exists THEN
DROP TEMPORARY TABLE Temporary_Table;
END IF;
END;
//


iii) Procedure 3 - This is the reqd. RECURSIVE procedure

CREATE PROCEDURE hLevel2 (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 c CURSOR FOR SELECT category_id, name, parent
FROM Recurse2 WHERE parent = start_with;

DECLARE c_level CURSOR FOR SELECT level FROM Temporary_Table WHERE catagory_id = start_with;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

SET error = TRUE;

OPEN c;
IF error THEN
SELECT 'OPEN failed'; LEAVE proc; END IF;

REPEAT

SET v_catagory_id=NULL;

FETCH c INTO v_catagory_id, v_name, v_parent;

IF done=FALSE THEN

IF v_catagory_id IS NOT NULL THEN

OPEN c_level;
FETCH c_level INTO v_level;
CLOSE c_level;

SET v_level := v_level +1;

INSERT INTO Temporary_Table VALUES
(v_catagory_id, v_name, v_parent,v_level);

CALL hLevel2(v_catagory_id);

END IF;
END IF;
UNTIL done = TRUE
END REPEAT;
CLOSE c;
END;
//

5 ) Running the procedures in MySQL


call hLevel1('p001'); //

Importantly, it works fine when the procedure call is made.


6) Make a function - Calling the same procedure from a function cause the error mentioned.

CREATE FUNCTION fnc_dummy3 ()
returns VARCHAR(32)
BEGIN
DECLARE ret VARCHAR(32);
call hLevel1('p001');
return ret;
END;
//


select fnc_dummy3 (); //

ERROR 1146 (42S02): Table '< database-name >.temporary_table' doesn't exist


Please let me know whether you could recreate the error or not .

Answer awaited.

Thanks