Friday, October 19, 2012

Issue with DBAdapter Update due to char datatype & shouldTrimStrings property


Recently I looked into a technical issue with DB adapter where the update was not working even though it was not throwing any error. Everything seems to be working fine but the field is never updated.

All the settings looked fine but the update was not happening. So we checked the table which revealed that the all the string fields were of char type. It didn’t seem to value much as my understanding was that char will work similar to varchar. I was puzzled to see the Note from Oracle @Oracle DBAdapter documentation ,but it was true.

It reads as :: Oracle recommends that you use varchar instead of char for primary key columns, otherwise you must set the weblogic-ra.xml property shouldTrimStrings to false. The truncation of trailing spaces could cause the primary key to be read incorrectly, making it impossible to update read rows as processed.

Issue :: The Primary Key fields in Database were of char type and data contains trailing spaces. Since by default shouldTrimStrings property is set to true, weblogic will trim these spaces, there by it will not be able to fetch the correct rows from DB. So update fails as it will not be able to fetch the row properly.
Solution::
Set the shouldTrimStrings property to false in weblogic DBAdapter Outbound Connection Properties
By default the property shouldTrimStrings is not visible in Outbound Connection Properties, so we will not able to change it.
So to modify the value a new entry needs to added to the DBAdapter config  properties. This is one time configuration task that needs to done by updating the ra.xml from filesystem.
Steps
1.    Take a backup of DBAdapter.rar
2.     Extract the entire content.(use unzip tools)
3.     Edit the META-INF/ra.xml.
Add a property in ra.xml file
           <config-property>
               <config-property-name>shouldTrimStrings</config-property-name>
               <config-property-type>java.lang.Boolean</config-property-type>
               <config-property-value>false</config-property-value>
            </config-property>
   
4.    Re-Create the DbAdapter  rar file.
>>>>jar.exe -cvf DbAdapter.rar -C DbAdapter/ .
added manifest
adding: DBAdapter.jar(in = 551405) (out= 519781)(deflated 5%)
ignoring entry META-INF/
ignoring entry META-INF/MANIFEST.MF
adding: META-INF/ra.xml(in = 13473) (out= 2866)(deflated 78%)
adding: META-INF/weblogic-ra.xml(in = 6713) (out= 1775)(deflated 73%)
adding: toplink-grid.jar(in = 102529) (out= 91349)(deflated 10%)
5.    Update the DBAdapter from Deployments
From Console the property should be available for all the database adapter connection under Outbound Connection Properties.

Thanks to Thani, Lee, Sampath for helping with this issue.

No comments: