5

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); SELECT @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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
TMG
  • 500
  • 2
  • 6
  • 13

4 Answers4

9

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.

TMG
  • 500
  • 2
  • 6
  • 13
  • 1
    "Solved" the problem for me too - that is, I can reproduce the bug using phpmyadmin, and it doesn't appear in the mysql CLI client, nor the PHP application itself. – tdammers Nov 02 '10 at 08:39
  • @tdammers - hey thanks for the validation. Sometimes when all the smart programmers on this board tell me I'm crazy, I start to think that I am. – TMG Nov 03 '10 at 13:21
1

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;
Community
  • 1
  • 1
Jomarip
  • 11
  • 2
1

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

Community
  • 1
  • 1
dugas
  • 12,025
  • 3
  • 45
  • 51
  • 1
    I did see that post. My PHP version is PHP Version 5.2.12, which is allegedly before that bug appears. – TMG Mar 02 '10 at 02:23
0

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

Atli
  • 7,855
  • 2
  • 30
  • 43
  • I checked the names. I tested it on another local server. This one is running PHP Version 5.2.9, MySQL 5.1.33-community, and phpmyadmin 3.1.3.1. According to the bug reports, that config should not have a problem. However, still the same error: #1312 - PROCEDURE test_db.test_proc can't return a result set in the given context – TMG Mar 02 '10 at 04:03