Bug #50031 OUT parameters still not working in Stored Procedures
Submitted: 2 Jan 2010 20:58 Modified: 15 Jan 2010 17:20
Reporter: Lothar Bongartz Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S1 (Critical)
Version:mysqld-5.5.0-m2-community OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: OUT Parameters, stored procedures

[2 Jan 2010 20:58] Lothar Bongartz
Description:
Even in version 5.5 of MySQL the output parameters of stored procedures do not work.

You get the runtime error "OUT or INOUT argument <n> for routine <name> is not a variable or NEW pseudo-variable in BEFORE trigger".

I have seen reports of this all over the Internet - unfortunately after investing weeks of work to migrate a huge project.

Since years there are promises to fix this.

As long as this basic functionality does not work, MySQL is useless for any serious project and there is no real excuse for this.
Every documentation of stored procedures in MySQL should start with the warning, that OUT parameters do not work in embedded environments.

This way everybody would be warned, not to invest time for MySQL, when anything ambitious is planned.

Is there any hope? How long will it take, until a working version of MySQL is available?

Is there any way to work around until then, which does not destroy the whole architecture of my project?

How to repeat:
I am using this functionlity in MS SQL since 10 years. For MS SQL it works perfect with C++.

In MySQL every OUT or INOUT parameter results in the mentioned runtime error.
[3 Jan 2010 11:39] Valeriy Kravchuk
This is an old, known and documented limitation, see http://dev.mysql.com/doc/refman/5.5/en/call.html. Workaround (with user variables instead of placeholders) is also described since 5.0 days. 

So, however sad is it, this is just a feature request, not a bug, formally. Do you agree?
[4 Jan 2010 20:21] Peter Gulutzan
For anyone who is listening in, and has the wrong impression:

MySQL does support OUT and INOUT parameters in stored procedures.
There is a limitation for prepared statements,
but see the comments attached to Bug#11638.
[4 Jan 2010 21:30] Lothar Bongartz
Actually OUT parameters are not working in prepared statements. Most annoying: You are not informed about this in the documentation of stored procedures and OUT parameters.

Many serious programmers are waiting for a fix, which is announced since many years. Now I even can’t see it on the roadmap.

While migrating from MS SQL to MySQL this is the final show stopper for me.
The combination of server side programming called from a programmed environment is the way to go for professional database usage and this way is not possible with MySQL. It is just unbelievable, that it isn't possible with MySQL, what is standard since more than a decade.
BTW: The other major problems with MySQL I have detected:
1) Collation is not only affecting sorting but unique inserting, too.
That means for example: By default you cannot have Nicole and Nicolé both in a first name table, if you like to have them sorted one after each other.
2) You are forced to use BACKSLASH_ESCAPES for user input (it is non standard and it does not make any sense there), since NO_BACKSLASH_ESCAPES is buggy since many years (see my other bug report). May be this is a reason for the SQL injection weakness of so many MySQL applications.

It looks like I am forced to stop my migration to MySQL and go for PostgresSQL.
[5 Jan 2010 9:57] Valeriy Kravchuk
This feature is already mostly implemented and should appear in the next GA relese, as far as I remember.
[15 Jan 2010 17:20] Alexander Nozdrin
It's duplicate of Bug#11638, which is fixed in Celosia (M3).