MySQL Bugs: #11171: Calling stored procedure failure

Bug #11171	Calling stored procedure failure
Submitted:	8 Jun 2005 13:37	Modified:	8 Jun 2005 18:21
Reporter:	jerry kendall	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.6 beta	OS:	Linux (Linux/Windows)
Assigned to:		CPU Architecture:	Any

Description:
Hi all;

I am experiencing a strange response to a stored procedure.

I have created a stored procedure in MySQL 5.0.6 Beta.

The procedure is as follows:
===========================================
DELIMITER $$

DROP PROCEDURE IF EXISTS `ViewIntus`.`TableNameListCnt`$$

CREATE PROCEDURE `ViewIntus`.`TableNameListCnt` ()
SQL SECURITY INVOKER
COMMENT 'Count the number of TableNames'
BEGIN
SELECT COUNT(*)
FROM TableName;
END$$
===========================================

Yes, I know this does not require a stored procedure but, this is the simplest code to demonstrate the issue.

Most of my other stored procedures work as expected.

OK.... Simple enough.
Now,

with the following query tools:
- MySQL Control Center
- MySQL Query Browser
- MySQL UNIX command Line tool
I can execute:

USE database_name;
CALL TableNameListCnt();

and everything works as expected.

But, I have a 'C' program that calls this same procedure and it fails.

When I call execute 'mysql_query(SqlConnection, "SELECT COUNT(*) FROM TableName");' it works fine.

But, when I execute 'mysql_query(SqlConnection, "CALL TableNameListCnt()");',
I get the following error:

"SQL error (PROCEDURE database_name.TableNameListCntcan't return a result set in the given context) while executing (CALL TableNameListCnt())".

I have looked at many BUG reports, howtos, forums, etc but cant seem to get any insight into this.

can anyone help me?

Please.

-- 
Jerry Kendall, CISSP
jerry@pentego.com
Pentego technologies Ltd.
www.pentego.com

How to repeat:
See the desciption above

I will mark this down as "Not a bug".  If Quey Browser, MySQLCC and the command line client can successfully execute the stored procedures, this means the problem is in your code rather than our client library.

If you need help getting this to work, I suggest you purchase MySQL Network and one of our engineers will be glad to help you.

Talk about arrogance.

I run the same query INSIDE the procedure that I ran directly in
mysql_real_query()

The direct method works fine.  The procedure errors...

WHY??? simple, the sql_real_connect call needs CLIENT_MULTI_RESULTS.

Thanks for your kind comment that I have an error in my code.

Bye.

We're sorry, but the bug system is not the appropriate forum for 
asking help on using MySQL products. Your problem is not the result 
of a bug.

Support on using our products is available both free in our forums
at http://forums.mysql.com and for a reasonable fee direct from our
skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.