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:Microsoft Windows (Microsoft Windows XP)
Assigned to: Mark Matthews CPU Architecture:Any

[3 Dec 2005 21:07] Ranv Z
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!
[4 Dec 2005 11:10] Vasily Kishkin
Thanks for the bug report. I was able to reproduce the bug on my test case. My test case is attached.
[4 Dec 2005 11:10] Vasily Kishkin
Test case

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

[5 Dec 2005 2:37] Roland Bouman
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
[7 Dec 2005 22:50] Mark Matthews
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