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:	18 Jun 13:09
Reporter:	Mark Matthews
Status:	In progress
Category:	C API	Severity:	S2 (Serious)
Version:	5.0	OS:	Any (Any)
Assigned to:	Alexander Nozdrin	Target Version:	6.0
Tags:	C_API, stored procedure, prepared statement
Triage:	D2 (Serious)

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