MySQL Bugs: #15464: INOUT parameter does not store IN value.

Bug #15464	INOUT parameter does not store IN value.
Submitted:	3 Dec 2005 21:07	Modified:	7 Dec 2005 22:50
Reporter:	Ranv Z	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	MySQL 5.0.15, mysql-connector-java-3.1.1	OS:	Windows (Microsoft Windows XP)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
The INOUT paramater in a stored procedure does not store an IN value when called from Java using cStmt.setInt("inOutParam", 4) or  cStmt.setInt(2, 4), where 4 is the IN value and 2 is the parameter index in the stored procedure.  Please see the "How to repeat" section on a more complete description as this situation is best described with an example.

How to repeat:
This example will repeat the problem.  

Part of my Java code (which is taken from the MySQL Connector/J documentation):

    CallableStatement cStmt = conn.prepareCall("{call demoSp(?, ?)}");                                     
    cStmt.registerOutParameter("inOutParam", Types.INTEGER);
    cStmt.setString(1, "hello");
    cStmt.setInt("inOutParam", 4);
    cStmt.execute();
    System.out.println(cStmt.getInt("inOutParam"));

The stored procedure (which has been taken directly from the MySQL Connector/J documentation, PDF format, Page 4)

CREATE PROCEDURE demoSp(IN inputParam VARCHAR(255), INOUT inOutParam INT)
BEGIN
    DECLARE z INT;
    SET z = inOutParam + 1;
    SET inOutParam = z;
    SELECT inputParam;
    SELECT CONCAT('zyxw', inputParam);
END

I have tested the query in MySQL and the stored procedure does work correctly.  The Java code successfuly runs the stored procedure, but the INOUT parameter always returns as 1.  This behaviour indicates that when the stored procedure runs, inOutParam is set to 0, regardless of what value it is given in the Java code.  Another user on the forum indicated that changing the stored procedure so that all the parameters are INOUT causes the correct results to be returned.  While this is a work around, it is not an ideal solution.

The expected result from the Java code would be an output of 5.  Thank you for your time!

Thanks for the bug report. I was able to reproduce the bug on my test case. My test case is attached.

Test case

Attachment: Bug15464.java (text/java), 907 bytes.

I dont know if it helps, but when you reverse the parameters you can get it to work. see: http://forums.mysql.com/read.php?98,57749,57813#msg-57813

Fixed for 3.1.13 and 5.0.0. Current in subversion at http://svn.mysql.com/svnpublic/connectors-svnroot/connector-j or will be available in tonight's nightly build after 00:00 GMT at http://downloads.mysql.com/snapshots.php#connector-j