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));
Stringvalue"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.