MySQL Stored Procedures not working with SELECT (basic question)

I am using a platform (perfectforms) that requires me to use stored procedures for most of my queries, and having never used stored procedures, I can't figure out what I'm doing wrong. The following statement executes without error:

DELIMITER //
DROP PROCEDURE IF EXISTS test_db.test_proc//
CREATE PROCEDURE test_db.test_proc() SELECT 'foo'; //
DELIMITER ;

But when I try to call it using:

CALL test_proc();

I get the following error:

#1312 - PROCEDURE test_db.test_proc can't return a result set in the given context

I am executing these statements from within phpmyadmin 3.2.4, PHP Version 5.2.12 and the mysql server version is 5.0.89-community.

When I write a stored procedure that returns a parameter, and then select it, things work fine (e.g.):

DELIMITER //
DROP PROCEDURE IF EXISTS test_db.get_sum//
CREATE PROCEDURE test_db.get_sum(out total int)
BEGIN
SELECT SUM(field1) INTO total FROM test_db.test_table;
END //
DELIMITER ;

works fine, and when I call it:

CALL get_sum(@t); SE开发者_JAVA技巧LECT @t;

I get the sum no problem.

Ultimately, what I need to do is have a fancy SELECT statement wrapped up in a stored procedure, so I can call it, and return multiple rows of multiple fields. For now I'm just trying to get any select working.

Any help is greatly appreciated.

Figured it out. This is not a bug with PHP (though it used to be) - it's a bug in some versions of phpmyadmin. The same bug intermittently reappears and is then fixed in various subversions (see above):

#1312 - PROCEDURE [name] can't return a result set in the given context

This behavior appears limited to SELECT statements within stored procedures inside phpmyadmin.

Using a client like MySQL Workbench works around the problem (or you could upgrade phpmyadmin, but that's a pain if you're on a shared server like I am).

Anyway, thanks to everyone for your help.

Check your php version to see if this is a reported bug (see here).

See this post: Can't return a result set in the given context

This probably is centered around a bug dealing with PHP and SELECT within a stored procedure/routine. One possible work around is to use the SELECT... INTO inside of the stored procedure to bring the result down to one variable. This worked in some other cases that I read.

   SELECT * FROM category INTO c;

When I execute the following:

DELIMITER //
DROP PROCEDURE IF EXISTS test_db.test_proc//
CREATE PROCEDURE test_db.test_proc() SELECT 'foo'; //
DELIMITER ;

Followed by

CALL test_db.test_proc();

I shows me a result set like:

+-----+
| foo |
+-----+
| foo |
+-----+

I'm using PHP 5.3.1, MySQL 5.1.41 with phpMyAdmin 3.2.4.

Perhaps you just misspelled your procedure name?
Or could there be a problem with your PHP or MySQL installations? (Perhaps a bug. Have you tried updating to the latest versions?)