System.Data.SQLite

I have found out, that this issue occurs only under Windows Vista and only if i put my database file into the AppData folder. I have no idea what Vista does with the database file that it blocks access to it or its folder so that sqlite cannot create a journal file. I have no antivirus software running and i have disabled indexing service. I have also noticed that this exception occurs often if i create command objects and execute sql statements inside of the using(...) block. Is there any way to grant sqlite exclusive access to the database file so that no other process can open it for writing?

Hi. I have this error message as well. Now here's the thing:

It never happens when debugging or at any other filesystem location on machines with the development environment (Visual C# Express 2005). It however fails to open the database on any (tested) system without Visual. I've double checked the db-path in the connection string, it's always correct...

 Here's the relevant code (trivial, but just in case) :

            private SQLiteConnection m_Conn = new SQLiteConnection("Data Source=" +        Path.GetDirectoryName(Application.ExecutablePath) + "\\db\\default.db");

            MessageBox.Show(m_Conn.ConnectionString);
            m_Conn.Open();

Any Ideas?

Easiest thing to do is to try and open the file for read/write access using File.Open().  If that succeeds and the connection fails, then we have a real problem.

I have tried to open the database file in external editor when there were database connections established from my application. If i modify the file and save it, the database connection is broken. The thing is, that any other program can access database file and block/modify it at any time, what can cause my application to loose database connection. And from the process of my application i could also open the database file in shared read/write mode. Any suggestions to avoid blocking of the database file?

 hello,

 I just wanted to ask you if this problem is allready fixed or not, because I am now also running into this problem if I install my software under partition C: under Windows Vista.

 thx

juvi 

 See http://www.nabble.com/forum/ViewPost.jtp?post=17656998&framed=y

 Hello, I got the same error message :

It append in a web application. When I try on my computer there is no problem, but when I try on the server, it can read data but no insert works.

Security settings are ok, no antivirus on our pre production platform.

Here is the C# code sample that raise error :

    public static void AddNews(News news)
    {
        using (SQLiteConnection connexion = new SQLiteConnection(SqlLiteConnectionString))
        using (SQLiteCommand command = new SQLiteCommand("insert into news (title,date,author,description,encodedtitle) values (@title,@date,@author,@description,@encodedtitle)", connexion))
        {
            command.Parameters.AddWithValue("@title", news.Title);
            command.Parameters.AddWithValue("@date", news.Date);
            command.Parameters.AddWithValue("@author", news.Author);
            command.Parameters.AddWithValue("@description", news.Description);
            command.Parameters.AddWithValue("@encodedtitle", news.EncodedTitle);
            //connexion.Open();
            //command.ExecuteNonQuery(); -----> This causes following error when uncomment this line and above

            using (System.IO.Stream stream = System.IO.File.Open(DatabaseFileName,System.IO.FileMode.Open,System.IO.FileAccess.ReadWrite))
            {
                stream.Close();
            } -----> this works
        }

    }

Error is : 

• ErrorCode : -2147467259
• Message : Exception of type 'System.Web.HttpUnhandledException' was thrown.
• Data : System.Collections.ListDictionaryInternal
• InnerException : System.Data.SQLite.SQLiteException: Unable to open the database file
  unable to open database file
  at System.Data.SQLite.SQLite3.Reset(SQLiteStatement stmt)
  at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
  at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()

Dear all,

After struckling around with this exception for months I finally found a solution for my product. And thanks to Robert to include the Journal Mode in the SQLiteConnectionStringBuilder.

First of all make very sure that the users have write access to the folder the database is created or installed in. Take for example the AppData folder of Vista, this folder is not writable for normal users. So make sure that your installation will set the permissions or use another folder. For my own application I’ve chosen to use the C:\Documents and Settings\USERNAME\Local Settings\Application Data\APPLICATIONNAME\VERSION. This is one of the recommended folders and easy to find by using System.Windows.Forms.Application.LocalUserAppDataPath. It ensure that the file is local (so no decreasing performance by having the database on a network share) and there is a file per user to prevent issues like user A has the database open and user B switches user and tries to use that very same database.

So if you get this “unable to open database” connection every time you try an INSERT or UPDATE the user has probably no permission to create the journal file.


Secondly, due to the deleting and creating of the journal file we run in strange problems. See:
 * http://www.nabble.com/CANTOPEN-error-on-Windows-systems-running-TortoiseSVN-td17656998.html#a17656998
 * http://www.mail-archive.com/sqlite-users@sqlite.org/msg34460.html
 * http://www.sqlite.org/cvstrac/tktview?tn=2987,6

While the sqlite driver tries to delete the journal file, the file could be in use by some other process. So the file isn’t deleted immediately. You can see this by using the File Monitor from Microsoft http://technet.microsoft.com/en-us/sysinternals/bb896642.aspx

The very simple solutions is to use the new (in 1.0.54.0) JournalMode property of the SQLiteConnectionStringBuilder.

  SQLiteConnectionStringBuilder connectionStringBuilder = new SQLiteConnectionStringBuilder ();
  connectionStringBuilder.JournalMode = SQLiteJournalModeEnum.Persist;
  connectionStringBuilder.DataSource = SQLITE_FILE;
  CONNECTION_STRING = connectionStringBuilder.ConnectionString;


Due to this very common problem, nearly every computer has different processes scanning files. I would pleat to make the Persist mode for Windows systems the default. But maybe I overlook some other technically things where I have no knowledge of.

The new pragma's usefulness may be short-lived.  There was a recent checkin for the upcoming SQLite 3.61 release that improves some of the code around deleting files, which directly affects the journal file.

After 3.61 you may not have to set the journal mode to persist anymore.

I've used my unit tests with the new 1.0.55.0 and without setting the JournalMode explicit. And luckily it has been fixed indeed.