System.Data.SQLite

I have a main database with an attched database and I don't use any threads by my self.

my simple code:

... try { sql = "select * from xy.table where ..."; da = new SQLiteDataAdapter(sql, db_con);

da.Fill(ds, tab);

dt = ds.Tables[tab];

// Indicate DataColumn ID is unique // This is required by the CommandBuilder to update the table dt.Columns["ID"].Unique = true; // and set the primery key // is needed for the .Find-Command DataColumn[] PrimaryKeys = new DataColumn[1]; PrimaryKeys[0] = dt.Columns["ID"]; dt.PrimaryKey = PrimaryKeys; SQLiteCommandBuilder builder = new SQLiteCommandBuilder(da);

manipulate_data();

// da.UpdateBatchSize = 100; not supported at this time

int i = da.Update(dt); // here the DBConcurrencyException raises !!!

After updating round about 150 rows the exception raises. If the table has just one row the exception raises immediately.

If I move the table to the main database, these problems do not appear.

regards, Simon

mistachkin added on 2011-09-27 22:51:45 UTC:
1.0.66.0 is no longer supported as it was released in April 2010. Please try using a newer version (such as the NuGet package, which contains statically linked binaries) and let me know if you still see the error in question.

mistachkin added on 2011-09-28 07:40:55 UTC:
Possibly related to your problem: http://sqlite.phxsoftware.com/forums/p/1103/4711.aspx

mistachkin added on 2011-09-29 06:14:36 UTC:
I have been unable to reproduce this issue with either 1.0.66.0 or the latest version. I suspect that I'm missing key information about the nature of the perceived problem here. For example, what does the "manipulate_data" function do?

anonymous added on 2011-10-05 20:59:48 UTC:
I upgraded to 1.0.76.0 but the DBConcurrencyException still remains

the manipulate_data function does change the contents of a single column (for one row)

mistachkin added on 2011-10-06 05:34:35 UTC:
Is it possible for you to supply any additional information about your environment or the specific circumstances of the issue you are seeing? So far, I am not able to reproduce it.

mistachkin added on 2011-10-06 05:40:28 UTC:
The current test case I have going is here: tkt-343d392b51.eagle

Am I missing any of the steps you are doing in your code?

anonymous added on 2011-10-06 07:33:30 UTC:
your test code is nearly exactly what I'm doing. It's a strange behaviour: I constructed an attached DB with just one table "product", containing 5 columns. CHANGE_DATE (DateTime), ID (integer primary key), PRODUCT_CODE1 (varchar2), PRODUCT_CODE2 (varchar2), PRICE (number). The table contains 189 rows. the select-statement queries some of them (152) into the local ADO-table. I change the CHANGE_DATE column (to Date.Now) and the PRICE column (PRICE += 1.5). After that I do wait for 5 seconds (for whatever - it has no effect). Then I do the adapter.update. after updating 141 rows the exception raises on the 142th row. If I exclude this special row in my select then the exception does not appear. If I delete this special row an all others except one (so the table does contain just 1 single row) the exception again appear (no matter which product is left in the table, so the content of the table is not the reason). Hint: In between the processing of the attached DB, I write some log-information to a log table into the main-DB. I think it has something to do with block size, or so. Really strange. I'm not an really expert on C#, so the information I can extract out of the exception is more or less useless for me, saying just that there is an DBConcurrencyException. What can I do ? My production CPU is i7 870 2.93GHz with 8GB RAM 64-Bit Win7 But I still develop on 32Bit Vista with VS 2008 3.5 SP1 .NET The exception raises on both PCs.

mistachkin added on 2011-10-06 09:59:20 UTC:
I am now able to reproduce the bug... It has something to do with the DATETIME column.

anonymous added on 2011-10-06 10:56:36 UTC:

try {

log("start updates");

System.Threading.Thread.Sleep(5000);

int i = da.Update(dt);

log("updates: " + i);

return i;

}

catch (DBConcurrencyException e) { log("Error on Update - DBConcurrencyException: " + e.Row[0].ToString() + " - " + e.Row[1].ToString() + " - " + e.Row[2].ToString() + " - " + e.Row[3].ToString() + " - " + e.Row[4].ToString() + " - RowCount:" + e.RowCount); log("Error on Update - DBConcurrencyException: RowCount:" + e.RowCount + " - TargetSite: " + e.TargetSite + " - Message: " + e.Message.ToString() + " - Data: " + e.Data.ToString());

log("Error on Update - DBConcurrencyException: InnerException: " + e.InnerException.ToString() );

return -1; } }

the >> e.InnerException.ToString() << again raises an exception.

mistachkin added on 2011-10-08 11:36:04 UTC:
I've got a partial fix for this on the tkt-343d392b51 branch. Now, if you use the DateTimeFormat=JulianDay or UnixEpoch in the connection string, parameterized queries will work properly, fixing this issue in your case. However, if the default ISO8601 DateTimeFormat is used for the connection, there is not much that can be done due to how DATETIME columns are handled by SQLite (i.e. using TEXT operator semantics).

mistachkin added on 2011-10-09 03:58:22 UTC:
I've added some more changes to fix various things discovered while investigating this issue, including new tests. These changes are not in the trunk yet.

anonymous added on 2011-10-10 02:46:42 UTC:
An attached DB is a DB like a main DB (the same file structure) and it should be treated exactly in the same way like a main DB by SQLite. So the question is why an attached DB is processed differently !

mistachkin added on 2011-10-10 03:32:07 UTC:
One of the problems is that the .NET Framework assumes that there can be no database name without a schema name (which SQLite does not support); however, I have implemented a workaround in the branch for this ticket. The root of your issues was the DbCommandBuilder class in the framework itself. It makes certain assumptions that do not hold true for SQLite.

mistachkin added on 2011-10-18 09:06:12 UTC:
Fixed in trunk by check-in [fc49363310].