OnUpgrade SQFLITE: Unhandled Exception: DatabaseException(table UsernameTable has no column named rememberMe (Sqlite code 1):

Question

Unhandled Exception: DatabaseException(table UsernameTable has no column named rememberMe (Sqlite code 1): , while compiling: INSERT OR REPLACE INTO UsernameTable (username, rememberMe) VALUES (?, ?), (OS error - 2:No such file or directory)) sql 'INSERT OR REPLACE INTO UsernameTable (username, rememberMe) VALUES (?, ?)' args [term@melfs, 1]}

I upgraded my DB. I added new column to UsernameTable Table. But It's not working. I have already so many records in this db. I can't drop So How I do add new column to exisiting db. I used Sqflite

 initDb() async {
    io.Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, "HelperDatabase.db");
    var theDb = await openDatabase(path, version: 4, onCreate: _onCreate, onUpgrade: _onUpgrade);
    return theDb;
  }

OnUpgrade method.

 void _onUpgrade(Database db, int oldVersion, int newVersion)async{
  if(oldVersion != 5){
      await db.execute("ALTER TABLE UsernameTable ADD COLUMN rememberMe INTEGER DEFAULT 0");
    }
  }

onCreate method

  void _onCreate(Database db, int version) async {
 await db.execute("""CREATE TABLE UsernameTable(username STRING)""");
}

Vitus

What value did you get in oldVersion and newVersion (_onUpgrade). I guess query in onUpgrade is not being called. – Necessarian

how to called onUpgrade? – Vitus

I added in initDB().. – Vitus

Try change version in openDatabase() to 5 or higher. Your DB seems to get wrong condition. – Necessarian

I did in my code. Isn't it? – Vitus

@Necessarian you are right. I added print() statement to _onUpgrade() method. It's not printing – Vitus

That's maybe your DB has a higher version than the version specified in openDatabase. In this case, onUpgrade will not be called. You can debug your db explained in this doc. github.com/tekartik/sqflite/blob/master/sqflite/doc/dev_tips.md – Necessarian

@BloodLoss, have you resolved this issue? Could you share some insights on the workaround you've tried? – Nielson

@MαπμQμαπkγVπ.0 yes I resolved it – Vitus

@BloodLoss could you present in the answer section the solution you've used to resolve your issue? – Nielson

Answer 1

You need to ensure that the db version matches to be able to access the database. Verify if the _onUpgrade() method - where a new version seems to be passed is being called. Automatic version management during open on sqflite is also available if that's something that you consider.

Twitter

Answer 2

Here is an easy to understand onUpgrade method I wrote:

Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
    for (var version = oldVersion + 1; version <= newVersion; version++) {
        switch (version) {
            case 1: {
                //Version 1 - no changes (won't enter here)
                break;
            }
            case 2: {
                await db.execute('ALTER TABLE xxx ADD COLUMN yyy INTEGER DEFAULT 0');
                break;
            }
        }
    }
}

If your database is currently on version 3, and you update to version 5 this will enter in case version 4 and version 5

So each time you do changes to the database and increment the version, just add the changes in the switch for that new version, keeping all the previous version changes in tact.

Bhutan