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

[Rajarshi Biswas]

I had already posted the script for creating the table and its corrosponding dummy datas. Please find it below once again.


Step-1. Table Defination

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_11 (start_with VARCHAR(32))

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

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_22(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_22 (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_22(v_catagory_id);
END IF;
END IF;
UNTIL done = TRUE
END REPEAT;
CLOSE cs;
END;
//

Step-6) Running the procedure in MySQL - WORKS FINE PRODUCES THE DESIRED RESULT


call Prc_11('p001'); //


Step- 7) Make a function - Calling the procedure from a function - CAUSE ERROR while running

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

I hope now u can run it .
Thanks in advance