I have a stored procedure in MySQL whose definition as below, which returns 4 ResultSets to the requesting script:
DROP PROCEDURE IF EXISTS `GetAllParts`;
delimiter ;;
CREATE PROCEDURE `GetAllParts`(
IN inRefNo VARCHAR(20)
)
BEGIN
DECLARE pType CHAR(1);
DECLARE pMake SMALLINT(4);
SELECT VNo FROM `details` WHERE RefNo = inRefNo; # ResultSet 1
SELECT * FROM deprates; # ResultSet 2
SELECT `Type`, Make INTO pType, pMake FROM `details` WHERE RefNo = inRefNo; # ResultSet 3
SELECT PartID, PartName, PartNo, PartTypeID FROM parts WHERE TypeID = pType AND MakeID = pMake; # ResultSet 4
END
;;
delimiter ;
Calling the proc from a PHP script takes minutes and returns error like "Fatal Error. Allowed memory size of xxxxxxxx bytes exhausted (tried to allocate xxxxxx bytes)".
I called the proc directly in phpMyAdmin with CALL GetAllParts('2021/0000012');. An error instantly thrown is "2014 - Commands out of sync; you can't run this command now"
If I run the queries inside the proc in one go like below:
SELECT VNo FROM `details` WHERE RefNo = '2021/0000012';
SELECT * FROM deprates;
SELECT `Type`, Make FROM `details` WHERE RefNo = '2021/0000012';
SELECT PartID, PartName, PartNo, PartTypeID FROM parts WHERE TypeID = 3 AND MakeID = 3;
All the 4 ResultSets are retrieved successfully. I have several similar procs but they are not throwing any error. As per description about the problem here in dev.mysql.com, how do I use mysql_use_result(), mysql_free_result() and mysql_store_result() inside a procedure?
Any help please?
