Error when opening android gem database

[wshelley]

System.Data.DBConcurrencyException: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records

Hi Julian,

There is a small problem with the way dates are being populated in the SQLite database. The Android tool and Windows tool appear to do it in different ways. This is particularly a problem with the field PROJ_DATE in the table GEM_PROJECT. Currently as things stand it is not possible to open an Android version of the database using the Windows Tool. If the PROJ_DATE field is modified outside the IDC Tool then everything works as expected.

SQLite does not have a specific Date type and stores dates as strings behind the scenes. I think this is the root of the problem.

Here are a few tests I did using SQL to populate the database with different datetime formats.

UPDATE GEM_PROJECT SET PROJ_DATE='2013-03-03'; This one causes the Windows tool to fail (not interpreted as a true date) UPDATE GEM_PROJECT SET PROJ_DATE='2013-03-03 01:25:23'; This is ok and works fine (probably not the best way as we are dealing with Date data types) UPDATE GEM_PROJECT SET PROJ_DATE=datetime('2013-03-03'); This is ok and works fine UPDATE GEM_PROJECT SET PROJ_DATE= datetime('2013-03-03 01:25:23'); This is ok and works fine

So it looks like we need to be careful if we populate Date fields using a string, probably best to convert to a true date using a datetime function.

Can you please let us know how you are populating the PROJ_DATE field, are you using a string or are you using a datetime function to convert? Also how easy would it be for you to change this behaviour in your App?

Cheers
Keith