DBConcurrencyException

This issue is related to the closed ticket [343d392b51]
In fact the problem still remains. Please read the old ticket first.

As a workaround I now do not use adapter.Update() any longer (as it raises the exception).
Instead of

String  table = “table”;
try {
sql = "select * from table where ...";
da = new SQLiteDataAdapter(sql, db_con);
da.Fill(ds, table);
dt = ds.Tables[table];
…
manipulate_data();
int i = da.Update(dt); here the DBConcurrencyException raises !!!

I now use a stupid sql.command in a loop.
…
String sql = String.Format("update table set … “

SQLiteCommand cmd = new SQLiteCommand(sql, dbconnection);
return cmd.ExecuteNonQuery();
…

This works, but it does not perform well and it is very ugly code.
________________________________

The DBConcurrencyException raises not on every adapter.Update() statement. Sometimes it works, sometimes not. Sometimes a few records are updated, sometimes just only one, sometimes none. Sometimes it raises on this table, sometimes on the other table.
All my tables have as a first column a DateTime column (“CHANGE_DATE”).
It does not matter whether I do
row[“CHANGE_DATE”] = DateTime.Now;
Or
row["CHANGE_DATE"] = DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day + " " + DateTime.Now.TimeOfDay;

regards,
Simon

Are you using DateTimeFormat=JulianDay or UnixEpoch in the connection string?  This is necessary to take advantage of the fix for the previous ticket you mentioned.

If I use > DateTimeFormat=JulianDay < in the connectionstring the statement

  xydatetime = reader.GetDateTime(0);

raises an exception:

TypeInitialisationException:  wrong format
_______

String sql = "select CHANGE_DATE, ...     from TABLE .. ";

SQLiteCommand cmd = new SQLiteCommand(sql, dbconnection);

SQLiteDataReader reader = cmd.ExecuteReader();

 if (reader.HasRows)
     while (reader.Read())
     {
       ...
       xydatetime = reader.GetDateTime(0);
       ...

       }

  reader.Close();


When you use the JulianDay format, the type conversion code assumes that database fields with the type DATETIME (or DATE, etc) will actually be a floating point number.  Likewise, when you use the UnixEpoch format, it assumes that database fields with the type DATETIME will actually be an integer number of seconds since the Unix epoch.


connection string contains > DateTimeFormat=JulianDay <

the following code raises System.FormatException:

String Sql = "Select CHANGE_DATE, … from table ..”;

da = new SQLiteDataAdapter(sql, dbconnection);

da.Fill(dset, table);

The column CHANGE_DATE is of type DATETIME.

Why did you close this ticket ?

Because the behavior you appear to describe is not a bug.  When using the UnixEpoch or JulianDay formats in the connection string, the underlying data in the database itself must be of the type expected by the type conversion code in System.Data.SQLite.  For UnixEpoch, it must be an integer.  For JulianDay, it must be a double-precision floating-point number.

Does that mean that I can't use columns of type DatTime ?
Just to use the DateTimeFormat=JulianDay does not convert the underlying data in the database. Right ? Do I have to convert the data in the database first before working on it ?

Effectively, yes.  The DateTimeFormat property of the connection string controls how System.Data.SQLite attempts to *interpret* the actual data.  If you want to use JulianDay or UnixEpoch (or Ticks), the actual data in the database must be changed first.  Alternatively, you can use ISO8601 if your DateTime strings match one of the supported format strings.

this issue is drivin me nuts

I tried:

first version

connectionstring:   DateTimeFormat= JulianDay

table product:

change_date  datetime,
price number,
name varchar2,
id integer

firstly, I did:

update product set change_date = 2455928.0    is 2012-01-01 12:00:00

then:

sql = “select change_date, id, name, price from product”;

adapter =  new SQLiteDataAdapter(sql, db_con);

adapter.Fill(ds, datatab);

….

row[“price”]  += 1;

row[“change_date”]  =  DateTime.Now;

….

adapter.update(datatab);     <- here the DBConcurrencyException raises

_______________________________________________

second version:

same table;

connectionString:     DateTimeFormat= JulianDay

update product set change_date = 2455928.0    is 2012-01-01 12:00:00

then

!! difference !!

sql = “select julianday(change_date) as change_date, id, name, price from product”;

adapter =  new SQLiteDataAdapter(sql, db_con);

adapter.Fill(ds, datatab);

….

row[“price”]  += 1.0;

in fact now the column is of type System.Double

row[“change_date”]  =  (double)row[“change_date”]  + 1.0;

….

adapter.update(datatab);

no exception raises, but what happens is:

the price column is updated, the DateTime-Column is not  !

What's wrong ?


<verbatim>
Instead of:

row["change_date"] = DateTime.Now;

Try:

row["change_date"] = SQLiteConvert.ToJulianDay(DateTime.Now);
</verbatim>


row["change_date"] = SQLiteConvert.ToJulianDay(DateTime.Now);

=> compiler says:

An object reference is required for the non-static field, method, or property 'System.Data.SQLite.SQLiteConvert.ToJulianDay(System.DateTime)'

Ok, that is an issue because those type conversion methods should be static as they do not need any instance data.

Changed to static by check-in [114024ffe7], with tests.


connectionstring: DateTimeFormat= JulianDay

update product set change_date = 2455928.0    which is 2012-01-01 12:00:00

change_date is of type DateTime !

----------------

sql = “select change_date, price, ....”;

adapter = new SQLiteDataAdapter(sql, db_con);

adapter.Fill(ds, datatab);

….

row[[price]] += 1;

row[[change_date]] = SQLiteConvert.ToJulianDay(DateTime.Now);      <-

TypeConverionException raises,

which is clear, because:

the original SQLite table-column is of type DateTime,
then the ADO-table is also of type System.DateTime, to which we want to assign a double.

System.ArgumentException: invalid type conversion "Double" to "DateTime".<2455941,93204139> cannot be saved to CHANGE_DATE column.
Expected type: DateTime. ---> System.InvalidCastException: invalid conversion "Double" to "DateTime".


I forgot that the necessary conversions happen internally.  Here is an <a Basically, this means the statement should actually be something like:
<verbatim>row["change_date"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.FFFFFFFK");</verbatim>