UpdateCheck problem for DATETIME column

[dilbert]

I experience similar problem to the one described in this thread:
http://www.devart.com/forums/viewtopic. ... w++changed

The problem concerns SQLite version 3.x of dotConnect (older versions 2.x worked correctly).

It could be reproduced by the following steps:

1) create simple table with DATETIME column, e.g.:

Code: Select all

CREATE TABLE [table_test] (
  [id] INT NOT NULL, 
  [date_update] DATETIME NOT NULL, 
  CONSTRAINT [] PRIMARY KEY ([id]));

2) insert a record into table NOT by datacontext, use e.g. any console for SqLite:

Code: Select all

insert into table_test (id, date_update) VALUES (1, '2011-03-03 17:48:10');

3) Running following code:

Code: Select all

TableTest test = DB.TableTests.Where(tt => tt.Id == 1).Single();
test.Date_update = DateTime.Now;
DB.SubmitChanges();

...results in following exception:

Code: Select all

Message: "Row not found or changed."
at Devart.Data.Linq.y.a(DataContext A_0, ConflictMode A_1)
at Devart.Data.Linq.y.b(DataContext A_0, ConflictMode A_1)
at Devart.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
at Devart.Data.Linq.DataContext.SubmitChanges()
...

2b) By the way, if you insert a record with datacontext:

Code: Select all

TableTest test = new TableTest();
test.Id = 2;
test.Date_update = DateTime.Now;
DB.TableTests.InsertOnSubmit(test);
DB.SubmitChanges();

then updating code in (3) is executed properly without any exception. So, I looked for difference between values inserted manually and by datacontext:

The difference is probably in miliseconds.


My conclusion -> A workaround for this problem is to set UpdateCheck=Never for each DATETIME entity. However, I think it shall be resolved either by modifying update query or setting UpdateCheck property to 'Never' by default for DATETIME entities.

Thanks for your attenttion in advance.

[StanislavK]

Thank you for the report. We will investigate this issue and post here about the results as soon as possible.

[StanislavK]

We have analyzed the issue. It is actually caused by the way SQLite works with dates: all dates are compared as strings, hence '2011-03-03 17:48:10.00000' (the DateTime value sent by LinqConnect) will not be equal to '2011-03-03 17:48:10' (which was inserted by another tool).

We've implemented a workaround for this issue: now update checks for DateTime columns will include explicit conversions to the SQLite datetime type, e.g.,

Code: Select all

UPDATE table_test SET date_update = :p1 WHERE id = :key1 AND datetime(date_update) = datetime(:chk1)

These changes will be available in the nearest build, which we plan to release in several days.

[StanislavK]

We have released the new 3.10.135 version of dotConnect for SQLite where this workaround is implemented. The new build can be downloaded from
http://www.devart.com/dotconnect/sqlite/download.html
(the trial version) or from Registered Users' Area (for users with active subscription only):
http://secure.devart.com/

For more information about the fixes and improvements available in dotConnect for SQLite 3.10.135, please refer to
http://www.devart.com/forums/viewtopic.php?t=20710