SA-1766

Summary

When Pega User adds a record in history (pyMemo column), and if the message contains a character like '(hyphen) ,(comma) -(dash) etc, then those characters get converted to ’. As one character changes to 3 characters, sometimes the comments go beyond the DB column length of 255 characters and causes DB error.

Error Messages

014-09-22 15:48:38,655 [ WebContainer : 10] (WithErrorHandling.Work_.Action) ERROR - Error in commit()
com.pega.pegarules.pub.database.DatabaseException: Database-Saver-Insertfail
From: (H850748CF699234E6183D6CEBBD6F7C47)
SQL: insert into fulfillment_pc_history_work (PXADDEDBYID , PXADDEDBYSYSTEM , PXASSIGNMENTELAPSEDTIME , PXASSIGNMENTPASTDEADLINE , PXASSIGNMENTPASTGOAL , PXCOVERINSKEY , PXEFFORTACTUALDELTA , PXFLOWACTION , PXHISTORYFORREFERENCE , PXINSNAME , PXOBJCLASS , PXRESOLUTIONCOSTDELTA , PXTASKELAPSEDTIME , PXTIMECREATED , PXTIMEFLOWSTARTED , PYASSIGNEDTO , PYASSIGNEDTOWORKGROUP , PYASSIGNMENTCLASS , PYFLOWKEY , PYFLOWNAME , PYFLOWTYPE , PYHISTORYTYPE , PYLABEL , PYMEMO , PYMESSAGEKEY , PYPERFORMACTIONTIME , PYPERFORMASSIGNMENTTIME , PYPERFORMER , PYPERFORMTASKTIME , PYSUBSTITUTIONVALUES , PYTASKID , PYTASKNAME , PYWORKCLASS , pzInsKey , pzPVStream) values (? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?)
SQL Inserts: <ABC357755> <pega> <<null>> <<null>> <<null>> <<null>> <<null>> <<null>> < DL-6053455> < DL-6053455!20140922T194838.115 GMT> <History-Work> <<null>> <<null>> <2014-09-22 15:48:38.115> <<null>> <<null>> <<null>> <<null>> <<null>> <<null>> <<null>> <<null>> <<null>> <Doc status changed from 'Questions' to 'Answer' > < AnyMessage Doc Status Changed for document > <<null>> <<null>> <test> <<null>> <<null>> <<null>> <<null>> <<null>> < DL-6053455!20140922T194838.115 GMT> <<stream>>

Caused by SQL Problems.
Problem #1, SQLState 22001, Error code -302: com.ibm.db2.jcc.b.xn: The value of a host variable in the EXECUTE or OPEN statement is out of range for its corresponding use.. SQLCODE=-302, SQLSTATE=22001, DRIVER=3.53.70
at com.pega.pegarules.engine.database.ExceptionInformation.createExceptionDueToDBFailure(ExceptionInformation.java:174)
at com.pega.pegarules.engine.database.DatabasePreparedStatement.addBatch(DatabasePreparedStatement.java:506)
at com.pega.pegarules.engine.database.Saver.saveInstanceWithNoDeletedColumnOnlyIfNew(Saver.java:1052)
at com.pega.pegarules.engine.database.Saver.saveInstance(Saver.java:692)
at com.pega.pegarules.engine.database.DatabaseImpl.performOps(DatabaseImpl.java:2160)
at com.pega.pegarules.engine.database.DatabaseImpl.attemptToProcessUpdates(DatabaseImpl.java:1574)
at com.pega.pegarules.engine.database.DatabaseImpl.processUpdates(DatabaseImpl.java:1471)
at com.pega.pegarules.engine.database.DatabaseImpl.commit(DatabaseImpl.java:1229)
..
Caused by:
com.ibm.db2.jcc.b.xn: The value of a host variable in the EXECUTE or OPEN statement is out of range for its corresponding use.. SQLCODE=-302, SQLSTATE=22001, DRIVER=3.53.70
..

Root Cause

 fulfillment_pc_history_work  table has column length limit to 255. Sometimes when comments are going beyond the DB column length limit of 255 following exception is thrown from DB2:

SQL0302N  The value of a host variable in the EXECUTE or OPEN 
statement is too large for its corresponding use.

Table defination is used to determin the column lenght

Resolution

 
Increase the appropriate column length limit to a greater value to resolve the problem. Check the column type and length of the value or the data type and contents of the input host variable or parameter position-number. Ensure that the value of the host variable or parameter will fit in the column or contains valid decimal data.