I have been trying call procedure "proc" which were created in MySQL Workbanch:
create database test_database;
use test_database;
delimiter &&
create procedure proc(inout param INT UNSIGNED)
begin
set param = 2*param;
end&&
using this application :
package test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class Test {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1", "root", "root");
connection.createStatement().execute("USE test_database");
CallableStatement callableStatement = connection.prepareCall("{call proc(?)}");
callableStatement.setInt(1, 5);
callableStatement.registerOutParameter(1, java.sql.Types.INTEGER);
ResultSet result = callableStatement.executeQuery();
if (result.first()) {
System.out.println(result.getInt(1));
}
} catch (Exception ex) {
ex.printStackTrace();
}
}}
but i always get this error:
java.sql.SQLException: Parameter number 1 is not an OUT parameter
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1094)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:997)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:983)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:928)
at com.mysql.jdbc.CallableStatement.checkIsOutputParam(CallableStatement.java:695)
at com.mysql.jdbc.CallableStatement.registerOutParameter(CallableStatement.java:2016)
at test.Test.main(Test.java:16)
I have been trying to find out what is wrong many hours, but without success. I saw many questions, but : this is useless because in function cannot be transaction
this is useless because when i use named parameter NullPointerEx is throwed(why??)
this is useless because i cant see any obvious mistake
this is useless because update JDBC connector didnt help
So my question is simple : Any idea what can be wrong?
I would try two things:
"jdbc:mysql://127.0.0.1/test_database"
connection string, and remove the call of execute("USE test_database")
setInt
and registerOutParameter
lines.In general, executing USE test_database
should be fine, but MySQL documentation explicitly cautions agains using it:
Always use the
Connection.setCatalog()
method to specify the desired database in JDBC applications, rather than theUSE database
statement.
Although the context is slightly different, it appears that the same advise applies to your situation as well.