Bug #17898 No straightforward way to deal with output parameters
Submitted: 3 Mar 2006 14:34 Modified: 14 Jul 2009 14:38
Reporter: Mark Matthews Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S2 (Serious)
Version:5.0 OS:Any (Any)
Assigned to: Alexander Nozdrin CPU Architecture:Any
Tags: C_API, prepared statement, stored procedure

[3 Mar 2006 14:34] Mark Matthews
Description:
Since there's no support in the protocol for an RPC call or binding directionality of parameters using server-side prepared statements for stored procedures, clients run into the issue that there is no standard or straightforward way to deal with OUTPUT or INOUT parameters, especially when a user wants to use a literal as the IN value of an INOUT parameter.

We can work around it in the clients, but it means yet more parsing code (different in each connector), and the parsing code will begin to get complex as we'll have to support a subset of SQL.

How to repeat:
From the JDBC forums at http://forums.mysql.com/read.php?39,66187,66187#msg-66187

callStmt.registerOutParameter(1, java.sql.Types.INTEGER);

falls over with "Parameter number 1 is not an OUT parameter"

The procedure definition is:
PROCEDURE `employee_surname_count`(surname VARCHAR(50), OUT surname_count INT)
BEGIN
DECLARE rtn INT;
SELECT COUNT(*) INTO rtn FROM employee e WHERE e.surname=surname;
SET surname_count = rtn;
END

String lclRunSQL= "{CALL employee_surname_count('Jones', ?)}"
callStmt = conn.prepareCall(lclRunSQL);

callStmt .getParameterMetaData().getParameterCount(); --- reports 1 (one).

I appreciate that I'm not supplying all parameters as question marks "?" to the prepared statement, but the jdbc driver is reporting the parameterCount as 1 - which is fine by me.

The works OK in MS SQL Server and Oracle.
[3 Mar 2006 15:05] Valeriy Kravchuk
Thank you for a problem report. Although, I think it is a feature request, really.
[9 Mar 2006 21:00] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/3667
[9 Mar 2006 21:31] Mark Matthews
Notice, this patch is just a workaround in the JDBC driver. Still a server-level issue.
[18 Jan 2007 22:55] Konstantin Osipov
Bug#25363 DataTruncation warning when calling stored proc was marked as a duplicate of this bug.
[1 Feb 2007 3:31] Konstantin Osipov
Bug#24724 Parameters failure ADO objects in VB was marked as duplicate of this bug. See also the limitation to the use of OUT variables with SQL syntax for prepared statements.
[4 Apr 2007 16:46] Tonci Grgin
Bug#27632 has been marked as duplicate of this one.
[11 Apr 2007 14:49] Tonci Grgin
Bug#26382 has been marked as duplicate of this one.
[23 Apr 2007 22:08] Marc ALFF
Setting to in progress,
evaluating possible solution
[14 Jun 2007 1:05] David Boccabella
Its start of June.
Has there been any reolution to this problem.

I am about to rediesign a very large system and it would be nice to use MySQL for the database intelligence.

Dave
[19 Jun 2007 23:45] David Boccabella
Now 20th June..
No Results, no resolve.

I would consider that OUT parameters from a Stored Proc would be one of the more fundimental behaviors.

Sort of like not being able to have joins in views!!!!

PLEASE can we get a resolution on this soon. 

Dave
[6 Jul 2007 4:15] David Boccabella
Its now into July.

Just wondering when this will be solved....

Dave
[30 Oct 2007 0:51] David Boccabella
Does this update mean that we will be waiting until version 6 of MySQL to use  Output Parameters???
[2 Nov 2007 16:30] Don Cohen
It's not clear how Bug #27632 maps to this.  I'm willing to believe that the patch for connector J fixed 27632 there because the same code that gives me this using ODBC
===
prepare {call getauthority(?)}java.sql.SQLException: [HY000][MySQL][ODBC 3.51 Driver][mysqld-5.0.37-log]OUT or INOUT argument 1 for routine devel.getauthority is not a variable or NEW pseudo-variable in BEFORE trigger
===
works in connector J
Is there some work around at the source level?
Or you just can't use out params with odbc?
(In my case this means moving from connector J to odbc requires rewriting all my stored procedures!)
[23 Nov 2007 7:44] Roderick Riolo
any update on this?
[4 Mar 2008 6:31] David Boccabella
Its nearly a year now.

Can we access the output parameters from a stored procedure yet??..

Still desperaterly waiting

Dave
[4 Mar 2008 6:31] David Boccabella
Its nearly a year now.

Can we access the output parameters from a stored procedure yet??..

Still desperaterly waiting

Dave
[4 Mar 2008 22:49] Marc ALFF
See related bug#11638
[6 Aug 2008 16:35] Konstantin Osipov
http://lists.mysql.com/commits/50950
 2675 Alexander Nozdrin	2008-08-05
      Patch for WL#4435: Support OUT-parameters in prepared statements.
      
      After execution of a prepared statement, send OUT parameters of the invoked
      stored procedure, if any, to the client.
      
      When using the binary protocol, send the parameters in an additional result set
      over the wire.  When using the text protocol, assign out parameters to the user
      variables from the CALL(@var1, @var2, ...) specification.
[7 Aug 2008 20:07] Alexander Nozdrin
Pushed into 6.0-runtime.
[3 Feb 2009 19:40] Paul DuBois
Noted in 6.0.8 changelog.

Previously, prepared CALL statements could be used via the C API only
for stored procedures that produce at most one result set, and
applications could not use placeholders for OUT or INOUT parameters.
For prepared CALL statements used via PREPARE and EXECUTE,
placeholders could not be used for OUT or INOUT parameters.

For the C API, prepared CALL support now is expanded in the following
ways: 

* A stored procedure can produce any number of result sets. The number
  of columns and the data types of the columns need not be the same for
  all result sets.

* The final values of OUT and INOUT parameters are available to the
  calling application after the procedure returns. These parameters are
  returned as an extra single-row result set following any result sets
  produced by the procedure itself. The row contains the values of the
  OUT and INOUT parameters in the order in which they are declared in
  the procedure parameter list.

* A new C API function, mysql_stmt_next_result(), is available for
  processing stored procedure results. See 
  http://dev.mysql.com/doc/refman/6.0/en/c-api-prepared-call-statements.html

* The CLIENT_MULTI_RESULTS flag now is enabled by default. It no longer
  needs to be enabled when you call mysql_real_connect(). (This flag is
  necessary for executing stored procedures because they can produce
  multiple result sets.)

For PREPARE and EXECUTE, placeholder support for OUT and INOUT
parameters is now available. See 
http://dev.mysql.com/doc/refman/6.0/en/call.html
[3 Feb 2009 22:33] David Boccabella
HI
Sadly - the solution seems to be a bit of a copout
This means that MySQl handles OUT parameters differently from any other DB like MSSQL and ORACLE

It also means that any program written for a standard RDMS that uses OUT parameters will have to modified to handle the quirks of MySQL

I have already been using a "Additional Resultset" solution myself in my code, but that only works in environments where I have full control over the code ceneration. Many 'Wizards' like CodeChargeStudio etc that generate a lot of the code for you will not beable to handle this.
[8 Feb 2009 12:38] Konstantin Osipov
Hello David,

We do not plan to force users to retrieve the additional result set manually.
This can be done automatically in the connector.
[28 Apr 2009 18:52] Matt Kopcienski
[8 Feb 13:38] Konstantin Osipov 
Hello David,

We do not plan to force users to retrieve the additional result set manually.
This can be done automatically in the connector.

How can this be done in the connector?  Which connector?
[30 Apr 2009 3:03] Konstantin Osipov
Connector = client library. Connector/C, JDBC or ODBC driver.
[30 Apr 2009 13:52] Matt Kopcienski
I understand what the connector is.  https://oem.mysql.com/connectors.php  However, we are using the Enterprise version of the connecters so having us modify the source code is not an option.

This is a major hole vis a vis Oracle and SQL Server.
[4 May 2009 7:55] Konstantin Osipov
Matt,
please do not use a closed bug report to communicate with the develpment/support team.
If you still experience a problem accessing out parameters, please open a separate bug report, or even better, a customer support case (since you seem to be having an enterprise subscription anyway).
Thank you,
Kostja
[10 Jul 2009 6:11] Tonci Grgin
Based on what Chris said on [28 Jun 22:05] and my discussion with Jeffrey today, I'm reopening the report until it's fixed in, at least, 5.4.

I realize this is a too much of behavior change for 5.0 and 5.1 but the final decision is for Tomas and Kostja to make.
[10 Jul 2009 6:31] David Boccabella
Fixing this in 5.4 would be very good as  5.4 also has support for SIGNAL raising within SP's as well.

This was another major difference between SP's in MySQl and MsSQL/Oracle.

Dave
[10 Jul 2009 9:00] Alexander Nozdrin
It is in 5.4 (Azalea).
[10 Jul 2009 9:14] Tonci Grgin
Thanks, this should be enough for now.
[11 Jul 2009 7:23] David Boccabella
Hi.
I originally resented Bug 27632 with the OUT isue when using SP and Visual Basic. The bug report has a full test including example of the failure

I tried the same tests with MySQL 5.4.1 and it failed.  
Can you do the tests I set out in Bug 27632 to make sure that this does work...

Many Thanks
Dave
[11 Jul 2009 16:55] Alexander Nozdrin
Hello,

it will work starting from 5.4.4.
[14 Jul 2009 14:38] Paul DuBois
Noted in 5.4.4 changelog, and documented in 5.4 manual. For example:

http://dev.mysql.com/doc/refman/5.4/en/c-api-prepared-call-statements.html
http://dev.mysql.com/doc/refman/5.4/en/call.html
[12 Aug 2009 21:43] Paul DuBois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[14 Aug 2009 22:41] Paul DuBois
Ignore previous comment about 5.4.2.
[12 Jul 2010 14:19] Paul DuBois
Noted in 5.5.3 changelog.