0

Im getting this error when calling a Stored_Procedure in SqlServer from MyBatis:

> ### Error querying database.  Cause: java.sql.SQLException: Invalid java.sql.Types constant value -9 passed to set or update method.
> ### The error may exist in mybatis-mapper/UpstreamMapper.xml
> ### The error may involve callStoredProcedure
> ### The error occurred while executing a query
> ### SQL: {call dbo.get_discount_value(?,?,       ?,?,?,?)}
> ### Cause: java.sql.SQLException: Invalid java.sql.Types constant value -9 passed to set or update method.

Do I need to match the types of the variable in my Stored_Procedure with that of my POJO?

So if these are the variables of Stored_Procedure:

CREATE PROCEDURE [dbo].[get_discount_value]
    @firstname nvarchar(50),
    @lastname nvarchar(10),
    @gender nvarchar(1),
    @dateOfBirth date,
    @age bigint
AS
BEGIN 
 .. SP BODY..

POJO: StoredProcRef.java

public class StoredProcRef {

    String  SP_FirstName= "";
    String  SP_LastName= "";
    String  SP_Gender = "";
    Date    SP_Birthday = null;
    Integer SP_Age = 0;
    String  Output = "";
    ..GETTERS AND SETTERS..
}

MapperXML.xml

<resultMap id = "ReferenceValueParams"  type="StoredProcRef" autoMapping="false" >
      <result property = "SP_FirstName"         column = "SP_FirstName"/>
      <result property = "SP_LastName"          column = "SP_LastName"/>
      <result property = "SP_Gender"            column = "SP_Gender"/>
      <result property = "SP_Birthday"          column = "SP_Birthday"/>
      <result property = "SP_Age"               column = "SP_Age"/>
      <result property = "Output"               column = "DiscountValue"/>
   </resultMap>   

        <select id = "callStoredProcedure" statementType = "CALLABLE" resultMap="ReferenceValueParams">
              {call dbo.lab_get_result_form_field(
     #{SP_FirstName,mode=IN,jdbcType=NVARCHAR},
     #{SP_LastName,mode=IN,jdbcType=NVARCHAR},
     #{SP_Gender,mode=IN,jdbcType=NVARCHAR},
     #{SP_Birthday,mode=IN,jdbcType=DATE},
     #{SP_Age,mode=IN,jdbcType=BIGINT},
     #{Output,mode=OUT,jdbcType=NVARCHAR}
    )}
      </select> 

Interfacemapper.java

public interface UpstreamXmlMapper {   
         public List<Map<String, ?>> callStoredProcedure(String FirstName, String LastName, String gender, Date dateOfBirth, Integer age);
}

Service.java

    public List<Map<String, ?>> getDiscountValue( StoredProcRef storedProcRef ) {

                List<Map<String, ?>> referenceValue = new ArrayList<>();
                SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession();
                try {
                    UpstreamXmlMapper applicationMapper = session.getMapper(UpstreamXmlMapper.class);
                    referenceValue = applicationMapper.callStoredProcedure(storedProcRef.getSP_FirstName(), storedProcRef.getLastName(), storedProcRef.getSP_Gender(), storedProcRef.getSP_Birthday(), storedProcRef.getSP_Age()) ;

                    session.commit();
                } catch (Exception e) {
                    LOGGER.error(e);
                } finally {
                    session.close();
                }
                return referenceValue;          
        }       

Main.java

        SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy");
        sdf.setTimeZone(TimeZone.getTimeZone("GMT"));
        Date date;
        date = sdf.parse("01-Oct-1984");

        List<StoredProcRef> storedProcRefList = new ArrayList<>();
        StoredProcRef storedProcRefObj = new StoredProcRef();
        storedProcRefObj.setSP_Age(28);
        storedProcRefObj.setSP_Birthday(date);
        storedProcRefObj.setSP_Gender("M");
        storedProcRefObj.setSP_FirstName("Joe");
        storedProcRefObj.setSP_LastName("Higashi");
        storedProcRefList.add(storedProcRefObj);

        List<Map<String, ?>> referenceValue = new ArrayList<>();
        referenceValue = service.getDiscountvalue(storedProcRefList.get(0));
4
  • 1
    "Do I need to match the types of the variable in my Stored_Procedure" You should do yes. Especially for data types like dates. If you ended up with the String value "01/02/03" that could be one of 6 different dates and you would be at the mercy of SQL Server "choosing" what it was based on the data type of the parameter and language of the login. Always be explicit. Commented Nov 13, 2019 at 10:40
  • @Larnu made them match, but still erroneous, im stuck FFS Commented Nov 13, 2019 at 11:32
  • Same error, different? Commented Nov 13, 2019 at 11:35
  • @Larnu yes , same error , updated my post, that's pretty much all of my code Commented Nov 13, 2019 at 11:47

1 Answer 1

3

You seem to be confused about input and output parameters.
As the procedure declares no OUT parameter, the result will be mapped to a different object.
I'll use the new class below for this explanation.

public class StoredProcOutput {
  private String Output;
  // getter/setter
}

As the procedure is declared with five parameters, there should be five parameters in the call statement, not six. i.e.

<select id="callStoredProcedure" statementType="CALLABLE"
    resultMap="outputResultMap">
  {call MY_PROC(
  #{SP_FirstName,mode=IN,jdbcType=NVARCHAR},
  #{SP_LastName,mode=IN,jdbcType=NVARCHAR},
  #{SP_Gender,mode=IN,jdbcType=NVARCHAR},
  #{SP_Birthday,mode=IN,jdbcType=DATE},
  #{SP_Age,mode=IN,jdbcType=BIGINT}
  )}
</select>

The result map is used to map the result of a query (there is a query in your procedure, right?).
As you just want DiscountValue, it is simple.

<resultMap id="outputResultMap" type="test.StoredProcOutput">
  <result property="Output" column="DiscountValue" />
</resultMap>

And your mapper method should look as follows:

List<StoredProcOutput> callStoredProcedure(StoredProcInput inParam);

I have updated the demo project as well.
https://github.com/harawata/mybatis-issues/tree/master/so-58802623

I may be available for a chat for a while if you need further help (I'm not sure how the chat works, though).

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.