db2 报错之 DB2 SQL Error: SQLCODE=-7, SQLSTATE=42601
记录一个困扰了两天的问题,使用ssm框架搭配 db2 数据库,一直在插入数据时报错,报错内容如下:
Exception in thread “main” org.springframework.jdbc.BadSqlGrammarException:
Error updating database. Cause: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-7, SQLSTATE=42601, SQLERRMC=
;ate_date,spp_status), DRIVER=3.59.81
The error may involve com.ilogst.part.dao.PhonePlanMapper.savePhonePlan-Inline
The error occurred while setting parameters
SQL: insert into service_phone_plan (spp_plan_id,spp_user_id,spp_unit_model,spp_unit_id,spp_plan_date,spp_plan_content,spp_create_date,spp_status) values (?,?,?,?,?,?,?,?)
Cause: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-7, SQLSTATE=42601, SQLERRMC=
;ate_date,spp_status), DRIVER=3.59.81
; bad SQL grammar []; nested exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-7, SQLSTATE=42601, SQLERRMC=
;ate_date,spp_status), DRIVER=3.59.81
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:98)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
at org.mybatis.spring.SqlSessionTemplate
S
q
l
S
e
s
s
i
o
n
I
n
t
e
r
c
e
p
t
o
r
.
i
n
v
o
k
e
(
S
q
l
S
e
s
s
i
o
n
T
e
m
p
l
a
t
e
.
j
a
v
a
:
368
)
a
t
c
o
m
.
s
u
n
.
p
r
o
x
y
.
SqlSessionInterceptor.invoke(SqlSessionTemplate.java:368) at com.sun.proxy.
SqlSessionInterceptor.invoke(SqlSessionTemplate.java:368)atcom.sun.proxy.Proxy5.insert(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:240)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:46)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:43)
at com.sun.proxy.
P
r
o
x
y
65.
s
a
v
e
P
h
o
n
e
P
l
a
n
(
U
n
k
n
o
w
n
S
o
u
r
c
e
)
a
t
c
o
m
.
i
l
o
g
s
t
.
p
a
r
t
.
s
e
r
v
i
c
e
.
i
m
p
l
.
P
h
o
n
e
P
l
a
n
S
e
r
v
i
c
e
I
m
p
l
.
s
a
v
e
P
h
o
n
e
P
l
a
n
(
P
h
o
n
e
P
l
a
n
S
e
r
v
i
c
e
I
m
p
l
.
j
a
v
a
:
44
)
a
t
s
u
n
.
r
e
f
l
e
c
t
.
N
a
t
i
v
e
M
e
t
h
o
d
A
c
c
e
s
s
o
r
I
m
p
l
.
i
n
v
o
k
e
0
(
N
a
t
i
v
e
M
e
t
h
o
d
)
a
t
s
u
n
.
r
e
f
l
e
c
t
.
N
a
t
i
v
e
M
e
t
h
o
d
A
c
c
e
s
s
o
r
I
m
p
l
.
i
n
v
o
k
e
(
N
a
t
i
v
e
M
e
t
h
o
d
A
c
c
e
s
s
o
r
I
m
p
l
.
j
a
v
a
:
57
)
a
t
s
u
n
.
r
e
f
l
e
c
t
.
D
e
l
e
g
a
t
i
n
g
M
e
t
h
o
d
A
c
c
e
s
s
o
r
I
m
p
l
.
i
n
v
o
k
e
(
D
e
l
e
g
a
t
i
n
g
M
e
t
h
o
d
A
c
c
e
s
s
o
r
I
m
p
l
.
j
a
v
a
:
43
)
a
t
j
a
v
a
.
l
a
n
g
.
r
e
f
l
e
c
t
.
M
e
t
h
o
d
.
i
n
v
o
k
e
(
M
e
t
h
o
d
.
j
a
v
a
:
606
)
a
t
o
r
g
.
s
p
r
i
n
g
f
r
a
m
e
w
o
r
k
.
a
o
p
.
s
u
p
p
o
r
t
.
A
o
p
U
t
i
l
s
.
i
n
v
o
k
e
J
o
i
n
p
o
i
n
t
U
s
i
n
g
R
e
f
l
e
c
t
i
o
n
(
A
o
p
U
t
i
l
s
.
j
a
v
a
:
319
)
a
t
o
r
g
.
s
p
r
i
n
g
f
r
a
m
e
w
o
r
k
.
a
o
p
.
f
r
a
m
e
w
o
r
k
.
R
e
f
l
e
c
t
i
v
e
M
e
t
h
o
d
I
n
v
o
c
a
t
i
o
n
.
i
n
v
o
k
e
J
o
i
n
p
o
i
n
t
(
R
e
f
l
e
c
t
i
v
e
M
e
t
h
o
d
I
n
v
o
c
a
t
i
o
n
.
j
a
v
a
:
183
)
a
t
o
r
g
.
s
p
r
i
n
g
f
r
a
m
e
w
o
r
k
.
a
o
p
.
f
r
a
m
e
w
o
r
k
.
R
e
f
l
e
c
t
i
v
e
M
e
t
h
o
d
I
n
v
o
c
a
t
i
o
n
.
p
r
o
c
e
e
d
(
R
e
f
l
e
c
t
i
v
e
M
e
t
h
o
d
I
n
v
o
c
a
t
i
o
n
.
j
a
v
a
:
150
)
a
t
o
r
g
.
s
p
r
i
n
g
f
r
a
m
e
w
o
r
k
.
t
r
a
n
s
a
c
t
i
o
n
.
i
n
t
e
r
c
e
p
t
o
r
.
T
r
a
n
s
a
c
t
i
o
n
I
n
t
e
r
c
e
p
t
o
r
.
i
n
v
o
k
e
(
T
r
a
n
s
a
c
t
i
o
n
I
n
t
e
r
c
e
p
t
o
r
.
j
a
v
a
:
110
)
a
t
o
r
g
.
s
p
r
i
n
g
f
r
a
m
e
w
o
r
k
.
a
o
p
.
f
r
a
m
e
w
o
r
k
.
R
e
f
l
e
c
t
i
v
e
M
e
t
h
o
d
I
n
v
o
c
a
t
i
o
n
.
p
r
o
c
e
e
d
(
R
e
f
l
e
c
t
i
v
e
M
e
t
h
o
d
I
n
v
o
c
a
t
i
o
n
.
j
a
v
a
:
172
)
a
t
o
r
g
.
s
p
r
i
n
g
f
r
a
m
e
w
o
r
k
.
a
o
p
.
f
r
a
m
e
w
o
r
k
.
J
d
k
D
y
n
a
m
i
c
A
o
p
P
r
o
x
y
.
i
n
v
o
k
e
(
J
d
k
D
y
n
a
m
i
c
A
o
p
P
r
o
x
y
.
j
a
v
a
:
202
)
a
t
c
o
m
.
s
u
n
.
p
r
o
x
y
.
Proxy65.savePhonePlan(Unknown Source) at com.ilogst.part.service.impl.PhonePlanServiceImpl.savePhonePlan(PhonePlanServiceImpl.java:44) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:319) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202) at com.sun.proxy.
Proxy65.savePhonePlan(UnknownSource)atcom.ilogst.part.service.impl.PhonePlanServiceImpl.savePhonePlan(PhonePlanServiceImpl.java:44)atsun.reflect.NativeMethodAccessorImpl.invoke0(NativeMethod)atsun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)atsun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)atjava.lang.reflect.Method.invoke(Method.java:606)atorg.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:319)atorg.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)atorg.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)atorg.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)atorg.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)atorg.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)atcom.sun.proxy.Proxy66.savePhonePlan(Unknown Source)
at com.part.TestPhonePlan.main(TestPhonePlan.java:45)
Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-7, SQLSTATE=42601, SQLERRMC=
;ate_date,spp_status), DRIVER=3.59.81
at com.ibm.db2.jcc.am.dd.a(dd.java:676)
at com.ibm.db2.jcc.am.dd.a(dd.java:60)
at com.ibm.db2.jcc.am.dd.a(dd.java:127)
at com.ibm.db2.jcc.am.bn.c(bn.java:2546)
at com.ibm.db2.jcc.am.bn.d(bn.java:2534)
at com.ibm.db2.jcc.am.bn.a(bn.java:2026)
at com.ibm.db2.jcc.t4.cb.g(cb.java:140)
at com.ibm.db2.jcc.t4.cb.a(cb.java:40)
at com.ibm.db2.jcc.t4.q.a(q.java:32)
at com.ibm.db2.jcc.t4.rb.i(rb.java:135)
at com.ibm.db2.jcc.am.bn.gb(bn.java:1997)
at com.ibm.db2.jcc.am.cn.pc(cn.java:3009)
at com.ibm.db2.jcc.am.cn.b(cn.java:3786)
at com.ibm.db2.jcc.am.cn.ec(cn.java:2261)
at com.ibm.db2.jcc.am.cn.execute(cn.java:2245)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:55)
at com.sun.proxy.
P
r
o
x
y
72.
e
x
e
c
u
t
e
(
U
n
k
n
o
w
n
S
o
u
r
c
e
)
a
t
o
r
g
.
a
p
a
c
h
e
.
i
b
a
t
i
s
.
e
x
e
c
u
t
o
r
.
s
t
a
t
e
m
e
n
t
.
P
r
e
p
a
r
e
d
S
t
a
t
e
m
e
n
t
H
a
n
d
l
e
r
.
u
p
d
a
t
e
(
P
r
e
p
a
r
e
d
S
t
a
t
e
m
e
n
t
H
a
n
d
l
e
r
.
j
a
v
a
:
41
)
a
t
o
r
g
.
a
p
a
c
h
e
.
i
b
a
t
i
s
.
e
x
e
c
u
t
o
r
.
s
t
a
t
e
m
e
n
t
.
R
o
u
t
i
n
g
S
t
a
t
e
m
e
n
t
H
a
n
d
l
e
r
.
u
p
d
a
t
e
(
R
o
u
t
i
n
g
S
t
a
t
e
m
e
n
t
H
a
n
d
l
e
r
.
j
a
v
a
:
66
)
a
t
o
r
g
.
a
p
a
c
h
e
.
i
b
a
t
i
s
.
e
x
e
c
u
t
o
r
.
R
e
u
s
e
E
x
e
c
u
t
o
r
.
d
o
U
p
d
a
t
e
(
R
e
u
s
e
E
x
e
c
u
t
o
r
.
j
a
v
a
:
47
)
a
t
o
r
g
.
a
p
a
c
h
e
.
i
b
a
t
i
s
.
e
x
e
c
u
t
o
r
.
B
a
s
e
E
x
e
c
u
t
o
r
.
u
p
d
a
t
e
(
B
a
s
e
E
x
e
c
u
t
o
r
.
j
a
v
a
:
100
)
a
t
o
r
g
.
a
p
a
c
h
e
.
i
b
a
t
i
s
.
s
e
s
s
i
o
n
.
d
e
f
a
u
l
t
s
.
D
e
f
a
u
l
t
S
q
l
S
e
s
s
i
o
n
.
u
p
d
a
t
e
(
D
e
f
a
u
l
t
S
q
l
S
e
s
s
i
o
n
.
j
a
v
a
:
148
)
a
t
o
r
g
.
a
p
a
c
h
e
.
i
b
a
t
i
s
.
s
e
s
s
i
o
n
.
d
e
f
a
u
l
t
s
.
D
e
f
a
u
l
t
S
q
l
S
e
s
s
i
o
n
.
i
n
s
e
r
t
(
D
e
f
a
u
l
t
S
q
l
S
e
s
s
i
o
n
.
j
a
v
a
:
137
)
a
t
s
u
n
.
r
e
f
l
e
c
t
.
N
a
t
i
v
e
M
e
t
h
o
d
A
c
c
e
s
s
o
r
I
m
p
l
.
i
n
v
o
k
e
0
(
N
a
t
i
v
e
M
e
t
h
o
d
)
a
t
s
u
n
.
r
e
f
l
e
c
t
.
N
a
t
i
v
e
M
e
t
h
o
d
A
c
c
e
s
s
o
r
I
m
p
l
.
i
n
v
o
k
e
(
N
a
t
i
v
e
M
e
t
h
o
d
A
c
c
e
s
s
o
r
I
m
p
l
.
j
a
v
a
:
57
)
a
t
s
u
n
.
r
e
f
l
e
c
t
.
D
e
l
e
g
a
t
i
n
g
M
e
t
h
o
d
A
c
c
e
s
s
o
r
I
m
p
l
.
i
n
v
o
k
e
(
D
e
l
e
g
a
t
i
n
g
M
e
t
h
o
d
A
c
c
e
s
s
o
r
I
m
p
l
.
j
a
v
a
:
43
)
a
t
j
a
v
a
.
l
a
n
g
.
r
e
f
l
e
c
t
.
M
e
t
h
o
d
.
i
n
v
o
k
e
(
M
e
t
h
o
d
.
j
a
v
a
:
606
)
a
t
o
r
g
.
m
y
b
a
t
i
s
.
s
p
r
i
n
g
.
S
q
l
S
e
s
s
i
o
n
T
e
m
p
l
a
t
e
Proxy72.execute(Unknown Source) at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:41) at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:66) at org.apache.ibatis.executor.ReuseExecutor.doUpdate(ReuseExecutor.java:47) at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:100) at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:148) at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:137) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.mybatis.spring.SqlSessionTemplate
Proxy72.execute(UnknownSource)atorg.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:41)atorg.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:66)atorg.apache.ibatis.executor.ReuseExecutor.doUpdate(ReuseExecutor.java:47)atorg.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:100)atorg.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:148)atorg.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:137)atsun.reflect.NativeMethodAccessorImpl.invoke0(NativeMethod)atsun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)atsun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)atjava.lang.reflect.Method.invoke(Method.java:606)atorg.mybatis.spring.SqlSessionTemplateSqlSessionInterceptor.invoke(SqlSessionTemplate.java:358)
… 18 more
检查了我所有的sql,没有发现有错误,手动在数据库软件直接insert也能成功,但是在测试类中插入数据就报错,困扰了两天,头疼。 终于在第三天,偶然发现,我的mapper.xml 文件里边,sql 的 insert 语句换行了,是下面这个样子:
但是,db2 是不支持 sql 语句换行的(可能我这个版本不支持 db2 v9.5),然后把 sql 语句调整到一行就能成功执行了。
问题解决,一身轻松啊。