SQLite: No such table

Question

I am getting intermittent SQLiteExceptions claiming "no such table: HomepageSection". It is always the same table, but stopping the app and starting it again and the issue is gone. It sometimes happens if the database is missing, other times when it is present.

The tables are created when the database file is opened, using sqlite-nets SQLiteAsyncConnection.CreateTableAsync methods (and yes, they are awaited before this exception occurs).

I am connecting to the correct database file, I have checked this multiple times. I open up the path I get from the connection object directly in SQLite Browser, and the HomepageSection table isn't there.

The SQLiteException is thrown on the following query: insert OR REPLACE into "HomepageSection"("Title","Version","Id","Updated") values (?,?,?,?) (SQLite.cs, Prepare2(), line 2951)

There was no exceptions thrown on the line that allegedly created the table:

await _connection.CreateTableAsync<HomepageSection>();

Ideas for figuring this out?

Answer 1

I was running into the this same type of intermittent error. I've made the following changes, and so far no new reports of errors from customers.

I turned off sqlite autocreate database in the connection string.

connectionString="Data Source=default.db3; Foreign Keys=true; FailIfMissing=True;"

With FailIfMissing turned on, I was getting reports of cannot open database instead of no such table. It seems sqlite could not open my db3 file, so it creates a new database with no tables. It must be creating it in memory, as I could not find any new db files on my drive.

To correct this, I'm using a singleton pattern to make only one connection and keep it open throughout the life of the application. There are other ways to do this, but I have more experience with singleton. This not the full class code, just a few parts to get you started.

public static SQLite Instance
{
  get
  {
    if (instance == null) 
      {
        instance = new SQLite();
        mySQLiteConn = new SQLiteConnection(ConnectionString);
        mySQLiteConn.Open();
      }
      return instance;
  }
}

public void Execute(string SQL)
{
  using (SQLiteCommand myCommand = new SQLiteCommand(SQL, mySQLiteConn))
  {
    myCommand.ExecuteNonQuery().ToString();
  }
}

So to execute an insert, you would do:

SQLite.Instance.Execute("Insert .....;");