Bug #40562 INOUT parameter is not initialized
Submitted: 6 Nov 2008 20:13 Modified: 7 Nov 2008 22:40
Reporter: Hans Bergsten Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / J Severity:S1 (Critical)
Version:5.1.7 OS:Any
Assigned to: CPU Architecture:Any

[6 Nov 2008 20:13] Hans Bergsten
Description:
An INOUT parameter for a stored procedure never gets initialized when called through Connector/J.

How to repeat:
Create this procedure:

CREATE PROCEDURE test.inout_test(IN p1 INT, INOUT p2 INT, OUT p3 INT)
BEGIN
  set p3 = p1;
  set p2 = p2 + p2;
END

Run this test code:

      try {
         Class.forName("com.mysql.jdbc.Driver");
         Connection conn = DriverManager.getConnection(URL);
         CallableStatement cStmt = conn.prepareCall("{call test.inout_test(1, ?, ?)}");
         cStmt.registerOutParameter(1, Types.INTEGER);
         cStmt.registerOutParameter(2, Types.INTEGER);
         cStmt.setInt(1, 2);
         
         cStmt.execute();
         
         System.out.println("p2: " + cStmt.getObject(1));
         System.out.println("p3: " + cStmt.getObject(2));
      }
      catch (Exception e) {
         System.out.println("Exception: " + e);
      }

Result:
  p2: null
  p3: 1

Expected resukt:
  p2: 4
  p3: 1

Changing getObject() to getInt() gives "p2: 0", i.e. still not the correct result.

Calling the procedure with MySQL commands gives the correct result, so the problem must be in Connector/J:

  set @p2 = 2;
  call test.inout_test(1, @p2, @p3);
  select @p2, @p3;
[6 Nov 2008 20:16] Mark Matthews
Does it work for you if instead of a literal one, you parameterize the first argument to your stored procedure?
[6 Nov 2008 20:27] Hans Bergsten
Yes, with parameter markers for all arguments it works
[7 Nov 2008 8:29] Tonci Grgin
Hans, from what I see in regression test suite, it is supposed to work only with param markers thus !Bg:

	/**
	 * Tests fix for BUG#15464 - INOUT parameter does not store IN value.
	 * 
	 * @throws Exception
	 *             if the test fails
	 */

	public void testBug15464() throws Exception {
		if (!serverSupportsStoredProcedures()) {
			return;
		}
		CallableStatement storedProc = null;

		try {
			this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testInOutParam");
			this.stmt
					.executeUpdate("create procedure testInOutParam(IN p1 VARCHAR(255), INOUT p2 INT)\n"
							+ "begin\n"
							+ " DECLARE z INT;\n"
							+ "SET z = p2 + 1;\n"
							+ "SET p2 = z;\n"
							+ "SELECT p1;\n"
							+ "SELECT CONCAT('zyxw', p1);\n"
							+ "end\n");

			storedProc = this.conn.prepareCall("{call testInOutParam(?, ?)}");

			storedProc.setString(1, "abcd");
			storedProc.setInt(2, 4);
			storedProc.registerOutParameter(2, Types.INTEGER);

			storedProc.execute();

			assertEquals(5, storedProc.getInt(2));
		} finally {
			this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testInOutParam");
		}
	}

Mark will overrule me if I'm wrong here.
[7 Nov 2008 22:40] Hans Bergsten
The spec seems to state clearly that a mix of literals and parameter markers is allowed:

JDBC3 spec, 13.3.2 Setting Parameters:

Parameter ordinals, which are integers passed to the approriate setter method, refer 
to the parameter markers ("?") in the statement, starting at one. Literal parameter 
values in the statement do not increment the ordinal value of the parameter markers. 
In CODE EXAMPLE 13-19, the two parameter markers have the ordinal values 1 and 2.
 
CallableStatement cstmt = con.prepareCall( 
  "{CALL PROC(?, "Literal_Value", ?)}"); 
cstmt.setString(1, "First"); 
cstmt.setString(2, "Third");