ヰ刀のおもちゃ箱 music×IT×guiter×programing

注目キーワード

Spring batchでmybatisがエラー（SQLCODE=-180, SQLSTATE=22007）

Spring batchでmybatisがエラー（SQLCODE=-180, SQLSTATE=22007）
目次

事象

Spring batchでDB2を使用しており、それまでは正常に動作していた。
DB2のバージョンをv11.1.2 fixpack2からv11.1.3.3 fixpack3に変更後、DBアクセス周りのテストを実行したところ、
SqlDataExceptionが発生し、SQLCODE=-180, SQLSTATE=22007と出力された。

SQLは以下の通り。

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
SELECT
    *
FROM
    HOGESCHEMA.POGETABLE
WHERE
    HOGESCHEMA.POGETABLE.hoge1 = RIGHT('0000000000' || TO_CHAR(?),10)
AND
(
    (
        HOGESCHEMA.POGETABLE.puge1 LIKE CONCAT(?,'%')
        OR
        HOGESCHEMA.POGETABLE.puge2 LIKE CONCAT(?,'%')
    )
    OR
    (
        HOGESCHEMA.POGETABLE.hogehoge1 LIKE CONCAT(?,'%')
        AND
        HOGESCHEMA.POGETABLE.pageStatus >= 32
    )
)

エラー内容

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
### Error querying database.  Cause: com.ibm.db2.jcc.am.SqlDataException: DB2 SQL Error: SQLCODE=-180, SQLSTATE=22007, SQLERRMC=null, DRIVER=4.23.42
### The error may exist in jp/repository/Hoge/HogeRepository.xml
### The error may involve jp.repository.Hoge.HogeRepository.select_MainTable_001-Inline
### The error occurred while setting parameters
### SQL: SELECT * FROM HOGESCHEMA.POGETABLE WHERE HOGESCHEMA.POGETABLE.hoge1 = RIGHT('0000000000' || TO_CHAR(?),10) AND ( ( HOGESCHEMA.POGETABLE.puge1 LIKE CONCAT(?,'%') OR HOGESCHEMA.POGETABLE.puge2 LIKE CONCAT(?,'%') ) OR ( HOGESCHEMA.POGETABLE.hogehoge1 LIKE CONCAT(?,'%') AND HOGESCHEMA.POGETABLE.pageStatus >= 32 ) )
### Cause: com.ibm.db2.jcc.am.SqlDataException: DB2 SQL Error: SQLCODE=-180, SQLSTATE=22007, SQLERRMC=null, DRIVER=4.23.42
; SQL []; DB2 SQL Error: SQLCODE=-180, SQLSTATE=22007, SQLERRMC=null, DRIVER=4.23.42; nested exception is com.ibm.db2.jcc.am.SqlDataException: DB2 SQL Error: SQLCODE=-180, SQLSTATE=22007, SQLERRMC=null, DRIVER=4.23.42
    at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:82)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
    at com.sun.proxy.$Proxy36.selectList(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)
    at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:137)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:75)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
    at com.sun.proxy.$Proxy38.select_MainTable_001(Unknown Source)
    at jp.repository.Hoge.HogeRepositoryTest.repositoryTest(HogeRepositoryTest.java:180)
    ... 44 more
Caused by: com.ibm.db2.jcc.am.SqlDataException: DB2 SQL Error: SQLCODE=-180, SQLSTATE=22007, SQLERRMC=null, DRIVER=4.23.42
    at com.ibm.db2.jcc.am.ld.a(ld.java:802)
    at com.ibm.db2.jcc.am.ld.a(ld.java:66)
    at com.ibm.db2.jcc.am.ld.a(ld.java:140)
    at com.ibm.db2.jcc.am.aq.c(aq.java:2807)
    at com.ibm.db2.jcc.am.aq.a(aq.java:2244)
    at com.ibm.db2.jcc.t4.bb.r(bb.java:1463)
    at com.ibm.db2.jcc.t4.bb.l(bb.java:588)
    at com.ibm.db2.jcc.t4.bb.d(bb.java:98)
    at com.ibm.db2.jcc.t4.p.c(p.java:44)
    at com.ibm.db2.jcc.t4.vb.j(vb.java:162)
    at com.ibm.db2.jcc.am.aq.nb(aq.java:2239)
    at com.ibm.db2.jcc.am.bq.a(bq.java:4637)
    at com.ibm.db2.jcc.am.bq.b(bq.java:4154)
    at com.ibm.db2.jcc.am.bq.qc(bq.java:2874)
    at com.ibm.db2.jcc.am.bq.execute(bq.java:2849)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:198)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:198)
    at net.sf.log4jdbc.PreparedStatementSpy.execute(PreparedStatementSpy.java:418)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
    at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
    at sun.reflect.GeneratedMethodAccessor85.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
    ... 51 more
[2018/12/04 12:26:47] [main] [j.c.n.s.r.p.d.B.HogeRepositoryTest] [ERROR] 9件のAssertionErrorが発生しています
Tests run: 2, Failures: 1, Errors: 0, Skipped: 0, Time elapsed: 226.489 sec <<< FAILURE! - in jp.repository.Hoge.HogeRepositoryTest
jp.repository.Hoge.HogeRepositoryTest  Time elapsed: 225.49 sec  <<< FAILURE!
java.lang.AssertionError: 9件のAssertionErrorが発生しています
    at jp.repository.HogeRepositoryTest.creanUpAfterTest(HogeRepositoryTest.java:144)
 
[2018/12/04 12:26:47] [Thread-1] [o.s.c.s.GenericApplicationContext] [INFO ] Closing org.springframework.context.support.GenericApplicationContext@2f8dad04: startup date [Tue Dec 04 12:23:03 JST 2018]; root of context hierarchy
 
Results :
 
Failed tests:
  HogeRepositoryTest.creanUpAfterTest:144 9件のAssertionErrorが発生しています
 
Tests run: 2, Failures: 1, Errors: 0, Skipped: 0
 
[INFO] ------------------------------------------------------------------------
[INFO] Reactor Summary:
[INFO]
[INFO] testProject-parent 1.0-SNAPSHOT .................. SUCCESS [  0.704 s]
[INFO] testProject-env .................................. SUCCESS [  2.849 s]
[INFO] testProject 1.0.0-SNAPSHOT ....................... FAILURE [06:14 min]
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 06:18 min
[INFO] Finished at: 2018-12-04T12:26:48+09:00
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal org.apache.maven.plugins:maven-surefire-plugin:2.19.1:test (default-test) on project testProject: There are test failures.
[ERROR]
[ERROR] Please refer to C:\Users\Jiki-Admin\.jenkins\workspace\000_testProject-parent single_Test\testProject\target\surefire-reports for the individual test results.
[ERROR] -> [Help 1]
[ERROR]
[ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
[ERROR] Re-run Maven using the -X switch to enable full debug logging.
[ERROR]
[ERROR] For more information about the errors and possible solutions, please read the following articles:
[ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoFailureException
[ERROR]
[ERROR] After correcting the problems, you can resume the build with the command
[ERROR]   mvn <goals> -rf :testProject
Build step 'Mavenの呼び出し' marked build as failure
Finished: FAILURE

原因

SQLCODE=-180, SQLSTATE=22007は
DATE、TIME、または TIMESTAMP value の、長さまたはストリング表記が、 有効な形式ではありません。
ということらしい。

しかし、SELECT対象のテーブルには日付型のカラムは存在しない。
どうやら、tableにはDate型やTIMESTAMP型などの設定はしていなかったが、
TO_CHAR関数を使用し、フォーマットを指定していなかったことが原因らしい。

解決策

TO_CHAR関数にフォーマットを追加する。

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
SELECT
    *
FROM
    HOGESCHEMA.POGETABLE
WHERE
    HOGESCHEMA.POGETABLE.hoge1 = RIGHT('0000000000' || TO_CHAR(?,'YYYYMMDD'),10)
AND
(
    (
        HOGESCHEMA.POGETABLE.puge1 LIKE CONCAT(?,'%')
        OR
        HOGESCHEMA.POGETABLE.puge2 LIKE CONCAT(?,'%')
    )
    OR
    (
        HOGESCHEMA.POGETABLE.hogehoge1 LIKE CONCAT(?,'%')
        AND
        HOGESCHEMA.POGETABLE.pageStatus >= 32
    )
)

備考

なんで元々のSQLで動いていたかは不明。
fixPackが更新されてTO_CHARの使い方が厳密になった・・？




エラー・バグ対処カテゴリの最新記事