CODEWITHSUNDEEP
javasqlmybatis
gadiz
gadiz

Reputation: 31

In MyBatis, is there any solution for not adding jdbcType when setting null parameters?

I'm using mybatis-3.2.8.jar and JDK 1.6.0_45.

The source table is called emp:

> EMPNO ENAME   JOB         MGR     HIREDATE            SAL     COMM  DEPTNO  SHORTCUT
> 
> 7499  ALLEN   SALESMAN    7698    1981/02/20 00:00:00 1600    300   30 null

and the SQL is this:

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, SHORTCUT)
values
(#{EMPNO},#{ENAME},#{JOB},#{MGR},#{HIREDATE},#{SAL},#{COMM},#{DEPTNO},#{SHORTCUT});

I'm using this SQL row by row. Select 1 row then insert, again and again. Or think the table just has 1 row.

Then I'm facing this error:

org.apache.ibatis.exceptions.PersistenceException: 
#### Error updating database.  Cause: org.apache.ibatis.type.TypeException: Error setting null for parameter #7 with JdbcType OTHER . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=insert into TB_ODS_EMP2(EMPNO,ENAME,JOB,MGR;BEGIN-OF-STATEMENT;<grant>, DRIVER=4.12.55
#### The error may involve pdss5.hs.hdw.ETLTargetMapper.insertTargetTable-Inline
#### The error occurred while setting parameters
#### SQL: insert into TB_ODS_EMP2(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,SHORTCUT) values(?,?,?,?,?,?,?,?,?) Call getNextException to see the cause
#### Cause: org.apache.ibatis.type.TypeException: Error setting null for parameter #7 with JdbcType OTHER . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=insert into TB_ODS_EMP2(EMPNO,ENAME,JOB,MGR;BEGIN-OF-STATEMENT;<grant>, DRIVER=4.12.55

My chief didn't want to use jdbyTYPE like this:

insert into emp
(#{EMPNO},#{ENAME},#{JOB},#{MGR},#{HIREDATE},#{SAL},#{COMM},#{DEPTNO},`#{SHORTCUT,jdbcTYPE=VARCHAR}`)
 ......

In MyBatis, is there any solution for not adding jdbcType when setting null parameters?

I think adding jdbcTYPE=VARCHAR is good enough, but my chief hates it (maybe...).

Upvotes: 0

Views: 9579

Answers (2)

Bogdan
Bogdan

Reputation: 24580

Some databases don't allow untyped null to be sent in the query so you should send it or switching from one database vendor to another might later cause your SQL to fail.

See here for extra explanations: Is jdbcType necessary in a MyBatis mapper?

The jdbcType is not actually a MyBatis requirement but a JDBC one. It's good practice to always send jdbcType for null parameters.

Upvotes: 1

gadiz
gadiz

Reputation: 31

I give up.

DB2 is super-typed database.

Oracle is ok to non-typed inserting through mybatis. Ofcourse set the jdbdtypeForNull in MyBatis.

But DB2 is NOT.

Upvotes: 0

Related Questions