System.Data.SQLite

Greetings!  I have recently started coding in C# after many years of experience with scripting languages (Perl, TCL) and Powerbuilder.  I am working on a C# version of a TCL program that I wrote, and have decided to use a database on the back end.  I am working in Visual Studio 2008, and have added SQLite as my database.  To my knowledge, I am not using a device emulator of any kind.

The program takes text files and parses them, inserting records into seven database tables (depending on what part of the text file I am parsing) and on occasion updating existing records.

 In my code, I am doing many inserts and updates to my database.  I am creating the connection and the command object, and passing the command object to my database routines.  In general, the code works okay, but when I run in the debugger (have not made it to running without the debugger because of this problem), I am getting the "Some kind of disk I/O error occurred" on an insert.  It is not always the same insert,  but the code is not making it past the first few text files.  It makes it through a couple of files, and the records get into the database and get updated as needed, but then this error occurs and I am stumped.  When I look up this error on here and elsewhere, it always seems to come up when a device emulator is being used.  

Using 1.066 of System.Data.SQLite with SQLite v3 (whatever the latest is) and Visual Studio 2008.  Does anyone have ANY idea how to track down where this is coming from?  When I look at the stack trace I see:

 "   at System.Data.SQLite.SQLite3.Reset(SQLiteStatement stmt)\r\n   at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)\r\n   at System.Data.SQLite.SQLiteDataReader.NextResult()\r\n   at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)\r\n   at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)\r\n   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()\r\n   at BSP.DatabaseUtils.UpdateToTable(SQLiteCommand myCommand, StringBuilder sqlStatement) in C:\\Documents and Settings\\jmeltzer\\My Documents\\Visual Studio 2008\\Projects\\ConsoleApplication1\\ConsoleApplication1\\Program.cs:line 193\r\n   at BSP.BoxScoreParser.ParseDecisions(String dataSetName, String gameID, String[ decisionLines, Int32 lcount, SQLiteCommand myCommand) in C:\\Documents and Settings\\jmeltzer\\My Documents\\Visual Studio 2008\\Projects\\ConsoleApplication1\\ConsoleApplication1\\Program.cs:line 762\r\n   at BSP.BoxScoreParser.ParseBoxScoreDB(String filePath, String boxScoreFile, String dataSetName, String selectedTeam, SQLiteCommand myCommand) in C:\\Documents and Settings\\jmeltzer\\My Documents\\Visual Studio 2008\\Projects\\ConsoleApplication1\\ConsoleApplication1\\Program.cs:line 230\r\n   at BSP.GenerateData.GetFilesFromDirDB(DirectoryInfo directory, String startDateText, String endDateText, String dataSetName, String selectedTeam) in C:\\Documents and Settings\\jmeltzer\\My Documents\\Visual Studio 2008\\Projects\\ConsoleApplication1\\ConsoleApplication1\\Program.cs:line 143\r\n   at BSP.GenerateData.FindFiles(String startDirectory, Boolean subDirs, DateTime startDate, DateTime endDate, String dataSetName, String selectedTeam, Boolean appendToOutput) in C:\\Documents and Settings\\jmeltzer\\My Documents\\Visual Studio 2008\\Projects\\ConsoleApplication1\\ConsoleApplication1\\Program.cs:line 110\r\n   at BSP.ScoreFinder.CreateDataSet() in C:\\Documents and Settings\\jmeltzer\\My Documents\\Visual Studio 2008\\Projects\\ConsoleApplication1\\ConsoleApplication1\\Program.cs:line 73\r\n   at BSP.BSPMain.Main() in C:\\Documents and Settings\\jmeltzer\\My Documents\\Visual Studio 2008\\Projects\\ConsoleApplication1\\ConsoleApplication1\\Program.cs:line 17\r\n   at System.AppDomain._nExecuteAssembly(Assembly assembly, String[ args)\r\n   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[ args)\r\n   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()\r\n   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)\r\n   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)\r\n   at System.Threading.ThreadHelper.ThreadStart()"

 Any help would be much appreciated.  I seem to be stuck, but the lack of posts about similar errors (without using the emulator) leads me to believe that there must be a solution.

Thanks,

Jonathan

Hi,

the same problem occurs on one device we've got for testing... this device uses Windows CE 6.0, SQLite version is 0.66.

We're creating a new database file, initialising commands etc.; the exception is finally thrown at the first write operation to the database (ExecuteNonQuery). Reading/writing a file with StreamWriter in the same directory (program files) is working without any problem.

Anyone found a solution for this problem?

Thanks in advance!

Found a workaround for me... if the journal is turned off or set to any other value than DELETE, it works on this device!

Simply try to set something like "PRAGMA journal_mode = TRUNCATE" before the first write operation to fix this. I'm not really deep into this, but the question is, why isn't DELETE working here? Is the journal in DELETE mode placed somewhere else?

 Setting the journal_mode through the PRAGMA statement worked for me as well. Have a look at the post here: http://www.stevemcarthur.co.uk/blog/post/Some-kind-of-disk-IO-error-occurrede28093SQLite.asp. Has some code using the SQLiteCommand object and a PRAGMA statement.

We are having the same issue. We _sometimes_ recieve this “Some kind of disk I/O error occurred” error. We tried to set the journal_mode to TRUNCATE, but it did not resolve the problem. So setting it to anything other than DELETE, as suggest above, does not work. Shall we try other settings, like PERSIST? I'm affraid we cannot use OFF or MEMORY because tha doc says it might corrupt the database, which is obviously unacceptable for business reasons.

Any idea what could cause this error? We thought that it may be the anti-virus software, but we tried to eliminate this by turning off the virus scan for the folder where the sqlite database resides, and the problem still occurs. Can it be the Windows indexing service that interferes with the SQLite disk operations? Is there a way to tell SQLite to give a more meaningful error message? (what kind of I/O operation failed)

Any help would be greatly appreciated. Thanks in advance

Peter