MySQL Forums :: Stored Procedures :: Call to a stored procedure via Perl DBD produces the error "DBD::mysql::st execute failed: PROCEDURE ARTH_DB.getObjectsBFS can't return a result set in the given context".

My impression from various web postings (http://rt.cpan.org/Public/Bug/Display.html?id=12322, http://forums.mysql.com/read.php?51,30177,30256#msg-30256,
http://forums.mysql.com/read.php?98,182823,182945#msg-182945) is that this MySQL bug has been fixed, or can be avoided by the *multi* attributes below. However, I still see it. I've also tried the connect without the *multi* attributes.


I'm using DBD::mysql 4.013, and MySQL 5.0.45-log.
I connect with "DBI->connect( " dbi:mysql:ARTH_DB:vpdev.bio.nyu.edu:3306;mysql_multi_statements=1;mysql_multi_results=1 ", "user", etc. )
SELECT statements work over the connection.

The stored procedure is below. It is called
CALL ARTH_DB.getObjectsBFS( '3606658', 'Group2Group' )

This call works correctly using the same DBMS from a client interface (SQuirrel SQL Client).


CREATE PROCEDURE `getObjectsBFS`(IN inoid int(10), IN relationship_like varchar(64) )
BEGIN

DROP TEMPORARY TABLE IF EXISTS explored, frontier, newfrontier;
CREATE TEMPORARY TABLE explored(OID int(10), INDEX USING HASH (OID)) ENGINE = MEMORY;
CREATE TEMPORARY TABLE frontier(OID int(10), INDEX USING HASH (OID)) ENGINE = MEMORY;
CREATE TEMPORARY TABLE newfrontier(OID int(10), INDEX USING HASH (OID)) ENGINE = MEMORY;
INSERT INTO explored VALUES(inoid);
INSERT INTO frontier VALUES(inoid);
breadthfirstsearchloop: LOOP
IF ( NOT EXISTS (SELECT * FROM frontier ) ) then LEAVE breadthfirstsearchloop; end IF;
DELETE FROM newfrontier;
INSERT INTO newfrontier
SELECT OC.OID2
FROM OBJECT_CONNECTION OC, frontier f
WHERE OC.OID1 = f.OID
AND OC.RELATIONSHIP LIKE relationship_like
AND OID2 IS NOT NULL;

DELETE newfrontier FROM newfrontier, explored
WHERE newfrontier.OID = explored.OID;

DELETE FROM frontier;

INSERT INTO frontier SELECT DISTINCT( OID ) FROM newfrontier;
INSERT INTO explored SELECT DISTINCT( OID ) FROM newfrontier;

END LOOP breadthfirstsearchloop;
SELECT DISTINCT( OID ) FROM explored;
DROP TEMPORARY TABLE IF EXISTS explored, frontier, newfrontier;
END;