Running Stored Procedures

[GoogleCodeExporter]

What steps will reproduce the problem?
1. create a store procedure (see 
http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html)
2. try to execute this procedure using "call my_procedure(10);"

What is the expected output? What do you see instead?

We expect to get the result set of my_procedure(10) but we get: can't return a 
result set in the given 
context.

Original issue reported on code.google.com by ursache....@gmail.com on 10 Apr 2009 at 11:23

[GoogleCodeExporter]

Maybe this can be easily fixed by enabling a flag when we connect to the mysql 
server. Also we have to check if 
the MySQL library supports this.

Original comment by ursache....@gmail.com on 10 Apr 2009 at 11:24

[GoogleCodeExporter]

Sample procedure created from CLI:

mysql> delimiter //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    -> SELECT COUNT(*) INTO param1 FROM blog_post;
    -> END
    -> ;
    -> //

Original comment by ursache....@gmail.com on 10 Apr 2009 at 11:31

[GoogleCodeExporter]

Original comment by ursache....@gmail.com on 10 Apr 2009 at 12:09

• Changed title: Running Stored Procedures

[GoogleCodeExporter]

i believe the call should go something like:

mysql> set @foo = 0;
mysql> call simpleproc(@foo);
mysql> select @foo;

The above works for me from the query editor.

Original comment by emptyvee on 13 May 2009 at 12:53

[GoogleCodeExporter]

For CALL support, we also have to add support for
CLIENT_MULTI_STATEMENTS/CLIENT_MULTI_RESULTS.

http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html

Original comment by ursache....@gmail.com on 14 May 2009 at 6:55

[GoogleCodeExporter]

I've experienced the same result as that described in the initial bug report 
and found the same workaround. 
Additionally, however, I have noticed that after calling the SP (which drops, 
creates, and populates a table) 
that the contents are not visible from inside Sequel Pro (but are visible form 
the command line client). I've 
tried flushing privileges with no luck. I've also noticed that the contents of 
other tables in the same db are 
also not showing (just appears to be an empty table).

And finally, switching databases via the pop-up menu fails to refresh the 
tables list (and clicking the Refresh 
button doesn't help).

I'd be happy to provide sample SPs for debugging.

MySQL 5.1.30 and Sequel Pro 0.9.6.

Original comment by tedmaste...@gmail.com on 21 Sep 2009 at 10:58

[GoogleCodeExporter]

Forgot to mention this is happening on Mac OS X (10.5.7).

Original comment by tedmaste...@gmail.com on 21 Sep 2009 at 11:00

[GoogleCodeExporter]

Regarding the issue of the table data not being updated, I think the problem 
existed between the chair and the 
screen ;-). It turns out my stored procedure was creating a table whose ENGINE 
was defined as MEMORY (instead 
of InnoDB or something more reasonable).

Original comment by tedmaste...@gmail.com on 22 Sep 2009 at 1:24

[GoogleCodeExporter]

Original comment by stuart02 on 31 Mar 2010 at 12:22

• Added labels: Usability

[GoogleCodeExporter]

I am experiencing the same issue.. can this be fixed? I so do not want to use 
MySql Workbench to edit Sps... yuck

Original comment by mjaff...@gmail.com on 10 Jun 2010 at 10:05

[GoogleCodeExporter]

I am having the same issue.  I am on a mac, running 0.9.7, build 1693, but 
continue to have the error.  I don't really understand what the 'workaround' 
here is that is mentioned, outside of using a different tool -- others that use 
sqlyog or query browser don't have an issue. So, it seems something specific to 
SequelPro -- not MySQL. 

Has anyone found a solution or is it on the roadmap for SequelPro to resolve? 

Original comment by orangele...@gmail.com on 25 Jun 2010 at 8:58

[GoogleCodeExporter]

Hi,

could you please tell us what do you mean by "having the same issue"? To create 
stored procedures?

First, please update to version 0.9.8.

Then you can create/run stored procedures like that in the Query Editor:

delimiter //
CREATE PROCEDURE simpleproc0815 (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM `mysql`.`user`;
END
;
//
delimiter ;
CALL simpleproc0815(@a);
SELECT @a;

Or did I misunderstood you? 

Kind regards,
--Hans

Original comment by bibiko@gmail.com on 25 Jun 2010 at 9:36

[GoogleCodeExporter]

Hans:  I think the problem is not creating/calling stored procedures per se, 
but that Sequel Pro can't handle multiple resultsets.

The "workaround" is to use stored procedures that don't return query results. 
Which naturally doesn't work if you have to use existing stored procedures.

Original comment by jabakobob@gmail.com on 28 Jun 2010 at 10:04

[GoogleCodeExporter]

Thanks for the clarification.

Ok, I ran Sequel Pro with the connection option CLIENT_MULTI_RESULTS and then 
such calling of these procedures are working. This option was added with MySQL 
version 4.1. I'll think how to add that option.

--Hans

Original comment by bibiko@gmail.com on 29 Jun 2010 at 8:12

[GoogleCodeExporter]

Oh, but after enabling this Sequel Pro will lost its connection to the server.

--Hans

Original comment by bibiko@gmail.com on 29 Jun 2010 at 8:16

[GoogleCodeExporter]

I think the problem is that when we enable that connection option, each result 
set must be checked for additional results, or the out-of-order command errors 
occur…

Original comment by rowanb@gmail.com on 29 Jun 2010 at 9:18

[GoogleCodeExporter]

To sum up: If we want to enable CLIENT_MULTI_RESULTS, we must do several things:
1) Change the Resultset class in some way so we can retrieve multiple result 
sets.
2) Add support for multiple result sets in the custom query view (or discard 
all result sets except the last)
3) ensure that the connection stays locked until all resultsets have been 
retrieved

I believe that (2) requires the largest effort. Since multiple results mostly 
occur when calling stored procedures, and one result is often uninteresting 
(the 'OK' status message), we can for the time being just discard all results 
and display only the one with the interesting results. (i'm not sure if that's 
the first or the last result)

Original comment by jabakobob@gmail.com on 30 Jun 2010 at 11:00

[GoogleCodeExporter]

I have enabled CLIENT_MULTI_RESULTS in r2371. When calling a stored procedure, 
the first result will be shown and remaining ones are silently discarded. While 
this doesn't solve the underlying problem (Sequel Pro can only display one 
result), it at least allows calling stored procedures that return a single 
result.

Original comment by jabakobob@gmail.com on 30 Jun 2010 at 1:46

• Added labels: Priority-Low
• Removed labels: Priority-Medium

[GoogleCodeExporter]

I have the same problem which is pretty frustrating and this is a BIG limitation

Original comment by rvelo...@gmail.com on 18 Sep 2010 at 6:27

[GoogleCodeExporter]

Issue 826 has been merged into this issue.

Original comment by schlabbe...@gmail.com on 18 Sep 2010 at 9:27

[GoogleCodeExporter]

hi -- not sure if you have any way for us to vote for which bugs get fixed 
first/features added first, but this one (inability to simply CALL myStoredProc 
and see the resultSet) would be currently top of my list! otherwise, the 
product is great!

Original comment by darrell....@gmail.com on 18 Jan 2011 at 2:49

[GoogleCodeExporter]

Hiya Darrell,

Starring (at the top left) or commenting on these issues is the right way to do 
it :)  Development builds (see http://nightly.sequelpro.com/ ) allow displaying 
the first result set; the next tweak will be to support multiple result sets 
using tabs, but that'll probably wait till after the next release.

Original comment by rowanb@gmail.com on 18 Jan 2011 at 2:59

[GoogleCodeExporter]

Is this still on the road map?  SequelPro is a great client, but this is one of 
the bigger issues i've come across with it.  I have to go back to the command 
line to use these.

Original comment by jeff.d...@gmail.com on 11 Mar 2011 at 10:44

[GoogleCodeExporter]

Original comment by mattlangtree on 14 Jan 2012 at 8:38

• Added labels: Milestone-Release1.1
• Removed labels: Milestone-Release0.9

[GoogleCodeExporter]

Issue 1854 has been merged into this issue.

Original comment by schlabbe...@gmail.com on 20 Nov 2013 at 3:19