Trigger on Timestamp column issues (with LINQ)

LordFjord · Post by **LordFjord** » Thu 10 Jun 2010 09:38

Hello,
I have a table with a TIMESTAMP(3) column that represents the last modified date of the object as UTC time without offsets. The defaut value is SYS_EXTRACT_UTC(SYSTIMESTAMP).
I have set up a trigger to automate this, on the sql level it works fine.

When I read the object via LINQ, modify it and commit the changes, I get a "System.ArgumentOutOfRangeException: Year, Month, and Day parameters describe an un-representable DateTime." exception.

Some additional info:
I am using 5.70.140.0 dotConnect for oracle (professonal edition).

The column definition:

Code: Select all

CREATE TABLE "MYUSER"."MYTABLE"
  (
...
LASTMODIFIEDDATE" TIMESTAMP (3) DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL ENABLE,
...

The trigger definition:

Code: Select all

create or replace
TRIGGER "MYUSER"."TRIGGERNAME" BEFORE
  UPDATE OF  ON MYUSER.MYTABLE
FOR EACH ROW
BEGIN
	:NEW.LASTMODIFIEDDATE := SYS_EXTRACT_UTC(SYSTIMESTAMP);
END;

The C# code to fetch the data, modify it and write it back:

Code: Select all

...
MyObject myObject = context.MyTables.SingleOrDefault(x => x.ID == id);
if ( myObject != null )
{
  myObject.SomeValue = value;
}
context.SubmitChanges();

The LastModifiedDate column's mapping:

Code: Select all

[Column(Name = @"LASTMODIFIEDDATE", Storage = "_LastModifiedDate", CanBeNull = false, DbType = "TIMESTAMP(3) NOT NULL", IsDbGenerated = true, UpdateCheck = UpdateCheck.Never)]

The stacktrace:

Code: Select all

Devart.Data.Linq.LinqCommandExecutionException: Error on executing DbCommand. ---> System.ArgumentOutOfRangeException: Year, Month, and Day parameters describe an un-representable DateTime.   at System.DateTime.DateToTicks(Int32 year, Int32 month, Int32 day)
   at Devart.Data.Oracle.OracleTimeStamp.get_Value()
   at Devart.Data.Oracle.OracleParameter.a(OracleDbType A_0, Object A_1, Type& A_2, Object& A_3, Byte[] A_4, Hashtable A_5, Int32 A_6, Int32 A_7, Int32 A_8, Int32 A_9, Boolean A_10, Boolean A_11, OracleCommand A_12, ParameterDirection A_13, av A_14, String A_15)
   at Devart.Data.Oracle.OracleParameter.a(aq& A_0, Boolean A_1, Boolean A_2, OracleCommand A_3, Byte[] A_4, Hashtable A_5, av A_6)
   at Devart.Data.Oracle.OracleCommand.a(OracleParameterCollection A_0, aq[] A_1, bt A_2, av A_3)
   at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at Devart.Data.Linq.Provider.DataProvider.ExecuteQuery(String commandText, List`1 resultsetParameters, IList`1 parameters, Boolean isBatch, IDbCommand& dbCommand)
   --- End of inner exception stack trace ---
   at Devart.Data.Linq.LinqCommandExecutionException.a(String A_0, Exception A_1)
   at Devart.Data.Linq.Provider.DataProvider.ExecuteQuery(String commandText, List`1 resultsetParameters, IList`1 parameters, Boolean isBatch, IDbCommand& dbCommand)
   at Devart.Data.Linq.h.a(SubmitCommand A_0, SubmitCommandBuilder A_1)
   at Devart.Data.Linq.h.a(SubmitedObject A_0, SubmitCommand A_1, SubmitCommandBuilder A_2)
   at Devart.Data.Linq.r.a(SubmitedObject A_0, SubmitCommand A_1, SubmitCommandBuilder A_2)
   at Devart.Data.Linq.s.a(m A_0, af A_1, Object A_2, Boolean A_3)
   at Devart.Data.Linq.aa.a(s A_0, af A_1, Boolean A_2)
   at Devart.Data.Linq.aa.a(DataContext A_0, ConflictMode A_1)
   at Devart.Data.Linq.aa.b(DataContext A_0, ConflictMode A_1)
   at Devart.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
   at Devart.Data.Linq.DataContext.SubmitChanges()

Is there a known issue with timestamps/triggers/LINQ?

LordFjord · Post by **LordFjord** » Thu 10 Jun 2010 10:08

A few more things:
The exception has definately something to do with the trigger. If i disable the trigger, the commands run without errors (but, well, my lastmodifieddate isnt updated).

AndreyR · Post by **AndreyR** » Fri 11 Jun 2010 13:36

Thank you for the report, I have reproduced the problem.
We will notify you about the results of our investigation.

AndreyR · Post by **AndreyR** » Thu 08 Jul 2010 14:52

We have made a test - this trigger makes a corrupted value in column in case of executing the SQL with RETURNING clause and this trigger enabled.
You can try to fix this issue in the new 5.70.146 build of dotConnect for Oracle. Set the Devart.Data.Oracle.Linq.Provider.OracleDataProvider.UseReturningClause property to true.