Skip to main content

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

Comments

Siva said…
Very useful tip!
Digit said…
I 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
Jobinesh said…
Well, makes sense.

Popular posts from this blog

How to set Bind Variable Values at runtime ?

In this post I'm sharing a couple of approaches for programmatically setting bind variables values at run time. This post is an attempt to explain 'When to use what ?'[ In case if you are familiar with 'Bind Variables' in ADF BC, please refer Section 5.10, Working with Bind Variables in Fusion Developer's Guide ]

1. Set the Bind Variable value using RowSet::setNamedWhereClauseParam(...)

You can use use the setNamedWhereClauseParam(...) method on the ViewObject interface (which extends oracle.jbo.RowSet) to set the value for bind variables. Please note this sets the value on default RowSet. In other words, this doesn't have any effect on the secondary RowSets that you/system generates.
ViewObject vo = am.findViewObject("EmployeesView1"); vo.setNamedWhereClauseParam("bindVarDeptId", new Number(10)); vo.executeQuery();
2. Set the Bind Variable value using ViewObject's VariableValueManager::setVariableValue(...)

VariableValueManager Ma…

Happy New Year 2018 !

We can't go back and change the beginning, but we always can start where we are and change the ending. Believe in yourself and you will be unstoppable!

Wishing you and your family a very happy new year 2018 !!!