What you may need to know about data types for history columns which stores date and time?
When you choose history columns data types to store 'Date', please follow the following points:
1. Always prefer java.sql.Timestamp (or similar) over java.sql.Date for history column attribute type in an entity:
Well,apart from the common benefits of using Timestamp over Date, there is one more point. The java.sql.Date will truncate the value(removing time comp) when the data is posted to data base. At this stage the database table is having truncated value and entity object has non truncated date. If the user again modifies the same row on the same date, the row consistence check may fail while posting data to database and you may get the below error
oracle.jbo.RowInconsistentException: JBO-25014: Another user has changed the row with primary key...
2. Make sure corresponding history column in database table is of type TIMESTAMP
DATE contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. TIMESTAMP stores date and time as small as the nanosecond and has the capability to track the updates happens on the same records within same second (by single/multiple users).
To learn more about history columns, please refer the following topic in Fusion Developers Guide
How to Track Created and Modified Dates Using the History Column
1. Always prefer java.sql.Timestamp (or similar) over java.sql.Date for history column attribute type in an entity:
Well,apart from the common benefits of using Timestamp over Date, there is one more point. The java.sql.Date will truncate the value(removing time comp) when the data is posted to data base. At this stage the database table is having truncated value and entity object has non truncated date. If the user again modifies the same row on the same date, the row consistence check may fail while posting data to database and you may get the below error
oracle.jbo.RowInconsistentException: JBO-25014: Another user has changed the row with primary key...
2. Make sure corresponding history column in database table is of type TIMESTAMP
DATE contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. TIMESTAMP stores date and time as small as the nanosecond and has the capability to track the updates happens on the same records within same second (by single/multiple users).
To learn more about history columns, please refer the following topic in Fusion Developers Guide
How to Track Created and Modified Dates Using the History Column
Very useful tip!
ReplyDeleteI thank you for sharing this article, but I think what you said is not completely correct: i mean, the reason behind a RowInconsistentException raised after the update of an history column value is not related to the use of a java.sql.Date type instead of java.sql.Timestamp (or similar), but to a mismatch between the fractional precision specified for the database column and the fractional precision used by the java type which maps that column. Let's say, for example, you defined a table with a timestamp column having fractional precision set to 0. Even if ADF, by default, maps this column to an entity attribute with type oracle.jbo.domain.Timestamp, the RowInconsistentException can still be raised very easily by the framework, 'cos the java type can have a fractional precision greater than 0 and, in that case, the comparison between the value in memory and that posted to db fails. I can assert all of the above simply 'cos i stumbled in such a situation. In my case, i solved the issue by increasing the fractional precision of the db column to the default of 6. Thanks, bye! Fabio
ReplyDeleteWell, makes sense.
ReplyDelete