2

I have the following (simplified) code:

IDbConnection connection = new SQLiteConnection(GetConnectionString());
if (connection.State == ConnectionState.Closed)
  connection.Open();
using (IDbTransaction transaction = connection.BeginTransaction())
{
  using (IDbCommand cmd = transaction.Connection.CreateCommand())
  {
    cmd.CommandText = "DELETE FROM Records2 WHERE ClientIndex = @ClientIndex";
    AddParameter(cmd, "@ClientIndex", DbType.Int32, clientIndex);
    cmd.ExecuteNonQuery();
  }
  using (IDbCommand cmd = transaction.Connection.CreateCommand())
  {
    cmd.CommandText = "INSERT INTO Records2(ClientIndex, CandidateIndex, Name)";
    cmd.CommandText += " VALUES(@ClientIndex, @CandidateIndex, @Name)";
    AddParameter(cmd, "@ClientIndex", DbType.Int32, clientIndex);
    IDbDataParameter pIndex = AddParameter(cmd, "@CandidateIndex", DbType.Int32, null);
    IDbDataParameter pName = AddParameter(cmd, "@Name", DbType.AnsiString, null);
    int index = 0;
    foreach (Record record in records)
    {
      pIndex.Value = index++;
      pName.Value = record.Name;
      cmd.ExecuteNonQuery();
    }
  }
  using (IDbCommand cmd = transaction.Connection.CreateCommand())
  {
    cmd.CommandText = "UPDATE Records SET Status = @Status, UpdateDate = @UpdateDate WHERE ClientIndex = @ClientIndex";
    AddParameter(cmd, "@ClientIndex", DbType.Int32, clientIndex);
    AddParameter(cmd, "@Status", DbType.Byte, status);
    AddParameter(cmd, "@UpdateDate", DbType.DateTime, DateTime.Now);
    cmd.ExecuteNonQuery();
  }
  transaction.Commit();
}
connection.Close();

with:

private IDbDataParameter AddParameter(IDbCommand command, string paramName, DbType type, object value)
{
  IDbDataParameter parameter = command.CreateParameter();
  parameter.ParameterName = paramName;
  parameter.DbType = type;
  if (value != null)
    parameter.Value = value;
  else
    parameter.Value = DBNull.Value;
  command.Parameters.Add(parameter);
  return parameter;
}

All the ExecuteNonQueries work without problems, except for the last one. On my machine (running xp) I get the following exception, but on other machines (running windows 7) it works without problems, with the same code and the same database file.

SQLiteException (0x80004005): Unable to open the database file
  System.Data.SQLite.SQLite3.Reset(SQLiteStatement stmt) +375
  System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt) +199
  System.Data.SQLite.SQLiteDataReader.NextResult() +226
  System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave) +87
  System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior) +38
  System.Data.SQLite.SQLiteCommand.ExecuteNonQuery() +39

I tried using JournalMode = Perist in the connection string and i excluded the directory containing the database file from my anti-virus, but nothing helps.

I'm using the 3.5 Framework and my version of System.Data.SQLite is 1.0.66.0.

Any Ideas ?

Thanks in advance,

Marc

Improve this question
3
  • I forgot to mention that the exception only happens in an asp.net web application. The same code (with the same database file) in a winforms application works without problems. And the anonymous user used to run the web application has acces to the directory where the database file is located. If i put a breakpoint before the last ExecuteNonQuery, i see the journal file is created and the anonymous user has access to it. But still it this exception occurs when i continue with the last query. Commented Jul 15, 2011 at 13:28
  • Could it be that SQLite uses a worker thread and that IIS 5.1 (winxp) executes this thread with another user than IIS 6 or higher ? Commented Jul 15, 2011 at 14:22
  • I tried running the website with my normal windows user instead of the anonymous user and then the exception did not occur. But like I said, the anonymous user has full access to the database files too. Commented Aug 24, 2011 at 13:07

3 Answers 3

Reset to default
1

I was having the same issue with 1.0.87 and 1.0.89, and eventually (with the help of procmon) figured out the reason. It's actually a misleading error message, because its not the database file itself to which access is denied, but the SQLite statement journal.

Although the SQLite database file and journal are being created in the correct location, and the permissions on that folder allow access to Everyone, SQLite is still using %TEMP% for its statement journal. %TEMP% maps to a temp folder under the user profile of the identity running the service. So when a non-privileged client identity hits the service (and is impersonated inside and behind the service), that non-privileged identity can't create the etilqs_XXXXX temp folder used for the statement journal.

My fix was to set a PRAGMA on SQLiteConnections:

PRAGMA temp_store_directory='<same_path_as_the_database_file>'
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

I upgraded my System.Data.SQLite.dll from version 1.0.66.0 written by Robert Simpson (http://sqlite.phxsoftware.com) to version 1.0.74.0 written by the SQLite development team (http://system.data.sqlite.org) and that seems to solve the problem.

Improve this answer

2 Comments

very strange that this has to do with versions. I had the same with the latest version (1.0.82). When i reverted back to 1.0.80 everything is fine
Wow. I appear to have an eerily similar issue running 1.0.87. In my case the database is embedded in a WCF service. When accessing the service from a client running on the same machine - no problems. When accessing the service from a separate client machine via NETTCP, the final query in a transaction gives the same error in SQLite3.Reset(). The db file is in a location that the service identity has Full Control over.
0

I know this questions is old but I just had the same issue yesterday and was searching for answers. In my case my development environment was working and production was failing. The issue was the following line in my production web.config: 

<connectionStrings>
    <add name="SalesForceConnection"
        connectionString="metadata=res://SalesForceDataModel.csdl|res://SalesForceDataModel.ssdl|res://SalesForceDataModel.msl;
        provider=Devart.Data.Salesforce;
        provider connection string=&quot;
        User Id=myUserId;
        Password=myPassword;
        Security Token=myToken;
        Persist Security Info=True;
        Host=login.salesforce.com;
        Data Cache=c:\projects\salesforce\1.db;
        Metadata Cache=c:\projects\salesforce\1_Metadata.db&quot;"
        providerName="System.Data.EntityClient" />
</connectionStrings>

The data cache and metadata cache both point to directories in my development environment. Since the connection string is really long I missed these 2 settings. I changed the directories to ones on the production server and it all worked.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.