MySQL Bugs: #40562: INOUT parameter is not initialized

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

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;

Does it work for you if instead of a literal one, you parameterize the first argument to your stored procedure?

Yes, with parameter markers for all arguments it works

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.

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");