CallableStatement setInt named parameter exception

Question

I am trying to call a stored procedure by parameter name:

int processId = 1;
CallableStatement stmt = conn.prepareCall("{call get_process_log_latest(?)}");
stmt.setInt("process_id", processId);

But setInt() throws a NullPointerException:

NullPointerException

However, stmt is not null; the exception is thrown from within setInt() on line 2065 from the method getNamedParamIndex() at line 1381.

The stored procedure:

DELIMITER $$
CREATE PROCEDURE `get_process_log_latest`(
    IN process_id INT
)
BEGIN
    SELECT
        id,
        start_time,
        end_time
    FROM process_logs pl
    WHERE pl.process_id = process_id
    ORDER BY id DESC
    LIMIT 1;
END$$
DELIMITER ;

I am using JDK 8, mysql-connector-java-5.1.38, and MySQL 5.7.12.

Do you know what I may be doing wrong?

UPDATE

Authenticated into MySQL with account used by Java and used

SELECT * FROM information_schema.parameters   
WHERE parameter_name = 'process_id';

to confirm the parameter name of the stored procedure and that the account has metadata privileges.

Though I am still curious about the cause of this, my workaround is getting the parameter indexes from the information_schema.parameters table and storing them in a dictionary. — plantbeard, Apr 02 '17 at 01:37
There is nothing wrong in your code. This is a bug in the [mysql-connector-j](https://github.com/mysql/mysql-connector-j/blob/5.1.38/src/com/mysql/jdbc/CallableStatement.java). There is no check if `namedParmInfo != null`. Workaround is to use the sql parameter index. — andih, Apr 02 '17 at 03:45
@chrylis I don't see why this is a duplicate. The problem here is that the mySql JDBC Driver internally invokes `namedParamInfo.index + 1` without checking if the parameter name passed to the method could be resolved to a parameter info object. — andih, Apr 02 '17 at 06:37
@andih Thanks for the ping. OP added information that demonstrated it's not a standard NPE question, and I reopened. — chrylis -cautiouslyoptimistic-, Apr 02 '17 at 07:58
@andih Thanks for the info. Indeed that is a bug, but it does not explain why namedParmInfo is null in the first place. — plantbeard, Apr 02 '17 at 16:45

Laxman Edara · Answer 1 · 2021-05-13T20:10:57.340

The problem is you are trying to set the the values using named parameters but you not using in same way in callable statement. In your case use Operational

Here are the two things you need to follow with CallableStatements

Operational:

int processId = 1;
CallableStatement stmt = conn.prepareCall("{call get_process_log_latest(?)}");
stmt.setInt(1, processId);

Named Paramenets:

int processId = 1;
CallableStatement stmt = conn.prepareCall("{call get_process_log_latest(:process_id)}");
stmt.setInt("process_id", processId);

if you want to add multiple named paramenets in callable statement

int processId = 1;
CallableStatement stmt = conn.prepareCall("{call get_process_log_latest(:process_id, :process_name)}");
stmt.setInt("process_id", processId);
stmt.setString("process_name", processName);

The documentation mentions another syntax: "{call get_process_log_latest(process_id => ?, process_name => ?)}" For me, neither your version nor mine above works. — Nadia Solovyeva, Apr 17 '20 at 04:10

CallableStatement setInt named parameter exception

1 Answers1