how to check which column cause db2 22001 error?

Question

Good day,

org.springframework.dao.DataIntegrityViolationException: could not insert: [com.entity.payment.BPaymentItem]; SQL [insert into sc.PHistoryItem (amount, beneficiaryAccNo, beneficiaryBankCode, beneficiaryBankName, beneficiaryRef2, billCode, branchId, chargeTo, cibRefNo, cifId, collectionAccountNo, companyId, companyName, createdHostDateTime, currentStatus, customerCategoryCharges, customerCategoryCode, customerCategoryId, customerCategoryName, description, errorCode, errorMessage, fromAccountName, fromAccountNo, fromAccountType, fromFavourite, gst, gstAmount, gstInclusive, gstName, gstRate, gstRefNo, gstTaxCode, hostRefNo, nbpsBillerCode, nbpsBillerName, nbpsReferenceNo, nbpsRrn2, nbpsRtn, nbpsSystemReferenceNo, notifyRetryCount, paymentDate, paymentHistoryId, processDate, promotionCharges, promotionCode, promotionId, promotionName, ref1, ref2, reference, rejectReason, remittanceStatus, remittanceTo, rfi, rrnInfo, rtTrxCode, rtnStatus, serviceCharge, serviceChargeCode, serviceChargeGLAcct, serviceChargeId, serviceChargeName, servicePackageCode, servicePackageId, servicePackageName, serviceType, showTerm, smsCharges, smsGst, smsGstAmount, smsGstInclusive, smsGstRate, smsGstTaxCode, toAccountName, toAccountNo, toAccountType, transferType, type, validateSig, approvalItemId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; nested exception is org.hibernate.exception.DataException: could not insert: [com.entity.payment.BPaymentItem]
    at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:642) ~[org.springframework.orm-3.0.4.RELEASE.jar:3.0.4.RELEASE]


Caused by: com.ibm.db2.jcc.am.SqlDataException: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null, DRIVER=3.63.123
    at com.ibm.db2.jcc.am.fd.a(fd.java:671) ~[db2jcc.jar:na]
...

SQLSTATE 22001 is causing by Character data, right truncation occurred; for example, an update or insert value is a string that is too long for the column, or a datetime value cannot be assigned to a host variable, because it is too small.

But is there a way to check which column causing this error?

Answer 1

There is no way from this error message to determine the column which is causing your problem. But based on this error: SQLERRMC=null. Your data is trying to insert a null value, either your column doesn't accept nulls or your not passing the right type of null.

If you're using a prepared statement like this, then to insert a null you would need to specify it.

String query = "insert into table_temp(col1,col2) values(?,?)";
pstmt = con.prepareStatement(query);
pstmt.setString(1, "a");
pstmt.setNull(2, java.sql.Types.NULL);