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

[Rajarshi Biswas]

hi !

There are some changes in the code ,as there was a nesting (BEGIN...END)problem ,
please find below the runnable code.

i am using MySQL command prompt and copy/pasting the code
please use
-> delimiter // command before pasting the code.

STEP - 1) 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 - 2) 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 - 3) 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 - 4) Running the procedure in MySQL - WORKS FINE PRODUCES THE DESIRED RESULT


call Prc_11('p001'); //


STEP - 5) 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;
//

select fnc_dummy4 (); //

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

Its a compiled code in my PC, please let me know incase there any problem/difficulty to compile or run the code .
I wonder whether i have to report it as a bug or not.
Your suggestions/reply is keenly awaited.

Thanks in advance



Edited 1 time(s). Last edit at 08/24/2006 07:49PM by Rajarshi Biswas.