SQLiteLog no such table is being reported when I try to query the database

[Mohammed Azeem]

Hello, good afternoon,

I'm learning to ship an existing SQLite database with an android project.

I used this this stackoverflow answer exactly

I have successfully copied over a simple database file from the projects asset's folder into the emulated devices files/databases/ directory.

The IDE's Device File Explorer correctly shows the database file in the correct directory with the correct name.

I created the simple database using the SQLiteStudio

When run the app, the logcat (where error/info/debug messages are logged reports:
        E/SQLiteLog: (1) no such table: table1 in "SELECT value FROM table1"

But there clearly is a table called table1 in the database file - when I re-examine it with SQLiteStudio.

What's going wrong?  I've been struggling with it for weeks, stackoverflow is silent on my post!

[Tim Holloway]

My best guess is that the database isn't where you think it is. It's not really a good idea to think of Android as a file-based OS.

I've never copied over SQLite databases to an Android project. I've always relied on the Android app itself to create the database and its schema if needed, as well as to ensure that the schema gets updated if a newer release of the app requires it.

[Al Hobbs]

I'm guessing you didn't do step 3 right.  If it says no table then it is probably finding the file but the contents are not right.

[Mohammed Azeem]

So Tim Holloway, you were right.

I have resolved this issue through my own experimenting and happening on this stackoverflow  post.

App Specific Internal Storage

Every app has a data directory, :/data/app_package/ which is private to it only. The path to this is obtained by calling getDataDir() on the app's context object, i.e context.getDataDir().  The docs for android class Context say that the data directory should not be used directly

A sub-directory of the data directory is the files directory  (:/data/app_package/files/)  obtained by context.getFilesDir(). Official android docs/guides say this where files private should be stored

Database files are stored in subdirectory :/data/app_package/databases. This is where SQLiteOpenHelper. getWritableDatabase()  expects to find them.

However, creating a subdirectory  :data/app_package/databases/  programmatically fails and throws an IOException

My Solution
After reading docs for android class Context  (the context is like the "environment" e.g. storage, device, memory, wifi etc within which the app is created:

Method   context.getDatabasePath(String name)  abstracts the creation of the databases directory and the database file of the specified name within that directory

The AssetCopier, class I defined in my experiments (see below) , therefore copies the database from assets folder into the above location

The log message (equivalent to System.out.println) confirms that the db file is copied and returns its file path.

public class AssetCopier {

    Context context;
    InputStream in;
    OutputStream out;
    String filenameA;
    String filenameB;
    File a;
    File b;

    static final String TAG = "De-bug Message : ";

    public AssetCopier(Context context, String filenameA, String filenameB) {
        this.context = context;
        this.filenameA = filenameA;
        this.filenameB = filenameB;

        //String path = context.getDataDir().getPath().toString() + "/databases/" + filenameB;
        //b = new File(path);
        // b = new File(context.getDataDir().getPath() + "/databases" + filenameB);
        b = context.getDatabasePath(filenameB);


        try {
            copyFromAssets(filenameA);
        } catch (IOException e) {
            Log.e(TAG, " Exception caught in copyFromAssets()");
        }

    }

    public void copyFromAssets(String filenameA) throws IOException {
        AssetManager assetManager = context.getAssets();
        in = assetManager.open(filenameA);

        out = new FileOutputStream(b);

        try {
            int n;
            while ((n = in.read()) != -1) {
                out.write(n);
            }
        } catch (IOException e) {
            Log.e(TAG, " Exception caught in copyFromAssets()");
        } finally {
            if (in != null) {
                in.close();
            }

            if (out != null) {
                out.close();
            }
        }
        Log.e(TAG, " File copied.");
        Log.e(TAG, " the path to the file is :" + b.getPath());
    }

}

Then in the main acitivity - which I use just as a starting point.

public class MainActivity extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        AssetCopier assetCopier = new AssetCopier(getApplicationContext(), "testDB.db", "testDB.db");
    }
}

Logcat output:

2022-08-30 15:03:55.065 15697-15697/com.mo.copydbfromassets E/De-bug Message ::  File copied.
2022-08-30 15:03:55.065 15697-15697/com.mo.copydbfromassets E/De-bug Message ::  the path to the file is :/data/user/0/com.mo.copydbfromassets/databases/testDB.db

[Tim Holloway]

Yup. Again, though, it's really not a good idea to think of Android as an OS that supports or works with files. It's not designed that way. Using the standard database functions you never have to worry about where the database is located or even if it's in a file, a BLOB, Amazon S3 bucket or something even more esoteric. Android does the worrying and handles it for you.

This is true even when running an Android emulator on a desktop and more so when running on an actual native Android device.