Attempting to migrate to MySQL Connector/J 8.1.0 and my CallableStatement code which works fine under version 5.1.49 fails with:
SQLException: Parameter number 2 is not an OUT parameter
Procedure:
CREATE PROCEDURE get_system_setting(IN p_setting VARCHAR(200), OUT message_out VARCHAR(200))
BEGIN
SELECT setting, value, message_out FROM SYSTEM_SETTING WHERE setting = p_setting;
END;
Sample Java Code:
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
public class CallableTest {
/* MySQL Connector/J 8.1.0 (fails on all 8.*.* versions) */
private static final String DB_DRIVER_CLASS = "com.mysql.cj.jdbc.Driver";
/* MySQL Connector/J 5.1.49 - comment line above and uncomment next line to use v5 */
//private static final String DB_DRIVER_CLASS = "com.mysql.jdbc.Driver";
private static final String DB_URL = "jdbc:mysql://localhost:3306/DB_NAME?useSSL=false";
private static final String DB_USERNAME = "user";
private static final String DB_PASSWORD = "password";
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName(DB_DRIVER_CLASS);
conn = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void main(String[] args) {
Connection conn = null;
CallableStatement cs = null;
try {
conn = CallableTest.getConnection();
cs = conn.prepareCall("{call DB_NAME.get_system_setting(?, ?)}");
cs.setString(1, "CONNECT_TIMEOUT");
cs.registerOutParameter(2, Types.VARCHAR); // <-- Fails here
cs.execute();
String message = cs.getString(2);
if(message != null) {
System.out.printf("out parameter: %s\n", message);
} else {
ResultSet rs = cs.getResultSet();
boolean rowsReturned = ((rs != null) && rs.next());
if (rowsReturned) {
String setting = rs.getString("setting");
String value = rs.getString("value");
System.out.printf("setting: %s, value: %s\n", setting, value);
}
}
} catch(Exception e) {
e.printStackTrace();
} finally {
try {
cs.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
The above fails with using 8.1.0 and all 8.0.* versions of connector/j
java -cp .:mysql-connector-j-8.1.0.jar CallableTest
java.sql.SQLException: Parameter number 2 is not an OUT parameter
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:130)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:98)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:90)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:64)
at com.mysql.cj.jdbc.CallableStatement.checkIsOutputParam(CallableStatement.java:645)
at com.mysql.cj.jdbc.CallableStatement.registerOutParameter(CallableStatement.java:1717)
at com.mysql.cj.jdbc.CallableStatement.registerOutParameter(CallableStatement.java:1725)
at CallableTest.main(CallableTest.java:49)
But works with 5.1.49
java -cp .:mysql-connector-java-5.1.49.jar CallableTest
setting: CONNECT_TIMEOUT, value: 10000
Tried both named and index parameters. Looked at the Connector/J docs and can't find any references to specific changes with CallableStatement between the versions.
Environment
It turns out there is a function in this DB schema with the same name as the procedure... MySQL allows the same object name to be used to declare a function as well as a procedure. Seems like this should not be allowed as it is the case with other DBs. Connector/J v5 handles this scenario, however v8 fails with an error message that is somewhat misleading. Opened a bug report with Oracle.