MySQL Bugs: #17898: No straightforward way to deal with output parameters

Bug #17898	No straightforward way to deal with output parameters
Submitted:	3 Mar 2006 15:34	Modified:	14 Jul 16:38
Reporter:	Mark Matthews
Status:	Closed
Category:	Server: PS	Severity:	S2 (Serious)
Version:	5.0	OS:	Any (Any)
Assigned to:	Alexander Nozdrin	Target Version:	6.0-beta
Tags:	prepared statement, stored procedure, C_API
Triage:	Triaged: D2 (Serious) / R3 (Medium) / E3 (Medium)

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.

Thank you for a problem report. Although, I think it is a feature request, really.

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

Notice, this patch is just a workaround in the JDBC driver. Still a server-level issue.

Bug#25363 DataTruncation warning when calling stored proc was marked as a duplicate of
this bug.

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.

Bug#27632 has been marked as duplicate of this one.

Bug#26382 has been marked as duplicate of this one.

Setting to in progress,
evaluating possible solution

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

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

Its now into July.

Just wondering when this will be solved....

Dave

Does this update mean that we will be waiting until version 6 of MySQL to use  Output
Parameters???

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

any update on this?

Its nearly a year now.

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

Still desperaterly waiting

Dave

Its nearly a year now.

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

Still desperaterly waiting

Dave

See related bug#11638

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.

Pushed into 6.0-runtime.

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

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.

Hello David,

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

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

Connector = client library. Connector/C, JDBC or ODBC driver.

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.

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

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.

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

It is in 5.4 (Azalea).

Thanks, this should be enough for now.

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

Hello,

it will work starting from 5.4.4.

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

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.