System.Data.SQLite

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

<hr /><i>mistachkin added on 2011-12-25 23:31:09 UTC:</i><br />
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.

<hr /><i>anonymous added on 2011-12-26 23:23:14 UTC:</i><br />
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();


<hr /><i>mistachkin added on 2011-12-27 03:24:15 UTC:</i><br />
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.


<hr /><i>anonymous added on 2011-12-27 17:54:42 UTC:</i><br />
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.

<hr /><i>anonymous added on 2011-12-28 00:06:25 UTC:</i><br />
Why did you close this ticket ?

<hr /><i>mistachkin added on 2011-12-28 05:07:57 UTC:</i><br />
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.

<hr /><i>anonymous added on 2011-12-28 09:43:43 UTC:</i><br />
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 ?

<hr /><i>mistachkin added on 2011-12-29 01:43:17 UTC:</i><br />
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.

<hr /><i>anonymous added on 2012-01-14 00:01:19 UTC:</i><br />
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    //which 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    //which 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 ?


<hr /><i>mistachkin added on 2012-01-14 03:00:45 UTC:</i><br />
<verbatim>
Instead of:

row["change_date"] = DateTime.Now;

Try:

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


<hr /><i>anonymous added on 2012-01-14 14:27:33 UTC:</i><br />
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)'

<hr /><i>mistachkin added on 2012-01-15 01:01:22 UTC:</i><br />
Ok, that is an issue because those type conversion methods should be static as they do not need any instance data.

<hr /><i>mistachkin added on 2012-01-15 01:28:17 UTC:</i><br />
Changed to static by check-in [114024ffe7], with tests.


<hr /><i>anonymous added on 2012-01-15 19:04:07 UTC:</i><br />
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".


<hr /><i>mistachkin added on 2012-01-16 01:00:09 UTC:</i><br />
I forgot that the necessary conversions happen internally.  Here is an <a href="/index.html/artifact?ci=tip&filename=Tests/tkt-343d392b51.eagle&ln=195-297">example</a>.