SimpleJdbcCall for MySql Function yields "Can't set IN parameter for return value of stored function call"

Question

Using the example from the Spring docs, I'm trying to return a value from a mySQL function. I keep getting the error Can't set IN parameter for return value of stored function call;.

I created a mySQL function that works fine (ran in MySQL Workbench). I've written a SimpleJdbcCall statement, set up the parameters as per Spring docs example but consistently get this error. If I turn the function into a procedure, the code works, I just have to retrieve the return value from the result set.

I used https://docs.spring.io/spring/docs/3.0.0.M3/reference/html/ch13s05.html, section 13.5.8 as reference.

CREATE FUNCTION `ScenarioRegistration`(
  environment VARCHAR(45),
  username VARCHAR(15),
  scenario_name VARCHAR(45)) RETURNS int(11)

A couple of SELECT statements followed by an INSERT then

RETURN scenario_id; // The inserted id

Java code:

SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(getJdbcTemplate())
 .withFunctionName("ScenarioRegistration")
 .withoutProcedureColumnMetaDataAccess();

simpleJdbcCall.addDeclaredParameter(new SqlParameter("environment"
  ,Types.VARCHAR));

simpleJdbcCall.addDeclaredParameter(new SqlParameter("username" 
  ,Types.VARCHAR));

simpleJdbcCall.addDeclaredParameter(new SqlParameter("scenario_name"
  ,Types.VARCHAR));    

SqlParameterSource parameters = new MapSqlParameterSource()
  .addValue("environment", environment)
  .addValue("username", username)
  .addValue("scenario_name", scenario);

simpleJdbcCall.setReturnValueRequired(true);

Integer scenario_id =  simpleJdbcCall.executeFunction(
   Integer.class, parameters);    

All I want the routine to do is give me back the id of the newly inserted scenario. What I get is: SQL [{? = call scenarioregistration(?, ?)}]; Can't set IN parameter for return value of stored function call.

I find it interesting that it's taken my THREE input values and changed them to an output and TWO input values.

Anyone enlighten me as to the problem and how to fix it?

Thanks,

Steven.

Answer 1

I would refer to the latest docs here for your answer. It appears Spring is trying to infer the output because you didn't explicity specify one.

Per the docs above there are two valid approaches on calling the desired function with the SimpleJdbcCall:

Inferred Parameters

Because you've specified withoutProcedureColumnMetaDataAccess, Spring isn't going to look and see what the ins/outs are to your function. If you want it easy, just don't specify that and you should be able to do:

SqlParameterSource parameters = new MapSqlParameterSource()
  .addValue("environment", environment)
  .addValue("username", username)
  .addValue("scenario_name", scenario);

Integer scenarioId = new SimpleJdbcCall(getJdbcTemplate())
    .withFunctionName("ScenarioRegistration")
    .executeFunction(Integer.class, parameters);

Explicit Parameters

If you want to keep withoutProcedureColumnMetaDataAccess turned off for whatever reason, you can do:

Integer scenarioId = new SimpleJdbcCall(getJdbcTemplate)
    .withFunctionName("ScenarioRegistration")
    .withoutProcedureColumnMetaDataAccess()
    .useInParameterNames("environment", "username", "scenario_name")
    .declareParameters(
        new SqlOutParameter("scenario_id", Types.NUMERIC),
        new SqlParameter("environment", Types.VARCHAR),
        new SqlParameter("username", Types.VARCHAR),
        new SqlParameter("scenario_name", Types.VARCHAR)            
    ).executeFunction(Integer.class, parameters);

Note: It appears that order is critical in this example. The output parameter should be declared first, and the subsequent named IN parameters come last. That is, the order of the parameters ? are ordinal in [{? = call scenarioregistration(?, ?, ?)}])

Alternative NamedParameterJdbcTemplate Solution

Another way to invoke your function is via an actual JDBC call. This could hypothetically save you the grief of using the fine tuning of the SimpleJdbcCall.

Integer scenarioId = namedParameterJdbcTemplate.queryForObject(
    "SELECT ScenarioRegistration(:environment, :username, :scenario_name)", 
    parameters, 
    Integer.class);