Mybatis could not set OUT parameter when executing stored procedure

Question

I am following this question to call a stored procedure with 1 input parameter and 1 out parameter:

Mapper:

package myapp.test.persistence;

import myapp.test.ExpectationProcessMessage;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface ExpectationProcessMessageMapper {
    List<ExpectationProcessMessage> processExpectation(Map<String, Object> params);
}

XML content:

<mapper namespace="myapp.test.persistence.ExpectationProcessMessageMapper">

    <resultMap id="expectationProcessMessages" type="myapp.test.ExpectationProcessMessage">
        <id property="errorCode" column="error_code"/>
        <result property="errorMessage" column="error_message"/>
    </resultMap>

    <select id="processExpectation" parameterType="java.util.HashMap" resultMap="expectationProcessMessages">
      {call cp_process_expectation_api (#{ai_expectation_id,jdbcType=NUMERIC, javaType=java.lang.Integer,mode=IN},
                                         #{ac_process_flag,jdbcType=VARCHAR, javaType=java.lang.String,mode=OUT})}
    </select>
</mapper>

Call class:

package myapp.test.persistence;

@Repository
public class SqlExpectationRepositoryImpl implements ExpectationRepository {
    ...
    @Override
    public ExpectationProcess processExpectation(Integer expectationId) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("ai_expectation_id", expectationId);
        params.put("ac_process_flag", null);
        List<ExpectationProcessMessage> expectationProcessMessages = expectationProcessMessageMapper.processExpectation(params);
        System.out.println(params.get("ac_process_flag"));
    }
    ...
}

Stored procedure definition:

begin
    declare @ai_expectation_id int = 0
    declare @ac_process_flag char
    declare @result int
    exec
        @result = cp_process_expectation_api
            @ai_expectation_id,
            @ac_process_flag output

    select @ac_process_flag

    select @result as result
end

But I got an exception:

Cause: com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 2.
Uncategorized SQLException; SQL state [null]; error code [0]; The value is not set for the parameter number 2.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 2.

What's the definition of your stored procedure? Maybe not the whole procedure, just the header part where you define the parameter list... — Richard Hansell, Mar 23 '20 at 09:26
@RichardHansell I have included the SP definition to the question. — Hoang Nguyen, Mar 23 '20 at 09:30
Okay, that's the bit we probably don't need to see, can you include the `CREATE` part, as that's key here, basically everything before the first `BEGIN`? Actually, is that even the stored procedure definition? It looks like this is just a script to call `cp_process_expectation_api`, and that's the actual stored procedure? — Richard Hansell, Mar 23 '20 at 10:07

score 0 · Answer 1 · answered Mar 23 '20 at 11:08

I have found the solution. We need to add statementType="CALLABLE" property to XML file. It will be like this:

<mapper namespace="com.gbst.processmanagement.infra.persistence.expectation.expectation.ExpectationProcessMessageMapper">

    <resultMap id="expectationProcessMessages" type="com.gbst.processmanagement.domain.expectation.expectation.ExpectationProcessMessage">
        <id property="errorCode" column="error_code"/>
        <id property="messageType" column="message_type"/>
        <result property="errorPointer" column="error_pointer"/>
        <result property="errorMessage" column="error_message"/>
    </resultMap>

    <select id="processExpectation" statementType="CALLABLE" parameterType="java.util.HashMap" resultMap="expectationProcessMessages">
      {call cp_process_expectation_api (#{expectationId,jdbcType=NUMERIC, javaType=java.lang.Integer,mode=IN},
                                        #{processFlag,jdbcType=VARCHAR, javaType=java.lang.String,mode=OUT})}
    </select>
</mapper>

Mybatis could not set OUT parameter when executing stored procedure

1 Answers1