Getting SQLiteException while accessing the database created with Flutter in Android native

I am getting an error no such table in Android native. The database is created with Flutter sqflite package. The database exists in the dir. The database and table names are checked and confirmed more than once. I don't know what is the problem with the native code. It is working quite well with Dart/Flutter.

Is it like we can't access the database created with Dart/Flutter in Android native or the approach is somehow different?

Code on Android side.

public static Cursor getDB(Context context){
        myDB = SQLiteDatabase.openDatabase(context.getDatabasePath(DB_NAME).getAbsolutePath(), null, SQLiteDatabase.OPEN_READWRITE);
        String query = "SELECT * FROM todo";
        return myDB.rawQuery(query, null);
}

Database creation Dart code

final String _DB_NAME = "todos.db";
final String tableTodo = 'todo';
final String columnId = '_id';
final String columnTitle = 'title';

  Future<Database> initializeDB() async {
    String dbDir = await getDatabasesPath();
    String dbPath = dbDir + _DB_NAME;
    var database = await openDatabase(dbPath, version: 1, onCreate: _createDB);
    return database;
  }

  void _createDB(Database db, int version) async {
    await db.execute(
        'create table $tableTodo ($columnId INTEGER PRIMARY KEY AUTOINCREMENT, $columnTitle TEXT NOT NULL)');
  }

Thanks in advance!

Solution

I am getting an error no such table in Android native.

The Android SQLite API's openDatabase method will create an empty database at the given path if the database does not exist.

The table not found is symptomatic of no file existing at the specified path (the root cause is very likely in the Flutter code as is described later).

Ideally you should check to see if the file exists before opening the database.

so along the lines of :-

public static Cursor getDB(Context context){
     if (context.getDatabasePath(DB_NAME).exists()) {
        myDB = SQLiteDatabase.openDatabase(context.getDatabasePath(DB_NAME).getAbsolutePath(), null, SQLiteDatabase.OPEN_READWRITE);
        String query = "SELECT * FROM todo";
        return myDB.rawQuery(query, null);
    } else {
        // handle the file not existing
    }
}

The database exists in the dir.

As explained the database will exist after the fact. The above code checks for the file (it doesn't go further and check if the file returned is a normal file ( File's isFile() method) or other checks that could be made)

other checks could be :-
- checking the file's length should be at least 4k, at least 8k if it has a user defined table and as you have used AUTOINCREMENT (which creates another internal table sqlite_sequence) at least 12k (at least 4k per table and that includes the always existing sqlite_master table).
- opening the file and checking the header first 16 bytes should be as per SQLite Database File Format
  - Every valid SQLite database file begins with the following 16 bytes (in hex): 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00. This byte sequence corresponds to the UTF-8 string "SQLite format 3" including the nul terminator character at the end.
- After opening the database using the openDatabase method you could check sqlite_master to see if the table(s) exist as expected.

Is it like we can't access the database created with Dart/Flutter in Android native or the approach is somehow different?

From a very quick scan of SQlite plugin for Flutter it says :-

A SQLite database is a file in the file system identified by a path. If relative, this path is relative to the path obtained by getDatabasesPath(), which is the default database directory on Android and the documents directory on iOS.

This indicates that Flutter uses the getDatabasePath() method, so it would appear that they are compatible, and probably that Flutter utilises the underlying native android sqlite api.

Looking at the documentation it appears that you are not opening the database as suggested. The example given uses :-

// Get a location using getDatabasesPath
var databasesPath = await getDatabasesPath();
String path = join(databasesPath, 'demo.db');

Which at a guess adds the file seprator and then demo.db so instead of data/data//databasetodos.db (as would appear to be generated by your code) the example above would use (if demo.db is changed to todos.db) /data/data//databases/todos.db

you may wish to check to see if such a file exists and perhaps move and rename it accordingly (this could save having to re-generate any data, you may not access to the file though)

As such I believe that your Flutter code should be :-

final String _DB_NAME = "todos.db";
final String tableTodo = 'todo';
final String columnId = '_id';
final String columnTitle = 'title';

  Future<Database> initializeDB() async {
    String dbDir = await getDatabasesPath();
    String dbPath = join(dbDir,_DB_NAME);
    var database = await openDatabase(dbPath, version: 1, onCreate: _createDB);
    return database;
  }

  void _createDB(Database db, int version) async {
    await db.execute(
        'create table $tableTodo ($columnId INTEGER PRIMARY KEY AUTOINCREMENT, $columnTitle TEXT NOT NULL)');
  }