Closing database and then open it

[shariful2011]

I tried issue # 184 https://github.com/tekartik/sqflite/issues/184 without any success. Please help.

I need to close the database as I need to put the file in a zip file for uploading. Closing is successful but it's not opening afterword. Below is my code:
closeDB() async {
final db = await database;
await db.close();
}

openDB() async {
Directory documentsDirectory = await getApplicationDocumentsDirectory();
String path = join(documentsDirectory.path, "TestDB.db");
return await openDatabase(
path,
version: 1,
onOpen: (db) {},
);
}

for executing:
await DBProvider.db.closeDB();
encoder.addFile(File('${documentsDirectory.path}/TestDB.db'));
await DBProvider.db.initDB();// failed
await DBProvider.db.openDB();// failed

[alextekartik]

Not sure what you mean by failed. Is it throwing an exception?
Maybe you can try to catch it and display the error. What is initDB doing? Maybe you can try a simple close/open and see what is happening.

[shariful2011]

This is what I am getting:
E/flutter (13829): [ERROR:flutter/lib/ui/ui_dart_state.cc(148)] Unhandled Exception: DatabaseException(error database_closed)
E/flutter (13829): #0 _SqfliteDatabaseBase&Object&SqfliteDatabaseMixin.checkNotClosed
package:sqflite/src/database_mixin.dart:245
E/flutter (13829): #1 _SqfliteDatabaseBase&Object&SqfliteDatabaseMixin&SqfliteDatabaseExecutorMixin.rawUpdate
package:sqflite/src/database_mixin.dart:122
E/flutter (13829): #2 _SqfliteDatabaseBase&Object&SqfliteDatabaseMixin&SqfliteDatabaseExecutorMixin.rawDelete

Is my openDB method ok? initDB is as following:
initDB() async {
Directory documentsDirectory = await getApplicationDocumentsDirectory();
String path = join(documentsDirectory.path, "TestDB.db");
return await openDatabase(path, version: 1, onOpen: (db) {},
onConfigure: (Database db) async {
await db.execute('PRAGMA foreign_keys=ON');
}, onCreate: (Database db, int version) async {
await db.execute(_supplier); //_supplier is string creating supplier table
});
}

[alextekartik]

Please try to wrap your code with ``` (triple ticks) as it is hard to read. Are you sure you are getting the error during initDB? Is it throwing an exception here? it seems to happen in some other places where indeed the database has been closed before. It seems also that you are trying to open the database twice (in initDB and openDB).

I know it is a pain but can you try to reproduce the issue in a smaller scenario and share your project as it is impossible to help you more without being able to run the code.

thanks!

[shariful2011]

Below is my code:
DBProvider class:

class DBProvider {
  DBProvider._();

  static final DBProvider db = DBProvider._();

  Database _database;

  Future<Database> get database async {
    if (_database != null) return _database;
    // if _database is null we instantiate it
    _database = await initDB();
    return _database;
  }

  initDB() async {
    final String _supplier =
        'CREATE TABLE supplier (id INTEGER PRIMARY KEY, name VARCHAR(100), phone VARCHAR(30), email VARCHAR(100), notes TEXT, photo VARCHAR(255));';
    final String _item =
        'CREATE TABLE item (id INTEGER PRIMARY KEY, supplier_id INTEGER REFERENCES supplier ON DELETE CASCADE, notes TEXT);';
    final String _item_detail =
        'CREATE TABLE item_detail (id INTEGER PRIMARY KEY, item_id INTEGER REFERENCES item ON DELETE CASCADE, photo VARCHAR(255), selected int default 0);';
    final String _state =
        'CREATE TABLE state (id INTEGER PRIMARY KEY, supplier_id INTEGER);';

    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, "TestDB.db");
    return await openDatabase(path, version: 1, onOpen: (db) {},
        onConfigure: (Database db) async {
      await db.execute('PRAGMA foreign_keys=ON');
    }, onCreate: (Database db, int version) async {
      await db.execute(_supplier);
      await db.execute(_item);
      await db.execute(_state);
      await db.execute(_item_detail);
    });
  }

  closeDB() async {
    final db = await database;
    await db.close();
  }

  openDB() async {
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, "TestDB.db");
    return await openDatabase(
      path,
      version: 1,
      onOpen: (db) {},
      onConfigure: (Database db) async {
        await db.execute('PRAGMA foreign_keys=ON');
      },
    );
  }
 //this methods fire the exception after I called closeDB() but before it fires perfectly
  Future<int> getStateId(int id) async {
    final db = await database;
    db.delete("state");

    var tableState = await db.rawQuery("SELECT MAX(id)+1 as id FROM state");
    int id = tableState.first["id"];
    //insert to the table using the new id
    var raw = await db.rawInsert(
        "INSERT Into state (id,supplier_id)"
        " VALUES (?,?)",
        [id, id]);
    print('stateid=$raw');
    return raw;
  }
}

This is the calling method:

  void _generateArchive() async {
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    ZipFileEncoder encoder = ZipFileEncoder();

    encoder.create(createdFileName(documentsDirectory.path, 'product.zip'));
    await DBProvider.db.closeDB();
    //await DatabaseManager.close();
    encoder.addFile(File('${documentsDirectory.path}/TestDB.db'));
    await DBProvider.db.openDB();
    //after that some of code for making the zip files
}

[alextekartik]

It seems _database is only set the first time. Maybe you should set it to null when closing the database, otherwise it will always return the old (closed) instance.

[shariful2011]

Many many thanks it wroked after I set _database = null.

[Adnantjexx]

where i write _database = null

[zAndreyG]

Inside the close method, like this:

Future close() async { var dbClient = await db; _database = null; return dbClient.close(); }

[UmarFarooqApp]

I also facing that problem any solution