Unusual behaviour in app using existing database

Question

I'm experimenting with using an existing database that is provided to the app in the Assets folder.

When I run the app for the very first time, I get an NullPointerException on an InputSteam object.

If I run the app a second time, then the input stream is not null but this time, I get an SQLiteException: no such table.

Very unusual, I'm wondering if anyone could help find the cause.

In this project, I created a simple SQLite database file and stored it in the Assets folder. It is called Customers.db and contains one table called CustomerList. The table columns are ID (integer primary key), CustomerName and Country.
In the DatabaseHelper object, the method loadDatabase() loads the database from the assets folder into the phone's internal memory.
DatabaseHelper method getRecords() returns an Array of Customer objects and these are the fields of these customer objects are listed in a RecyclerView in the MainActivity. To simply the experiment, as a first step, the getRecords() method return all the rows of the table.

When run for the first time the following exception is reported:

java.lang.RuntimeException: Unable to start activity ComponentInfo{com.mo.useexistingdatabasedemo/com.mo.useexistingdatabasedemo.MainActivity}: java.lang.NullPointerException: Attempt to invoke virtual method 'int java.io.InputStream.read(byte[], int, int)' on a null object reference

And the second time it's run, the input stream seems to be no longer null, but instead an sqliteexception is reported:

java.lang.RuntimeException: Unable to start activity ComponentInfo{com.mo.useexistingdatabasedemo/com.mo.useexistingdatabasedemo.MainActivity}: android.database.sqlite.SQLiteException: no such table: CustomerList (code 1 SQLITE_ERROR): , while compiling: SELECT * FROM CustomerList

In ,my project class DatabaseHelper is defined as follows (and its methods are called from the MainActivity).

package com.mo.useexistingdatabasedemo;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import androidx.annotation.Nullable;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.nio.Buffer;
import java.util.ArrayList;

public class DatabaseHelper extends SQLiteOpenHelper {

    public static ArrayList<Customer> arrayList = new ArrayList<>();

    public static final String DATABASE_NAME = "Customers.db";
    public static final String TABLE_NAME = "CustomerList";
    public static final int DATABASE_VERSION = 1;
    public static final String DATABASE_DIRECTORY_PATH = "data/data/com.mo" +
            ".useexistingdatabasedemo/databases";
    public static final String DATABASE_FILE_PATH = DATABASE_DIRECTORY_PATH + DATABASE_NAME;

    Context context;
    InputStream inputStream;
    OutputStream outputStream;
    Buffer buffer;


    public static final String SELECT_ALL_TABLE = "SELECT * FROM " + TABLE_NAME;

    public DatabaseHelper(@Nullable Context context) {
        super(context, DATABASE_NAME, null, 2);
        this.context = context;

    }

    public void loadDatabase() {
            String path = DATABASE_DIRECTORY_PATH + DATABASE_NAME;

            File dbFile = new File(path); // the descriptor of a file in internal memory to which
            // we will write the db in the assets folder.
                // if the database isn't already in internal memory, copy it over from assets.
            if (!dbFile.exists()) {
                try {
                    inputStream = context.getAssets().open(DATABASE_NAME);
                } catch (IOException e) {
                    Log.e("IOException input stream: ",
                            "Exception opening input stream " + e.getMessage());
                }
                OutputStream outputStream = null;
                try {
                    outputStream = new FileOutputStream(path);
                } catch (FileNotFoundException e) {
                    Log.e("IOException, output stream: ",
                            "Exception creating output stream " + e.getMessage());
                }
                // create a buffer of 1024 bytes length
                byte[] buffer = new byte[1024];
                int len;

                try {
                    while ((len = inputStream.read(buffer, 0, 1024)) > 0)
                        outputStream.write(buffer, 0, len);
                    outputStream.flush();
                    inputStream.close();
                } catch (IOException e) {
                    Log.e("IOException", "Exception occurred in while block" + e.getMessage());
                }
            }
            Log.i("Load database", "The method public loadDatabase() executed succesffully");

    }


    @Override
    public void onCreate(SQLiteDatabase db) {

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public ArrayList getRecords() {
        SQLiteDatabase database = getWritableDatabase();
        Log.i("The attached database is :", database.getAttachedDbs().toString());

        Cursor cursor = database.rawQuery("SELECT * FROM " + TABLE_NAME, null);
        while (cursor.moveToNext()) {
            Customer customer = new Customer();
            customer.customerName = cursor.getColumnName(1).toString();
            customer.country = cursor.getColumnName(2).toString();
            arrayList.add(customer);
        }
        return arrayList;

    }
}

By the way, the database is stored in a sub-folder called databases. assets>databases. — azeemofwalsall, Sep 02 '21 at 20:14

MikeT · Accepted Answer · 2021-09-02T21:39:14.050

Unusual behaviour in app using existing database

The Issue(s) Not at all unusual. You are not copying the database to it's expected location, which is data/data/your_package/databases (directory) However no need to know or even code that (see later).

First run and the copy fails BUT the file is created.

Second run as the file exists, no attempt is made to copy. So theSQLiteOpenHelper subclass can't find the database, as it's not in the expected location, so it invokes the onCreate method and creates the database without any tables and hence the table not found.

The Fix

I would suggest NOT hard coding the path but getting the path via the Context's getDatabasePath method. e.g.

 String dbpath = context.getDatabasePath(name).getPath();

or as a File :-

 File  db = context.getDatabasePath(name);

After acquiring I suggest checking if the parent directory exists and if not then issuing a mkdirs so that the databases folder is created if it doesn't exist (for older API's it will not and it's non existence prevents the copy working but with the file existing, I believe I saw that later versions create the directory). e.g. something like :-

    File db = context.getDatabasePath(name);
    if (!db.getParentFile().exists()) db.mkdirs();
    String dbpath = db.getPath(); // may not be needed as the File object is what is then required

Here's is an example of a pretty bulletproof SQLiteOpenHelper subclass that will copy a pre-existing database.

Unusual behaviour in app using existing database

1 Answers1