System.Data.SQLite
Artifact [774e22236e]
Not logged in

Artifact 774e22236e65caac001c8e6b6b05e9e1e919a721:

Ticket change [774e22236e] - Closed ticket [4bbf851fa5]: DBConcurrencyException plus 1 other change by mistachkin 2012-02-29 14:54:09.
D 2012-02-29T14:54:09.541
J comment DBConcurrencyException\r\n\r\nThis\sissue\sis\srelated\sto\sthe\sclosed\sticket\s[343d392b51]\r\nIn\sfact\sthe\sproblem\sstill\sremains.\sPlease\sread\sthe\sold\sticket\sfirst.\r\n\r\nAs\sa\sworkaround\sI\snow\sdo\snot\suse\sadapter.Update()\sany\slonger\s(as\sit\sraises\sthe\sexception).\r\nInstead\sof\r\n\r\nString\s\stable\s=\s“table”;\r\ntry\s{\r\nsql\s=\s"select\s*\sfrom\stable\swhere\s...";\r\nda\s=\snew\sSQLiteDataAdapter(sql,\sdb_con);\r\nda.Fill(ds,\stable);\r\ndt\s=\sds.Tables[table];\r\n…\r\nmanipulate_data();\s\r\nint\si\s=\sda.Update(dt);\s//\shere\sthe\sDBConcurrencyException\sraises\s!!!\s\r\n\r\nI\snow\suse\sa\sstupid\ssql.command\sin\sa\sloop.\r\n…\r\nString\ssql\s=\sString.Format("update\stable\sset\s…\s“\r\n\r\nSQLiteCommand\scmd\s=\snew\sSQLiteCommand(sql,\sdbconnection);\r\nreturn\scmd.ExecuteNonQuery();\r\n…\r\n\r\nThis\sworks,\sbut\sit\sdoes\snot\sperform\swell\sand\sit\sis\svery\sugly\scode.\r\n________________________________\s\r\n\r\nThe\sDBConcurrencyException\sraises\snot\son\severy\sadapter.Update()\sstatement.\sSometimes\sit\sworks,\ssometimes\snot.\sSometimes\sa\sfew\srecords\sare\supdated,\ssometimes\sjust\sonly\sone,\ssometimes\snone.\sSometimes\sit\sraises\son\sthis\stable,\ssometimes\son\sthe\sother\stable.\r\nAll\smy\stables\shave\sas\sa\sfirst\scolumn\sa\sDateTime\scolumn\s(“CHANGE_DATE”).\r\nIt\sdoes\snot\smatter\swhether\sI\sdo\r\nrow[“CHANGE_DATE”]\s=\sDateTime.Now;\r\nOr\r\nrow["CHANGE_DATE"]\s=\sDateTime.Now.Year\s+\s"-"\s+\sDateTime.Now.Month\s+\s"-"\s+\sDateTime.Now.Day\s+\s"\s"\s+\sDateTime.Now.TimeOfDay;\r\n\r\nregards,\r\nSimon\r\n\r\n<hr\s/><i>mistachkin\sadded\son\s2011-12-25\s23:31:09\sUTC:</i><br\s/>\r\nAre\syou\susing\sDateTimeFormat=JulianDay\sor\sUnixEpoch\sin\sthe\sconnection\sstring?\s\sThis\sis\snecessary\sto\stake\sadvantage\sof\sthe\sfix\sfor\sthe\sprevious\sticket\syou\smentioned.\r\n\r\n<hr\s/><i>anonymous\sadded\son\s2011-12-26\s23:23:14\sUTC:</i><br\s/>\r\nIf\sI\suse\s>\sDateTimeFormat=JulianDay\s<\sin\sthe\sconnectionstring\sthe\sstatement\r\n\r\n\s\sxydatetime\s=\sreader.GetDateTime(0);\r\n\r\nraises\san\sexception:\r\n\r\nTypeInitialisationException:\s\swrong\sformat\r\n_______\r\n\r\nString\ssql\s=\s"select\sCHANGE_DATE,\s...\s\s\s\s\sfrom\sTABLE\s..\s";\r\n\r\nSQLiteCommand\scmd\s=\snew\sSQLiteCommand(sql,\sdbconnection);\r\n\r\nSQLiteDataReader\sreader\s=\scmd.ExecuteReader();\r\n\r\n\sif\s(reader.HasRows)\r\n\s\s\s\s\swhile\s(reader.Read())\r\n\s\s\s\s\s{\r\n\s\s\s\s\s\s\s...\r\n\s\s\s\s\s\s\sxydatetime\s=\sreader.GetDateTime(0);\r\n\s\s\s\s\s\s\s...\r\n\r\n\s\s\s\s\s\s\s}\r\n\r\n\s\sreader.Close();\r\n\r\n\r\n<hr\s/><i>mistachkin\sadded\son\s2011-12-27\s03:24:15\sUTC:</i><br\s/>\r\nWhen\syou\suse\sthe\sJulianDay\sformat,\sthe\stype\sconversion\scode\sassumes\sthat\sdatabase\sfields\swith\sthe\stype\sDATETIME\s(or\sDATE,\setc)\swill\sactually\sbe\sa\sfloating\spoint\snumber.\s\sLikewise,\swhen\syou\suse\sthe\sUnixEpoch\sformat,\sit\sassumes\sthat\sdatabase\sfields\swith\sthe\stype\sDATETIME\swill\sactually\sbe\san\sinteger\snumber\sof\sseconds\ssince\sthe\sUnix\sepoch.\r\n\r\n\r\n<hr\s/><i>anonymous\sadded\son\s2011-12-27\s17:54:42\sUTC:</i><br\s/>\r\nconnection\sstring\scontains\s>\sDateTimeFormat=JulianDay\s<\r\n\r\nthe\sfollowing\scode\sraises\sSystem.FormatException:\r\n\r\nString\sSql\s=\s"Select\sCHANGE_DATE,\s…\sfrom\stable\s..”;\r\n\s\r\nda\s=\snew\sSQLiteDataAdapter(sql,\sdbconnection);\r\n\r\nda.Fill(dset,\stable);\r\n\r\nThe\scolumn\sCHANGE_DATE\sis\sof\stype\sDATETIME.\r\n\r\n<hr\s/><i>anonymous\sadded\son\s2011-12-28\s00:06:25\sUTC:</i><br\s/>\r\nWhy\sdid\syou\sclose\sthis\sticket\s?\r\n\r\n<hr\s/><i>mistachkin\sadded\son\s2011-12-28\s05:07:57\sUTC:</i><br\s/>\r\nBecause\sthe\sbehavior\syou\sappear\sto\sdescribe\sis\snot\sa\sbug.\s\sWhen\susing\sthe\sUnixEpoch\sor\sJulianDay\sformats\sin\sthe\sconnection\sstring,\sthe\sunderlying\sdata\sin\sthe\sdatabase\sitself\smust\sbe\sof\sthe\stype\sexpected\sby\sthe\stype\sconversion\scode\sin\sSystem.Data.SQLite.\s\sFor\sUnixEpoch,\sit\smust\sbe\san\sinteger.\s\sFor\sJulianDay,\sit\smust\sbe\sa\sdouble-precision\sfloating-point\snumber.\r\n\r\n<hr\s/><i>anonymous\sadded\son\s2011-12-28\s09:43:43\sUTC:</i><br\s/>\r\nDoes\sthat\smean\sthat\sI\scan't\suse\scolumns\sof\stype\sDatTime\s?\r\nJust\sto\suse\sthe\sDateTimeFormat=JulianDay\sdoes\snot\sconvert\sthe\sunderlying\sdata\sin\sthe\sdatabase.\sRight\s?\sDo\sI\shave\sto\sconvert\sthe\sdata\sin\sthe\sdatabase\sfirst\sbefore\sworking\son\sit\s?\r\n\r\n<hr\s/><i>mistachkin\sadded\son\s2011-12-29\s01:43:17\sUTC:</i><br\s/>\r\nEffectively,\syes.\s\sThe\sDateTimeFormat\sproperty\sof\sthe\sconnection\sstring\scontrols\show\sSystem.Data.SQLite\sattempts\sto\s*interpret*\sthe\sactual\sdata.\s\sIf\syou\swant\sto\suse\sJulianDay\sor\sUnixEpoch\s(or\sTicks),\sthe\sactual\sdata\sin\sthe\sdatabase\smust\sbe\schanged\sfirst.\s\sAlternatively,\syou\scan\suse\sISO8601\sif\syour\sDateTime\sstrings\smatch\sone\sof\sthe\ssupported\sformat\sstrings.\r\n\r\n<hr\s/><i>anonymous\sadded\son\s2012-01-14\s00:01:19\sUTC:</i><br\s/>\r\nthis\sissue\sis\sdrivin\sme\snuts\r\n\r\nI\stried:\r\n\r\nfirst\sversion\r\n\r\nconnectionstring:\s\s\sDateTimeFormat=\sJulianDay\r\n\r\ntable\sproduct:\r\n\r\nchange_date\s\sdatetime,\r\nprice\snumber,\r\nname\svarchar2,\r\nid\sinteger\r\n\r\nfirstly,\sI\sdid:\r\n\r\nupdate\sproduct\sset\schange_date\s=\s2455928.0\s\s\s\s//which\sis\s2012-01-01\s12:00:00\r\n\r\nthen:\r\n\r\nsql\s=\s“select\schange_date,\sid,\sname,\sprice\sfrom\sproduct”;\r\n\r\nadapter\s=\s\snew\sSQLiteDataAdapter(sql,\sdb_con);\r\n\r\nadapter.Fill(ds,\sdatatab);\r\n\r\n….\r\n\r\nrow[“price”]\s\s+=\s1;\r\n\r\nrow[“change_date”]\s\s=\s\sDateTime.Now;\r\n\r\n….\r\n\r\nadapter.update(datatab);\s\s\s\s\s<-\shere\sthe\sDBConcurrencyException\sraises\r\n\r\n_______________________________________________\s\s\s\r\n\r\nsecond\sversion:\r\n\r\nsame\stable;\r\n\r\nconnectionString:\s\s\s\s\sDateTimeFormat=\sJulianDay\r\n\r\nupdate\sproduct\sset\schange_date\s=\s2455928.0\s\s\s\s//which\sis\s2012-01-01\s12:00:00\r\n\r\nthen\r\n\r\n!!\sdifference\s!!\r\n\r\nsql\s=\s“select\sjulianday(change_date)\sas\schange_date,\sid,\sname,\sprice\sfrom\sproduct”;\r\n\r\nadapter\s=\s\snew\sSQLiteDataAdapter(sql,\sdb_con);\r\n\r\nadapter.Fill(ds,\sdatatab);\r\n\r\n….\r\n\r\nrow[“price”]\s\s+=\s1.0;\r\n\r\n//\sin\sfact\snow\sthe\scolumn\sis\sof\stype\sSystem.Double\r\n\r\nrow[“change_date”]\s\s=\s\s(double)row[“change_date”]\s\s+\s1.0;\r\n\r\n….\r\n\r\nadapter.update(datatab);\r\n\r\nno\sexception\sraises,\sbut\swhat\shappens\sis:\r\n\r\nthe\sprice\scolumn\sis\supdated,\sthe\sDateTime-Column\sis\snot\s\s!\r\n\r\nWhat's\swrong\s?\r\n\r\n\r\n<hr\s/><i>mistachkin\sadded\son\s2012-01-14\s03:00:45\sUTC:</i><br\s/>\r\n<verbatim>\r\nInstead\sof:\r\n\r\nrow["change_date"]\s=\sDateTime.Now;\r\n\r\nTry:\r\n\r\nrow["change_date"]\s=\sSQLiteConvert.ToJulianDay(DateTime.Now);\r\n</verbatim>\r\n\r\n\r\n<hr\s/><i>anonymous\sadded\son\s2012-01-14\s14:27:33\sUTC:</i><br\s/>\r\nrow["change_date"]\s=\sSQLiteConvert.ToJulianDay(DateTime.Now);\r\n\r\n=>\scompiler\ssays:\r\n\r\nAn\sobject\sreference\sis\srequired\sfor\sthe\snon-static\sfield,\smethod,\sor\sproperty\s'System.Data.SQLite.SQLiteConvert.ToJulianDay(System.DateTime)'\r\n\r\n<hr\s/><i>mistachkin\sadded\son\s2012-01-15\s01:01:22\sUTC:</i><br\s/>\r\nOk,\sthat\sis\san\sissue\sbecause\sthose\stype\sconversion\smethods\sshould\sbe\sstatic\sas\sthey\sdo\snot\sneed\sany\sinstance\sdata.\r\n\r\n<hr\s/><i>mistachkin\sadded\son\s2012-01-15\s01:28:17\sUTC:</i><br\s/>\r\nChanged\sto\sstatic\sby\scheck-in\s[114024ffe7],\swith\stests.\r\n\r\n\r\n<hr\s/><i>anonymous\sadded\son\s2012-01-15\s19:04:07\sUTC:</i><br\s/>\r\nconnectionstring:\sDateTimeFormat=\sJulianDay\r\n\r\nupdate\sproduct\sset\schange_date\s=\s2455928.0\s\s\s\s//\swhich\sis\s2012-01-01\s12:00:00\r\n\r\nchange_date\sis\sof\stype\sDateTime\s!\r\n\r\n----------------\s\r\n\r\nsql\s=\s“select\schange_date,\sprice,\s....”;\r\n\r\nadapter\s=\snew\sSQLiteDataAdapter(sql,\sdb_con);\r\n\r\nadapter.Fill(ds,\sdatatab);\r\n\r\n….\r\n\r\nrow[[price]]\s+=\s1;\r\n\r\nrow[[change_date]]\s=\sSQLiteConvert.ToJulianDay(DateTime.Now);\s\s\s\s\s\s<-\s\r\n\r\nTypeConverionException\sraises,\r\n\r\nwhich\sis\sclear,\sbecause:\r\n\r\nthe\soriginal\sSQLite\stable-column\sis\sof\stype\sDateTime,\r\nthen\sthe\sADO-table\sis\salso\sof\stype\sSystem.DateTime,\sto\swhich\swe\swant\sto\sassign\sa\sdouble.\r\n\r\nSystem.ArgumentException:\sinvalid\stype\sconversion\s"Double"\sto\s"DateTime".<2455941,93204139>\scannot\sbe\ssaved\sto\sCHANGE_DATE\scolumn.\r\nExpected\stype:\sDateTime.\s--->\sSystem.InvalidCastException:\sinvalid\sconversion\s"Double"\sto\s"DateTime".\r\n\r\n\r\n<hr\s/><i>mistachkin\sadded\son\s2012-01-16\s01:00:09\sUTC:</i><br\s/>\r\nI\sforgot\sthat\sthe\snecessary\sconversions\shappen\sinternally.\s\sHere\sis\san\s<a\shref="/index.html/artifact?ci=tip&filename=Tests/tkt-343d392b51.eagle&ln=195-297">example</a>.\s\sBasically,\sthis\smeans\sthe\sstatement\sshould\sactually\sbe\ssomething\slike:\r\n<verbatim>row["change_date"]\s=\sDateTime.Now.ToString("yyyy-MM-dd\sHH:mm:ss.FFFFFFFK");</verbatim>\r\n\r\n<hr\s/><i>anonymous\sadded\son\s2012-01-18\s21:07:58\sUTC:</i><br\s/>\r\nI\sstill\shave\sthe\sDBConcurrencyException\sissue.\r\n\r\nI\snow\suse\sDateTimeFormat=JulianDay\sas\syou\srecommended.\r\nI\sconverted\sthe\sdata\sto\sfloats\sas\sdescribed:\r\nupdate\sproduct\sset\schange_date\s=\s2455928.0\s//\swhich\sis\s2012-01-01\s12:00:00\s\r\n\r\nI\snow\suse:\r\nrow["change_date"]\s=\sDateTime.Now.ToString("yyyy-MM-dd\sHH:mm:ss.FFFFFFFK");\r\n\r\nbut\sthe\sDBConcurrencyException\sstill\sraises\s(not\severytime,\sbut\smost\soften)\s\r\n\r\n<hr\s/><i>anonymous\sadded\son\s2012-02-03\s06:56:17\sUTC:</i><br\s/>\r\nDid\syou\ssee\smy\slast\scomment\s?\sThe\sissue\sstill\sexists.\r\nI\stested\swith\s1.0.79.0\s-\sno\schanges.\r\nI\sthink\sit's\sa\sdeeper\sproblem,\sprobably\slinked\sto\s[d76b409d07]\r\nWhat\scan\sI\sdo\s?\r\n\r\n<hr\s/><i>mistachkin\sadded\son\s2012-02-03\s13:46:16\sUTC:</i><br\s/>\r\nI'm\sstill\sunable\sto\sreproduce\sthe\sproblem\sat\sall\shere.\s\sOne\sthing\sto\stry\s(using\sthe\slatest\schecked-in\scode,\snot\s1.0.79.0)\sis\sto\sset\sthe\sFlags\sproperty\sof\sthe\sSQLiteConnection\sobject\sto\sLogPrepare\sand\sthen\swatch\sthe\strace\soutput\sto\ssee\swhat\sSQL\sstatement\sis\sreturning\szero\srecords.\s\sThen,\sadd\sthat\sSQL\sstatement\sto\sthis\sticket\sso\sI\scan\sfigure\sout\swhere\sthings\sare\sgoing\swrong.\r\n\r\n\r\n<hr\s/><i>anonymous\sadded\son\s2012-02-05\s23:02:43\sUTC:</i><br\s/>\r\nok,\swill\sdo.\r\nfor\sthe\sfirst\sthe\sexception\sin\sdetail:\r\n\r\ne.Source:\r\nSystem.Data\r\n\r\ne.Data:\r\nSystem.Collections.ListDictionaryInternal\r\n\r\ne.StackTrace:\r\n\s\s\sat\sSystem.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs\srowUpdatedEvent,\sBatchCommandInfo[]\sbatchCommands,\sInt32\scommandCount)\r\n\s\s\sat\sSystem.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs\srowUpdatedEvent,\sBatchCommandInfo[]\sbatchCommands,\sInt32\scommandCount)\r\n\s\s\sat\sSystem.Data.Common.DbDataAdapter.Update(DataRow[]\sdataRows,\sDataTableMapping\stableMapping)\r\n\s\s\sat\sSystem.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable\sdataTable,\sDataTableMapping\stableMapping)\r\n\s\s\sat\sSystem.Data.Common.DbDataAdapter.Update(DataTable\sdataTable)\r\n\s\s\sat\sTest.mytest.mytest.UpdateTest(DataTable\sdt)\sin\sC:\\Users\\admin\\Documents\\Visual\sStudio\s2008\\Projects\\Test\\Test\\mytest\\mytest.cs:Line\s236.\r\n\r\ne.TargetSite:\r\nInt32\sUpdatedRowStatusErrors(System.Data.Common.RowUpdatedEventArgs,\sBatchCommandInfo[],\sInt32)\r\n\r\ne.Message:\r\nConcurrency\sviolation:\sthe\sUpdateCommand\saffected\s0\sof\sthe\sexpected\s1\srecords.\r\n\r\n<hr\s/><i>mistachkin\sadded\son\s2012-02-06\s22:01:43\sUTC:</i><br\s/>\r\nThe\sexception\sinformation\sitself\sis\snot\sparticularly\silluminating.\s\sThe\sSQL\sstatement\scausing\sthe\sissue\swill\sbe\sfar\smore\sinformative.\r\n\r\n\r\n<hr\s/><i>anonymous\sadded\son\s2012-02-08\s13:42:08\sUTC:</i><br\s/>\r\n5\srows\swhere\supdated\s(of\s~200)\sthen\sthe\sexception\socurred.\r\n\r\ntrace\soutput:\r\n\r\n...\r\n\r\nPrepare:\s123241624\r\n\r\nSQLite\smessage\s(0):\sPreparing\s{UPDATE\s[main].[PRODUCT]\sSET\s[CHANGE_DATE]\s=\s@param1,\s[ID]\s=\s@param2,\s[PRICE]\s=\s@param3\sWHERE\s(([CHANGE_DATE]\s=\s@param4)\sAND\s([ID]\s=\s@param5)\sAND\s((@param6\s=\s1\sAND\s[PRICE]\sIS\sNULL)\sOR\s([PRICE]\s=\s@param7)))}...\r\n\r\nPrepare:\s123239424\r\n\r\n'Test.vshost.exe'\s(Managed):\sLoaded\s'C:\\Windows\\assembly\\GAC_MSIL\\System.Data.resources\\2.0.0.0_de_b77a5c561934e089\\System.Data.resources.dll'\r\n\r\nA\sfirst\schance\sexception\sof\stype\s'System.Data.DBConcurrencyException'\soccurred\sin\sSystem.Data.dll\r\n\r\n...\r\n\r\n\r\n<hr\s/><i>mistachkin\sadded\son\s2012-02-08\s21:13:05\sUTC:</i><br\s/>\r\nJudging\sfrom\sthe\squery\syou\sposted,\sit\slooks\slike\sthis\scould\sagain\sbe\sa\sDateTime\sformat\sissue\ssince\sthere\sis\sa\sDateTime\scolumn\sin\sthe\sWHERE\sclause.\s\sWould\sit\sbe\spossible\sto\sfigure\sout\swhat\sparameter\svalues\sare\sbeing\sused\sat\sthat\spoint?\r\n\r\n<hr\s/><i>anonymous\sadded\son\s2012-02-09\s23:19:57\sUTC:</i><br\s/>\r\nagain\s5\srows\swhere\supdated,\sthen\sthe\sexception\socurred\r\n\r\ncode:\r\n\r\n\s\s\s\s\s\s\s\s\s\s\s\scstr\s=\snew\sSQLiteConnectionStringBuilder();\r\n\r\n\s\s\s\s\s\s\s\s\s\s\s\scstr.DataSource\s=\smyDB;\r\n\r\n\s\s\s\s\s\s\s\s\s\s\s\scstr.Version\s=\s3;\r\n\r\n\s\s\s\s\s\s\s\s\s\s\s\scstr.DateTimeKind\s=\sDateTimeKind.Local;\r\n\r\n\s\s\s\s\s\s\s\s\s\s\s\scstr.DateTimeFormat\s=\sSQLiteDateFormats.JulianDay;\r\n\r\n\s\s\s\s\s\s\s\s\s\s\s\scstr.Flags\s=\sSQLiteConnectionFlags.LogPrepare;\r\n\r\n...\r\n\r\n\s\s\s\s\s\s\s\s\s\s\s\sfor\s(int\si\s=\s0;\si\s<\srcount;\si++)\r\n\r\n\s\s\s\s\s\s\s\s\s\s\s\s{\r\n\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\sSystem.Threading.Thread.Sleep(5000);\r\n\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\sdt.Rows[i]["PRICE"]\s=\s(double)dt.Rows[i]["PRICE"]\s+\s5.123;\r\n\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\sstring\ss\s=\sDateTime.Now.ToString("yyyy-MM-dd\sHH:mm:ss.FFFFFFFK");\r\n\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\sdt.Rows[i]["CHANGE_DATE"]\s=\ss;\r\n\r\n\s\s\s\s\s\s\s\s\s\s\s\s}\r\n\r\n---\r\n\r\nadapter.Update();\r\n\r\nstring\ss\soutputs:\r\n\r\n2012-02-09\s23:51:24.746+01:00\r\n\r\n2012-02-09\s23:51:29.756+01:00\r\n\r\n2012-02-09\s23:51:34.762+01:00\r\n\r\n2012-02-09\s23:51:39.772+01:00\r\n\r\n2012-02-09\s23:51:44.777+01:00\r\n\r\n2012-02-09\s23:51:49.787+01:00\r\n\r\n2012-02-09\s23:51:54.797+01:00\r\n\r\n2012-02-09\s23:51:59.807+01:00\r\n\r\n2012-02-09\s23:52:04.817+01:00\r\n\r\n2012-02-09\s23:52:09.823+01:00\r\n\r\n2012-02-09\s23:52:14.829+01:00\r\n\r\n2012-02-09\s23:52:19.835+01:00\r\n\r\nand\sso\son\r\n\r\n<hr\s/><i>mistachkin\sadded\son\s2012-02-10\s06:55:38\sUTC:</i><br\s/>\r\nI've\sbeen\strying\sto\sreproduce\sthis\sissue\shere\sbased\son\sthe\smodified\sexample\syou\sprovided;\showever,\sno\smatter\swhat\svariation\sI\stry,\sI\scannot\sget\sthe\sconcurrency\sexception.\s\sHere\sis\smy\scurrent\stest\scase\s(the\scomplete\sC#\scode\sis\sembedded\swithin\sthe\stest\sscript\sitself):\r\n\r\n<verbatim>\r\nrunTest\s{test\stkt-4bbf851fa5-1.1\s{SQLiteDataAdapter\supdate\sfail}\s-setup\s{\r\n\s\ssetupDb\s[set\sfileName\stkt-4bbf851fa5-1.1.db]\r\n}\s-body\s{\r\n\s\sset\sid\s[object\sinvoke\sInterpreter.GetActive\sNextId]\r\n\s\sset\sdataSource\s[file\sjoin\s[getDatabaseDirectory]\s$fileName]\r\n\r\n\s\sset\ssql(inserts)\s""\r\n\s\sset\ssql(1)\s[subst\s{\s\\\r\n\s\s\s\sCREATE\sTABLE\st1(y\sDATETIME\sPRIMARY\sKEY,\sz\sFLOAT);\s\\\r\n\s\s\s\s[for\s{set\si\s1}\s{$i\s<\s10}\s{incr\si}\s{\r\n\s\s\s\s\s\sappend\ssql(inserts)\s[appendArgs\s\\\r\n\s\s\s\s\s\s\s\s\s\s"INSERT\sINTO\st1\s(y,\sz)\sVALUES(JULIANDAY('"\s\\\r\n\s\s\s\s\s\s\s\s\s\s[clock\sformat\s$i\s-format\s[getDateTimeFormat]]\s"'),\s"\s$i\s");\s"]\r\n\s\s\s\s};\sreturn\s[expr\s{[info\sexists\ssql(inserts)]\s?\s$sql(inserts)\s:\s""}]]\s\\\r\n\s\s}]\r\n\r\n\s\sset\ssql(2)\s[subst\s{\s\\\r\n\s\s\s\sSELECT\sy,\sz\sFROM\st1\sORDER\sBY\sy;\s\\\r\n\s\s}]\r\n\r\n\s\sunset\s-nocomplain\sresults\serrors\r\n\r\n\s\sset\scode\s[compileCSharpWith\s[subst\s{\r\n\s\s\s\susing\sSystem;\r\n\s\s\s\susing\sSystem.Data;\r\n\s\s\s\susing\sSystem.Data.SQLite;\r\n\r\n\s\s\s\snamespace\s_Dynamic${id}\r\n\s\s\s\s{\r\n\s\s\s\s\s\spublic\sclass\sTest${id}\r\n\s\s\s\s\s\s{\r\n\s\s\s\s\s\s\s\spublic\sstatic\svoid\sMain()\r\n\s\s\s\s\s\s\s\s{\r\n\s\s\s\s\s\s\s\s\s\sSQLiteConnectionStringBuilder\sconnStr\s=\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\snew\sSQLiteConnectionStringBuilder();\r\n\r\n\s\s\s\s\s\s\s\s\s\sconnStr.DataSource\s=\s"${dataSource}";\r\n\s\s\s\s\s\s\s\s\s\sconnStr.Version\s=\s3;\r\n\s\s\s\s\s\s\s\s\s\sconnStr.DateTimeKind\s=\sDateTimeKind.Local;\r\n\s\s\s\s\s\s\s\s\s\sconnStr.DateTimeFormat\s=\sSQLiteDateFormats.JulianDay;\r\n\s\s\s\s\s\s\s\s\s\sconnStr.Flags\s=\sSQLiteConnectionFlags.LogPrepare;\r\n\r\n\s\s\s\s\s\s\s\s\s\susing\s(SQLiteConnection\sconnection\s=\snew\sSQLiteConnection(\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\sconnStr.ToString()))\r\n\s\s\s\s\s\s\s\s\s\s{\r\n\s\s\s\s\s\s\s\s\s\s\s\sconnection.Open();\r\n\r\n\s\s\s\s\s\s\s\s\s\s\s\susing\s(SQLiteCommand\scommand\s=\sconnection.CreateCommand())\r\n\s\s\s\s\s\s\s\s\s\s\s\s{\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\scommand.CommandText\s=\s"${sql(1)}";\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\scommand.ExecuteNonQuery();\r\n\s\s\s\s\s\s\s\s\s\s\s\s}\r\n\r\n\s\s\s\s\s\s\s\s\s\s\s\susing\s(SQLiteDataAdapter\sdataAdapter\s=\snew\sSQLiteDataAdapter(\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s"${sql(2)}",\sconnection))\r\n\s\s\s\s\s\s\s\s\s\s\s\s{\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\susing\s(DataSet\sdataSet\s=\snew\sDataSet())\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s{\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\sdataAdapter.Fill(dataSet,\s"t1");\r\n\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\sDataTable\sdataTable\s=\sdataSet.Tables\\["t1"\\];\r\n\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\sdataTable.Columns\\["y"\\].Unique\s=\strue;\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\sdataTable.PrimaryKey\s=\snew\sDataColumn\\[\\]\s{\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\sdataTable.Columns\\["y"\\]\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s};\r\n\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s[expr\s{[isMono]\s?\s"#pragma\swarning\sdisable\s219"\s:\s""}]\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\sSQLiteCommandBuilder\scommandBuilder\s=\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\snew\sSQLiteCommandBuilder(dataAdapter);\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s[expr\s{[isMono]\s?\s"#pragma\swarning\srestore\s219"\s:\s""}]\r\n\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\sfor\s(int\sindex\s=\s0;\sindex\s<\sdataTable.Rows.Count\s-\s1;\sindex++)\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s{\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\sSystem.Threading.Thread.Sleep(1000);\r\n\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\sdataTable.Rows\\[index\\]\\["y"\\]\s=\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\sDateTime.Now.ToString("[getDateTimeFormat]");\r\n\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\sdataTable.Rows\\[index\\]\\["z"\\]\s=\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s(double)dataTable.Rows\\[index\\]\\["z"\\]\s+\s1.123;\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s}\r\n\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\sdataAdapter.Update(dataTable);\s//\sDBConcurrencyException\s(?)\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s}\r\n\s\s\s\s\s\s\s\s\s\s\s\s}\r\n\s\s\s\s\s\s\s\s\s\s}\r\n\s\s\s\s\s\s\s\s}\r\n\s\s\s\s\s\s}\r\n\s\s\s\s}\r\n\s\s}]\strue\strue\strue\sresults\serrors\sSystem.Data.SQLite.dll]\r\n\r\n\s\slist\s$code\s$results\s\\\r\n\s\s\s\s\s\s[expr\s{[info\sexists\serrors]\s?\s$errors\s:\s""}]\s\\\r\n\s\s\s\s\s\s[expr\s{$code\seq\s"Ok"\s?\s[catch\s{\r\n\s\s\s\s\s\s\s\sobject\sinvoke\s_Dynamic${id}.Test${id}\sMain\r\n\s\s\s\s\s\s}\sresult]\s:\s[set\sresult\s""]}]\s$result\r\n}\s-cleanup\s{\r\n\s\scleanupDb\s$fileName\r\n\r\n\s\sunset\s-nocomplain\sresult\scode\sresults\serrors\ssql\sdataSource\sid\sdb\sfileName\r\n}\s-constraints\s\\\r\n{eagle\smonoBug28\scommand.sql\scompile.DATA\sSQLite\sSystem.Data.SQLite}\s-match\s\\\r\nregexp\s-result\s{^Ok\sSystem#CodeDom#Compiler#CompilerResults#\\d+\s\\{\\}\s0\s\\{\\}$}}\r\n</verbatim>\r\n\r\n\r\n<hr\s/><i>mistachkin\sadded\son\s2012-02-10\s19:13:56\sUTC:</i><br\s/>\r\nBy\sany\schance,\sis\sthere\sanother\sthread\sor\sprocess\smodifying\sthe\sdatabase\sat\sthe\ssame\stime\sas\sthe\sdata\sadapter\sis\strying\sto?\r\n\r\n<hr\s/><i>anonymous\sadded\son\s2012-02-14\s07:33:46\sUTC:</i><br\s/>\r\nI've\ssent\san\semail\sto\ssqlite-dev\swith\sexample\scode\san\sa\sdb-file\swhich\sgenerates\sthe\sexception.\sDid\syou\ssee\sit\s?\r\n\r\n<hr\s/><i>mistachkin\sadded\son\s2012-02-14\s09:59:01\sUTC:</i><br\s/>\r\nI\shaven't\sseen\sit\syet.\s\sAlso,\ssince\sthe\smailing\slist\sstrips\sattachments,\sit\smight\sbe\sbest\sto\supload\sit\ssomewhere\spublic\s(e.g.\sDropbox)\sand\sthen\sprovide\sa\slink\sto\sit\sin\sthe\semail.\r\n\r\n<hr\s/><i>anonymous\sadded\son\s2012-02-14\s11:47:02\sUTC:</i><br\s/>\r\nhere\sis\sthe\slink:\r\n\r\ndb-file:\s\shttp://dl.dropbox.com/u/62206335/xy.db3\r\n\r\ncode:\s\s\s\shttp://dl.dropbox.com/u/62206335/code.txt\r\n\r\nI\sfound\sanother\sstrange\sbehaviour:\r\nthe\stest\scode\slikes\sto\soverride\sthe\sprice\swith\sthe\svalue\s5.123.\r\nthe\sfirst\sten\srows\sare\supdated\sbut\swith\sthe\svalue\s5,\snot\s5.123.\r\nThen\sthe\sexception\soccurs.\r\n(it's\sthe\ssame\scode\si've\ssent\sby\semail)\r\n\r\n\r\n<hr\s/><i>mistachkin\sadded\son\s2012-02-14\s21:47:05\sUTC:</i><br\s/>\r\nThanks.\s\sI\sthink\syour\sexample\swill\sbe\shelpful\sin\stracking\sthis\sissue\sdown.\s\sAs\sfor\sthe\sPRICE\sissue,\sthe\s"NUMBER"\scolumn\stype\sis\snot\srecognized\sby\sSQLite\snor\sSystem.Data.SQLite.\s\sI\swould\suse\sDOUBLE\sor\sREAL\sinstead.\r\n\r\n\r\n<hr\s/><i>mistachkin\sadded\son\s2012-02-14\s22:14:23\sUTC:</i><br\s/>\r\n\r\nIt\sappears\sthe\smixture\sof\sdata\sin\sthe\sdatabase\scombined\swith\sthe\sNUMBER\scolumn\stype\sis\sthe\sroot\scause\sof\sthe\sissue.\s\sFor\sexample,\stry\sthis\squery\swith\syour\sdatabase:\r\n\r\n<verbatim>\r\n\s\s\s\sSELECT\stypeof(PRICE)\sFROM\sproduct;\r\n</verbatim>\r\n\r\nNotice\sthat\ssome\sof\sthe\sresults\ssay\s"integer"\sand\sothers\s"real".\s\sFor\sSQLite,\sthis\sis\snot\sa\sbig\sdeal,\ssince\sit\swill\ssimply\sdefault\sthe\scolumn\saffinity\sto\sTEXT;\showever,\sSystem.Data.SQLite\suses\sthe\scolumn\stype\sinformation\sto\sprovide\sa\smapping\sto\sthe\sactual\s.NET\stype\sand\sdatabase\scolumn\stype.\s\sIn\sthe\scase\sof\s"NUMBER",\sit\sreturns\sthe\sdefault,\sDbType.Object.\r\n\r\nAt\sthat\spoint,\sI\sthink\s.NET\sattempts\sto\sfigure\sout\sthe\stype\sof\sthe\scolumn\sbased\son\sthe\sdata\scurrently\sin\sit\sfor\sthe\srow\s(this\sis\sjust\san\seducated\sguess)\sand\sthen\sattempts\sto\sconvert\sthe\snew\svalue\sto\sthat\stype.\s\sIf\sthe\snew\svalue\sends\sup\sbeing\struncated\sfrom\sREAL\sto\sINTEGER,\sfor\sexample,\sit\scould\sresult\sin\s5.123\sbecoming\s5,\sas\syou\sstated.\s\sThis\smay\scause\sthe\sexception,\sfor\sexample,\sif\sthe\svalue\swas\sactually\s5\sto\sstart\swith.\r\n\r\n\r\n<hr\s/><i>mistachkin\sadded\son\s2012-02-14\s22:21:31\sUTC:</i><br\s/>\r\nFor\sthe\sfuture,\sI've\sadded\sa\sTrace\swarning\s(in\sthe\sDebug\sbuild\sonly)\sto\sthe\stype\smapping\scode,\ssee\scheck-in\s[10fae679b2].\r\n\r\n\r\n<hr\s/><i>anonymous\sadded\son\s2012-02-15\s07:01:55\sUTC:</i><br\s/>\r\nthanks.\r\n\r\nand\sjust\sfor\syour\sinformation:\r\n\r\nyou\salso\sclosed\sticket\s[d76b409d07]\swhich\swas\snot\smy\sticket.\r\n(and\sthis\sguy\suses\sonly\svarchars\sand\sone\sinteger\sprim\skey,\r\nseems\sthat\smy\sproblem\sis\snot\shis\sproblem)\r\n\r\nSimon\r\n\r\n<hr\s/><i>mistachkin\sadded\son\s2012-02-15\s07:56:04\sUTC:</i><br\s/>\r\nGiven\sthe\srather\sextensive\samount\sof\sanalysis\sand\stesting\sI\shave\sconducted\son\sthese\sconcurrency\sexception\sissues\sand\sthe\sfact\sthat\sthis\sticket\sand\sanother\sticket\slike\sit\swere\sboth\scaused\sby\sdata\stype\sconversion\sissues\soutside\sof\sthe\sdirect\scontrol\sof\sthe\slibrary,\sI\sam\sfairly\sconfident\sthat\sthe\sfinal\ssuch\sticket,\s[d76b409d07],\sdoes\snot\sactually\srepresent\sa\sreal\sissue\swith\sSQLite\sor\sSystem.Data.SQLite.\s\sThat\sbeing\ssaid,\sif\sthe\soriginal\sreporter\sdiscovers\snew\sinformation\sor\sevidence\sto\spoint\sto\san\sactual\sissue,\she\scan\salways\sre-open\sthe\sticket.\r\n\r\n\r\n<hr\s/><i>anonymous\sadded\son\s2012-02-18\s11:13:37\sUTC:</i><br\s/>\r\nif\stested\s-\sno\schanges.\r\n\r\nI\sstarted\swith\sa\scomplete\snew\sdb-file,\screated\sa\stable\sproduct\swith\sone\sDatetime\scolumn,\sone\sinteger\sprim\skey\scolumn\sand\sthen\sonly\stext\sand\sreal\scolumns.\r\nAgain\sI\sinserted\smy\s180\sproducts.\r\n\r\nafter\sround\sabout\sten\sruns\sof\smy\sprogram\swith\seach\supdates\sround\sabout\s30\srows\scorrectly\swith\sno\sproblems\sthe\sexception\socurrs\sagain,\shaving\supdated\sonly\s15\srows.\r\n\r\nrunning\sthe\sgiven\scode\swith\sthe\sgiven\sdbfile\swill\supdate\sagain\sonly\s15\srows\sbefore\sthe\sexception\soccurs.\r\n\r\nhttp://dl.dropbox.com/u/62206335/code2.txt\r\n\r\nhttp://dl.dropbox.com/u/62206335/code2.txt\r\n\r\n\r\n<hr\s/><i>anonymous\sadded\son\s2012-02-18\s16:31:33\sUTC:</i><br\s/>\r\ncorrect\slinks:\r\n\r\nhttp://dl.dropbox.com/u/62206335/code2.txt\r\n\r\nhttp://dl.dropbox.com/u/62206335/abc.db3\r\n\r\n<hr\s/><i>mistachkin\sadded\son\s2012-02-19\s12:10:11\sUTC:</i><br\s/>\r\nAfter\sresearching\sthis\sissue,\sit\sappears\sto\sbe\scaused\sby\srounding\serrors\sin\sthe\sdouble\sprecision\svalues\sbeing\sused\sfor\sthe\sJulian\sDay\snumbers.\s\sFor\sexample,\sthe\sUPDATE\squery\sgenerated\sby\sthe\s.NET\sFramework\sdata\sadapter\ssubsystem\sfor\sthe\sfirst\srow\sin\sthe\stable\slooks\ssomething\slike\sthis:\r\n\r\n<verbatim>\r\n\s\sUPDATE\s[main].[t1]\sSET\s[y]\s=\s@param1,\s[z]\s=\s@param2\r\n\s\sWHERE\s(([x]\s=\s@param3)\sAND\s([y]\s=\s@param4)\sAND\s([z]\s=\s@param5))\r\n</verbatim>\r\n\r\nAnd\sthe\svalues\sfor\sthe\sparameters\sare\sas\sfollows:\r\n\r\n<verbatim>\r\n\s\s@param1\s=\s2455976.6870139237\s(double)\r\n\s\s@param2\s=\s9.876\s(double)\r\n\s\s@param3\s=\s1\s(long)\r\n\s\s@param4\s=\s2455975.9869903936\s(double)\r\n\s\s@param5\s=\s5.123\s(double)\r\n</verbatim>\r\n\r\nThe\sproblem\sis\sthat\sthe\sactual\sfirst\srow\sin\sthe\stable\scontains\sthis\sdata:\r\n\r\n<verbatim>\r\n\s\sINSERT\sINTO\st1\s(x,\sy,\sz)\sVALUES(1,\s2455975.98699039,\s5.123);\r\n</verbatim>\r\n\r\nThis\scauses\sthe\sWHERE\sclause\sin\sthe\sabove\sUPDATE\squery\sto\smatch\szero\srows\sbecause\s2455975.98699039\s(in\sdatabase)\sis\snot\sequal\sto\s2455975.9869903936\s(query\sparameter).\n\n<hr\s/><i>anonymous\sadded\son\s2012-02-19\s22:42:13\sUTC:</i><br\s/>\nso\sthe\squestion\sis:\swhere\sdo\sthe\sadditional\s2\sdigits\scome\sfrom\s?\r\nand\sit\sis\snot\san\simmediate\sexplanation\sfor\sthe\sexception.\n\n<hr\s/><i>mistachkin\sadded\son\s2012-02-20\s00:13:42\sUTC:</i><br\s/>\nFirst,\sthe\sWHERE\sclause\sin\sthe\sUPDATE\squery\sreturning\szero\srows\sis\sprecisely\swhat\scauses\sthe\sexception.\r\n\r\nThe\s"extra"\sdigits\sare\spresumably\scaused\sby\sdouble\sprecision\srounding\serrors,\ssee:\r\n\r\nhttp://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html\r\n\n\n<hr\s/><i>anonymous\sadded\son\s2012-02-20\s00:28:39\sUTC:</i><br\s/>\nwow,\sfor\sthat\sarticle\sI\sneed\sdays\s...\r\n\r\nbut\swhat\sI\sdon't\sunderstand\sis\sthe\scause\sfor\sthe\sexception.\sIf\sthe\swhere\sclause\sdoes\snot\smatch,\sok,\sso\swhat,\sthe\srow\swill\snot\sbe\supdated.\sBut\swhy\sthe\sexception\s?\n\n<hr\s/><i>mistachkin\sadded\son\s2012-02-20\s04:10:09\sUTC:</i><br\s/>\nThe\sexception\soccurs\sbecause\sthe\s.NET\sFramework\stries\sto\sUPDATE\sone\srow\sat\sa\stime\sand\sif\szero\srows\sare\smatched,\sit\sthrows\sthe\sexception\sautomatically.
J status Closed
K 4bbf851fa5b26c1ca74a102731942f99ba8aa18e
U mistachkin
Z f57b9f89b603c84f7d0523e4e81ad512