org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO TABLE1 (COL1, COL2, COL3 VALUES (?, ?, ?)]; DB2 SQL error: SQLCODE: -181, SQLSTATE: 22007, SQLERRMC: null; nested exception is com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -181, SQLSTATE: 22007, SQLERRMC: null

Spring JIRA | Jaime Sanchez | 8 years ago
  1. 0

    I have applications working with Spring 2.5.1 and DB2. My queries use BeanPropertySqlParameterSource and when I changed to Spring upper version, I got the following error: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO TABLE1 (COL1, COL2, COL3 VALUES (?, ?, ?)]; DB2 SQL error: SQLCODE: -181, SQLSTATE: 22007, SQLERRMC: null; nested exception is com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -181, SQLSTATE: 22007, SQLERRMC: null at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:100) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:607) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:792) ... Caused by: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -181, SQLSTATE: 22007, SQLERRMC: null at com.ibm.db2.jcc.a.jd.d(jd.java:1397) at com.ibm.db2.jcc.b.fb.l(fb.java:366) at com.ibm.db2.jcc.b.fb.a(fb.java:64) at com.ibm.db2.jcc.b.s.a(s.java:48) ... This error appears when I map a java.util.Date property with a Date type column. If I don't define specifically the java.util.Date properties like Date type columns by registerSqlType method, BeanPropertySqlParameterSource defines the java.util.Date properties like TimeStamp type columns. This sql type (TimeStamp) doen's work with DB2 Date columns. I have tested if the error happens when I work with another database, for example with MySQL. In this case BeanPropertySqlParameterSource works properly without registering the sql type. This behaviour appears from Spring 2.5.2. I can't update to uppper version, because I would modify all the applications which use BeanPropertySqlParameterSource and register the Date sql type in the queries. Have I got a chance to update the Spring version without modify my applications?

    Spring JIRA | 8 years ago | Jaime Sanchez
    org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO TABLE1 (COL1, COL2, COL3 VALUES (?, ?, ?)]; DB2 SQL error: SQLCODE: -181, SQLSTATE: 22007, SQLERRMC: null; nested exception is com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -181, SQLSTATE: 22007, SQLERRMC: null
  2. 0

    I have applications working with Spring 2.5.1 and DB2. My queries use BeanPropertySqlParameterSource and when I changed to Spring upper version, I got the following error: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO TABLE1 (COL1, COL2, COL3 VALUES (?, ?, ?)]; DB2 SQL error: SQLCODE: -181, SQLSTATE: 22007, SQLERRMC: null; nested exception is com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -181, SQLSTATE: 22007, SQLERRMC: null at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:100) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:607) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:792) ... Caused by: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -181, SQLSTATE: 22007, SQLERRMC: null at com.ibm.db2.jcc.a.jd.d(jd.java:1397) at com.ibm.db2.jcc.b.fb.l(fb.java:366) at com.ibm.db2.jcc.b.fb.a(fb.java:64) at com.ibm.db2.jcc.b.s.a(s.java:48) ... This error appears when I map a java.util.Date property with a Date type column. If I don't define specifically the java.util.Date properties like Date type columns by registerSqlType method, BeanPropertySqlParameterSource defines the java.util.Date properties like TimeStamp type columns. This sql type (TimeStamp) doen's work with DB2 Date columns. I have tested if the error happens when I work with another database, for example with MySQL. In this case BeanPropertySqlParameterSource works properly without registering the sql type. This behaviour appears from Spring 2.5.2. I can't update to uppper version, because I would modify all the applications which use BeanPropertySqlParameterSource and register the Date sql type in the queries. Have I got a chance to update the Spring version without modify my applications?

    Spring JIRA | 8 years ago | Jaime Sanchez
    org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO TABLE1 (COL1, COL2, COL3 VALUES (?, ?, ?)]; DB2 SQL error: SQLCODE: -181, SQLSTATE: 22007, SQLERRMC: null; nested exception is com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -181, SQLSTATE: 22007, SQLERRMC: null
  3. 0

    error during registration/inital load

    symmetricds | 7 years ago | psi_teck
    org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL ; Column 'CHANNEL_ID' already exists in Table/View '"SMS"."SYM_INCOMING_BATCH"'.; nested exception is java.sql.SQLException: Column 'CHANNEL_ID' already exists in Table/View '"SMS"."SYM_INCOMING_BATCH"'.
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    exception syncing with sync_on_incoming_batch

    symmetricds | 8 years ago | psi_teck
    org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallb ck; SQL [insert into TOPROCKALBUMS("ALBUMID","RANK","BAND","ALBUM","YEARRELEASE ") values (?,?,?,?,?)]; The statement was aborted because it would have caused duplicate key value in a unique or primary key constraint or unique index iden ified by 'UNIQUE_RANK' defined on 'TOPROCKALBUMS'.; nested exception is java.sq .SQLException: The statement was aborted because it would have caused a duplica e key value in a unique or primary key constraint or unique index identified by 'UNIQUE_RANK' defined on 'TOPROCKALBUMS'.
  6. 0

    When Spring batch is connected to DB2 z os , it has issues when trying to read the sequences created from the db2.schema given in the spring batch. My Configuration file is as below. {code} <bean id="jobRepository" class="org.springframework.batch.core.repository.support.JobRepositoryFactoryBean"> <property name="dataSource" ref="dataSource" /> <property name="transactionManager" ref="transactionManager" /> <property name="databaseType" value="DB2ZOS" /> </bean> {code} All the metadata tables that sprin gbatch uses to loge job instances and step details are created in DB2 z os and also the three sequences. All these schema are taken from the db2.schema. The error what i am getting is. {noformat} org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT into BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION) values (?, ?, ?, ?)]; DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505, SQLERRMC=IVA204X1;0000000202, DRIVER=3.53.70; nested exception is com.ibm.db2.jcc.b.zn: DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505, SQLERRMC=IVA204X1;0000000202, DRIVER=3.53.70 at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:228) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:607) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:792) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:850) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:854) at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.createJobInstance(JdbcJobInstanceDao.java:117) at org.springframework.batch.core.repository.support.SimpleJobRepository.createJobExecution(SimpleJobRepository.java:131) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:64) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) {noformat} Does it mean that its not able to make call to the sequences created in DB2 z os ?

    Spring JIRA | 5 years ago | Hema Math
    org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT into BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION) values (?, ?, ?, ?)]; DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505, SQLERRMC=IVA204X1;0000000202, DRIVER=3.53.70; nested exception is com.ibm.db2.jcc.b.zn: DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505, SQLERRMC=IVA204X1;0000000202, DRIVER=3.53.70

    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. org.springframework.dao.DataIntegrityViolationException

      PreparedStatementCallback; SQL [INSERT INTO TABLE1 (COL1, COL2, COL3 VALUES (?, ?, ?)]; DB2 SQL error: SQLCODE: -181, SQLSTATE: 22007, SQLERRMC: null; nested exception is com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -181, SQLSTATE: 22007, SQLERRMC: null

      at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate()
    2. Spring Framework
      JdbcTemplate.update
      1. org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:100)
      2. org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
      3. org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
      4. org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:607)
      5. org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:792)
      5 frames