Problem with <timestamp>

crash · **Joined:** Tue Nov 27, 2007 1:25 pm **Posts:** 24

Hello again,

Intro:
Table Parent with columns PK ParentId, nvarchar Name and timestamp type column RecordVersion.

Database:
Oracle

Situation:
Get existing Parent using ISession.Get method, update Parent name and call ISession.Update on Parent.

Problem:
Exception comes out, saying that Parent version is not the same as in database. In the database Parent RecordVersion column holds value "2007.12.02 18:31:02,750000000", but when update statement on Parent is executed, NHibernate puts the same RecordVersion value as parameter BUT without miliseconds, so update statement is rejected. How to solve this problem?

Regards,
Mindaugas

sergey · **Posted:** Sun Dec 02, 2007 1:57 pm

Which database are you using?

crash · **Joined:** Tue Nov 27, 2007 1:25 pm **Posts:** 24

Oracle 10g Release 2 (10.2)

sergey · **Posted:** Sun Dec 02, 2007 2:52 pm

Hmm, show your mappings and sources.

crash · **Joined:** Tue Nov 27, 2007 1:25 pm **Posts:** 24

* .NET source (C#):

Code:

Configuration cfg = new Configuration();
         cfg.AddAssembly( "SaveWithParent" );
         
         ISessionFactory factory = cfg.BuildSessionFactory();
         ISession session = factory.OpenSession();
         ITransaction transaction = null;

         try
         {
            // initialize tran
            transaction = session.BeginTransaction();

            Parent parent = (Parent)session.Get( typeof( Parent ), 3 );

            parent.Name = parent.Name + "_udp";

            session.Update( parent );

            // commit all of the changes to the DB and close the ISession
            transaction.Commit();
         }
         catch ( System.Exception exc )
         {
            if ( transaction != null )
            {
               transaction.Rollback();
            }
         }
         finally
         {
            session.Close();
         }

* Data in database before update:
PARENTID = 3
NAME = "Jonas Didysis"
RECORDVERSION = "2007.12.02 18:31:02,750000000"

* Script to create table:

Code:

CREATE TABLE TEST_PARENT (
    "PARENTID" INT NOT NULL CONSTRAINT PK__TEST_PARENT PRIMARY KEY,
   "NAME" NVARCHAR2(255) NOT NULL,
"RECORDVERSION" TIMESTAMP DEFAULT SYSTIMESTAMP
)
/

CREATE SEQUENCE SEQ__TEST_PARENT
  MINVALUE 1 
  INCREMENT BY 1 
  START WITH 1 
  CACHE 30 
  NOORDER
  NOCYCLE
/

CREATE OR REPLACE TRIGGER TR__INS_UPD__TEST_PARENT
BEFORE
  INSERT OR UPDATE
ON TEST_PARENT
REFERENCING
  NEW AS NEW 
  OLD AS OLD
FOR EACH ROW
BEGIN
    IF(INSERTING) THEN
     IF (:NEW.PARENTID IS NULL) THEN
      SELECT SEQ__TEST_PARENT.NEXTVAL INTO :NEW.PARENTID FROM DUAL;
      END IF;
    END IF;  
:NEW.RECORDVERSION := SYSTIMESTAMP; 
END;
/

* Update statement generated by NHibernate:

Code:

UPDATE TEST_PARENT SET RECORDVERSION = :p0, NAME = :p1 WHERE PARENTID = :p2 AND RECORDVERSION = :p3; :p0 = '2007.12.02 20:06:45', :p1 = 'Jonas Didysis_udp', :p2 = '3', :p3 = '2007.12.02 18:31:02'

* Mapping file:

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="SaveWithParent" assembly="SaveWithParent">

   <class name="Parent" table="TEST_PARENT" dynamic-update="true" optimistic-lock="version">
      <id name="Identity" column="PARENTID" type="Int32" unsaved-value="0">
         <generator class="sequence">
            <param name="sequence">SEQ__TEST_PARENT</param>
         </generator>
      </id>
      <timestamp column="RECORDVERSION" name="RecordVersion" unsaved-value="null"/>
      <property name="Name" column="NAME" type="String" not-null="true"/>
   </class>

</hibernate-mapping>

crash · **Joined:** Tue Nov 27, 2007 1:25 pm **Posts:** 24

Sorry, remove
unsaved-value="null"
from the mapping file.

* Exception text:
"Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect) for SaveWithParent.Parent instance with identifier: 3"

sergey · **Posted:** Sun Dec 02, 2007 3:44 pm

I believe the problem is that Oracle's TIMESTAMP type is too precise for .NET - the precision of TIMESTAMP is 1 nanosecond, but .NET is only precise up to 100 nanoseconds. Try changing the column type to TIMESTAMP(7).

crash · **Joined:** Tue Nov 27, 2007 1:25 pm **Posts:** 24

I did change to TIMESTAMP(7), but it didn't help. NHibernate actually loads miliseconds to .NET DateTime field value, but when updating entity, timestamp value is trimmed to seconds. I've heard that this is a bug in NHibernate and it will be fixed only in version 2.0. Is this true?

sergey · **Posted:** Mon Dec 03, 2007 2:36 pm

It shouldn't truncate to seconds unless you use type="DateTime" which you don't. So I'm actually at a loss here. I wouldn't pay much attention to the debug output of NHibernate since that just uses DateTime.ToString() and it might not display milliseconds even if they were there.

xasp · **Posted:** Tue Dec 04, 2007 3:53 am

Hi,

We had the very same problem. I managed to solve it by using an own OracleDialect class where I round off the timestamp precision.

Code:

    public class MyOracleDialect : NHibernate.Dialect.Oracle9Dialect
    {
        public override long TimestampResolutionInTicks
        {
            get
            {
                return 10L; // limit precision to 6 (because columns are defined as TIMESTAMP(6))
            }
        }
    }

The cause was that the resolution NHibernate used was higher than the one the database used. This meant that, if we saved an object once, kept a reference to it and tried to save it once more, NHibernate complained that the object was modified in the meantime. The actual reason was that the timestamp that was kept in memory didn't match the timestamp in the database.

Note: the timestamp columns in the database are defined as TIMESTAMP(6).

Note2 : to use the dialect, set the following property in your configuration file:

Code:

<property name="dialect">MyDataLayer.MyOracleDialect,MyDataLayer</property>

Note3: actually, I agree with Sergey that setting the column type to TIMESTAMP(7) should have solved your problem (we hadn't access to the database schema so for us that wasn't an option). Strange...

i5riza · **Joined:** Fri Dec 21, 2007 9:47 am **Posts:** 2

hi crash.

Customizing dialect will not help, because TimestampResolutionInTicks is used to calculate new value for your timestamp column. It is not related to WHERE part of the statement (where timestamp = oldStampValue).

Problem is in nhibernate NHibernate.Type.TimestampType class, which is based on DbType.DateTime. As far as it is based on DateTime, milliseconds will be ignored executing OracleCommand and no mater if command will be used from System.Data.OracleClient or Oracle.DataAccess.Client.

After some debugging and modifications, I detected that if TimestampType depends on DbType.Time (not on DbType.DateTime), then setting DbType.Time to OracleParameter class you get oracle type equal to OracleType.Timestamp as parameter type. And his is correct. Unfortunately, OracleParameter from Oracle.DataAccess and System.Data.OracleClient works differently when setting DbType.Time (one maps to OracleType.Timestamp, another to OracleType.DateTime).

So, first I updated config file to use NHibernate.Driver.OracleDataClientDriver instead of NHibernate.Driver.OracleClientDriver and added assembly Oracle.DataAccess.dll to my project. Then I made some dirty reflection modifications of hibernate TypeFactory (why not to provide legal way to modify existing types or add new types from dialect?), which is responsible for type handling. Now it works fine. Factory modification code you should execute once on your application.

Code:

      static AnyClass()
       {
          AnyClass.FixHibernateTimestampForOracle();
       }


      private static void FixHibernateTimestampForOracle()
      {
         Type dataType = typeof(TypeFactory);
         String name4typeByTypeOfName = "typeByTypeOfName";
         FieldInfo[] finfo = dataType.GetFields(BindingFlags.Static | BindingFlags.NonPublic);
         FieldInfo myInfo = null;
         for (int i = 0; myInfo == null && i < finfo.Length; i++)
         {
            FieldInfo info = finfo[i];
            if ( name4typeByTypeOfName.Equals(info.Name))
            {
               myInfo = info;
            }
         }
         if (myInfo != null)
         {
            // override hibernate timestamp
            Hashtable typeByTypeOfName = (Hashtable) myInfo.GetValue(null);
            FixedTimestampType typeTimestamp = new FixedTimestampType();
            typeByTypeOfName[typeTimestamp.Name] = typeTimestamp;
         }
         return;
      }

where new timestamp class is

Code:

   public class FixedTimestampType : TimestampType
   {
      public override SqlType SqlType
      {
         get { return SqlTypeFactory.Time; }
      }
   }

best regards,
i5riza

yy17616406 · **Joined:** Mon Dec 24, 2007 8:16 am **Posts:** 3

〖^o^〗^o^ 〖^o^〗
Shakugan no Shana
Claymore
Fate-Stay Night
Rurouni Kenshin
Highschool of the dead
naruto
One Piece
Prince of Tennis
naruto
comic
Angel Sanctuary
Slam Dunk

brendan · **Posted:** Wed Jan 30, 2008 9:37 pm

xasp wrote:

Code:

    public class MyOracleDialect : NHibernate.Dialect.Oracle9Dialect
    {
        public override long TimestampResolutionInTicks
        {
            get
            {
                return 10L; // limit precision to 6 (because columns are defined as TIMESTAMP(6))
            }
        }
    }

We've had similar issues to this as well, just reporting back that the solution xasp suggested works a treat. Thanks.

i5riza · **Joined:** Fri Dec 21, 2007 9:47 am **Posts:** 2

xasp wrote:

We've had similar issues to this as well, just reporting back that the solution xasp suggested works a treat. Thanks.

Be carefull with this solution. If your data is beging modified with other technologies, not with nhibernate, you may have problems. NHibernate trims milliseconds (this is why it works) on save, but other techs may not do this (manual modifications, triggers etc)

sergey, am i wrong?

i5riza