CODEWITHSUNDEEP
androidsqliteandroid-sqliteandroid-external-storage
VanessaF
VanessaF

Reputation: 793

Error message when opening external database in Android

I would like to load an external database in Android. I have used the approach from with tutorial Read External Database with SQLiteOpenHelper on Android. I created an folder called "assets" in src/main/assets and I inserted the database 'Test_DB' there. Unfortunately, when I start the app I get the error message:

Cannot open database 'C:/Users/user/Projects/AndroidStudioProjects/Bapp/Bapp_Projekt/app/src/main/assets/Test_DB.db': Directory C:/Users/user/Projects/AndroidStudioProjects/Bapp/Bapp_Projekt/app/src/main/assets doesn't exist

altough the database 'Test_DB.db' exists at the mentioned path. Do you have an idea what causes the problem and how I can read this external database file.

Here you can see the code of the 'DatabaseContext' class for reading the external database:

package com.example.td.bapp;


import android.content.Context;
import android.content.ContextWrapper;
import android.database.DatabaseErrorHandler;
import android.database.sqlite.SQLiteDatabase;
import android.os.Environment;
import android.util.Log;

import java.io.File;

public class DatabaseContext extends ContextWrapper {

    private static final String DEBUG_CONTEXT = "DatabaseContext";

    public DatabaseContext(Context base) {
        super(base);
    }

    @Override
    public File getDatabasePath(String name)  {

        String dbfile = "C:/Users/user/Projects/AndroidStudioProjects/Bapp/Bapp_Projekt/app/src/main/assets/" + name;
        if (!dbfile.endsWith(".db")) {
            dbfile += ".db" ;
        }

        File result = new File(dbfile);

        if (!result.getParentFile().exists()) {
            result.getParentFile().mkdirs();
        }

        if (Log.isLoggable(DEBUG_CONTEXT, Log.WARN)) {
            Log.w(DEBUG_CONTEXT, "getDatabasePath(" + name + ") = " + result.getAbsolutePath());
        }

        return result;
    }

    /* this version is called for android devices >= api-11. thank to @damccull for fixing this. */
    @Override
    public SQLiteDatabase openOrCreateDatabase(String name, int mode, SQLiteDatabase.CursorFactory factory, DatabaseErrorHandler errorHandler) {
        return openOrCreateDatabase(name,mode, factory);
    }

    /* this version is called for android devices < api-11 */
    @Override
    public SQLiteDatabase openOrCreateDatabase(String name, int mode, SQLiteDatabase.CursorFactory factory) {
        SQLiteDatabase result = SQLiteDatabase.openOrCreateDatabase(getDatabasePath(name), null);

        if (Log.isLoggable(DEBUG_CONTEXT, Log.WARN)) {
            Log.w(DEBUG_CONTEXT, "openOrCreateDatabase(" + name + ",,) = " + result.getPath());
        }
        return result;
    }
}

and this class is called in an other class 'DataBaseHelper' which extends SQLiteOpenHelper and that has the following constructor

public DataBaseHelper(@Nullable Context context, String name) {
   // super(context, DATABASE, null, 1);
    super(new DatabaseContext(context), name, null, 1);

}

I'll appreciate every copmment and will be quite thankful for your help.

I think the problem might be that I created the folder 'assets' on my own as it was not there before. How can I tell Android that there is this 'assets' folder that should be included in the App?

UPDATE: I did what MikeT adviced me and utilise SQLiteAssetHelper SQLiteAssetHelper. Now the database can be loaded. However, every query of the database does not return anything as if the database was empty. This is definitely not the case. For example the following query yiels a rowCount of 0 which is not true

public Cursor getDataDB_TableItemNamesByItemType(String itemType) {

    SQLiteDatabase db = this.getWritableDatabase();
    Cursor res = db.rawQuery("select * from " + TABLE_ITEM + " where "
            + ITEM_TYPE + " = '" + itemType+ "'", null);
    Log.e("LogTag", "res.getCount(): " + res.getCount());
    return res;

}

If I perform the exactly same query on the SQLdatabase I get positive row counts (depending on the argument itemType). Can you imagine what the problem is as to why my externally read database appears to be empty?

Upvotes: 0

Views: 783

Answers (2)

MikeT
MikeT

Reputation: 56953

Supplementary

In regards to the comments :-

Basically the database is changed all the time dynamically by the user as new items are added and removed based on the user iteraction. Further, the database should be changed from time to time externally. As stated before, I can easily archieve the update by just renaming the database and I was wondering whether a more convenient way exists for that (normally it should exist). However, when having a look at your suggested code it looks way more complex than just changing the database name. So I guess there is no easy solution for that.

I believe you need to consider two separate functionalities.

That is 1) maintaining the App user's data and 2) changing the App's supplied data.

You could separate them by using separate but connected databases, another way could be to differentiate the two types according to entity naming (an entity being tables triggers views etc).

  • e.g. you have the App supplied entities be called as_ or the user's entities called user_ or both.

When a new asset file is detected you could rename the existing file to old_TestDB.db and then copy the new file to TestDB.db. You could then delete all rows from the user's entities (perhaps not needed if you never supply user's data) and then connect the old_TestDB.db copy the data from the old to the new drop the connection to old_TestDB.db, and finally delete the old_TestDB.db file (or perhaps keep it just in case there are issue so you could have something to restore from).

Detecting the new asset file using file names could be problematic as you might end up with many old files in the assets folder. As such it could be simpler and more favourable for the App size if the same filename was used BUT the sqlite user_version was utilised.

The sqlite user_version is a version number stored in the header data of the database. It's 4 byte at an offset of 60 bytes into the header. You can access this without the overheads of opening the database by using standard IO (like when the asset file is copied). You'd compare this to the already existing database and if different handle the new asset file.

You can change the user version using the SQL PRAGMA user_version = n (some editors allow you to change this via a window) https://www.sqlite.org/pragma.html#pragma_user_version

An alternative approach would to not try to detect a changed asset, nor utilise the SQLite user_version but to always assume a changed asset and thus when a new version of the App is introduced to additionally increment the DATABASE VERSION that is passed to the SQLiteOpenHelper and to then perform the copy of the asset file and applying the user's data in the onUpgrade method. However, this can be fraught with errors/issues as the database has been opened (to extract the database's user version the user_version will be changed to the App's DATABASE VERSION).

However, you should design the database accordingly first before starting to write code.

Upvotes: 1

MikeT
MikeT

Reputation: 56953

Your main issue is with this line

String dbfile = "C:/Users/user/Projects/AndroidStudioProjects/Bapp/Bapp_Projekt/app/src/main/assets/" + name;

You are basically trying to tell the device to look at your source code rather than to look for the asset file (the database) in the devices assets folder.

Android Studio (assuming you are using it) when it makes the package that is installed places the file in the assets folder not in c:/Users/user/Projects ....

In the tutorial (which doesn't use the assets file) but places the file on the SD card it uses:-

String dbfile = sdcard.getAbsolutePath() + File.separator+ "database" + File.separator + name;

However, as you have copied the file into the assets folder of the project then what you do is copy the file (the database) from the assets folder to a location (you cannot use the database from the assets folder directly as it is compressed).

Here's a quick quide that shows how the above can be done (note that step10 is for the tables/columns and would have to be changed accordingly) how to launch app with SQLite darabase on Android Studio emulator?

An alternative would be to utilise SQLiteAssetHelper here's a guide that uses SQLiteAssetHelper .How to copy files from project assets folder to device data folder in android studio?

EXAMPLE based upon your code

Stage 1 - Create the external database

Using Navicat as the SQLite tool (my preferred tool)

The database was created with a single table namely item The table has 2 columns namely item_name and item_type

4 rows were inserted 2 with an item_type of type1 and 2 with an item_type of type2 as per :-

enter image description here

The database connection was closed and therefore saved and then the connection was reopened to confirm that the database is in fact populated, the connection was then closed again.

The existence of the database file was then confirmed to be at the appropriate location as per :- enter image description here

Stage 2 - Copy the database file into the assets folder

In this case the project has been called SO66390748 (the question #).

The assets folder was created in the Apps src/main folder as by default it doesn't exist and then the file copied into that folder noting that the size is as expected. enter image description here

The project file structure was then tested in Android Studio to confirm the existence of the database in the asset folder :- enter image description here

Stage 3 - Create the DatabaseHelper class as per

class DataBaseHelper extends SQLiteOpenHelper {

    private static final String TAG = "DBHELPER";
    public static final String DBNAME = "TestDB.db"; //<< Name of the database file in the assets folder
    public static final int DBVERSION = 1;
    public static final String TABLE_ITEM = "item";
    public static final String ITEM_NAME = "item_name";
    public static final String ITEM_TYPE = "item_type";

    SQLiteDatabase mDB;

    public DataBaseHelper(Context context) {
        super(context,DBNAME,null,DBVERSION);
        if (!ifDBExists(context)) {
            if (!copyDBFromAssets(context)) {
                throw new RuntimeException("Failed to Copy Database From Assets Folder");
            }
        }
        mDB = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        // Do NOTHING in here as the database has been copied from the assets
        // if it did not already exist
        Log.d(TAG, "METHOD onCreate called");
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        Log.d(TAG,"METHOD onUpgrade called");
    }

    public Cursor getDataDB_TableItemNamesByItemType(String itemType) {
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor res = db.rawQuery("select * from " + TABLE_ITEM + " where "
                + ITEM_TYPE + " = '" + itemType+ "'", null);
        Log.e("LogTag", "res.getCount(): " + res.getCount());
        return res;
    }

    /*
        Copies the database from the assets folder to the apps database folder (with logging)
        note databases folder is typically data/data/the_package_name/database
             however using getDatabasePath method gets the actual path (should it not be as above)
        This method can be significantly reduced one happy that it works.
     */
    private boolean copyDBFromAssets(Context context) {
        Log.d("CPYDBINFO","Starting attemtpt to cop database from the assets file.");
        String DBPATH = context.getDatabasePath(DBNAME).getPath();
        InputStream is;
        OutputStream os;
        int buffer_size = 8192;
        int length = buffer_size;
        long bytes_read = 0;
        long bytes_written = 0;
        byte[] buffer = new byte[length];

        try {

            is = context.getAssets().open(DBNAME);
        } catch (IOException e) {
            Log.e("CPYDB FAIL - NO ASSET","Failed to open the Asset file " + DBNAME);
            e.printStackTrace();
            return false;
        }

        try {
            os = new FileOutputStream(DBPATH);
        } catch (IOException e) {
            Log.e("CPYDB FAIL - OPENDB","Failed to open the Database File at " + DBPATH);
            e.printStackTrace();
            return false;
        }
        Log.d("CPYDBINFO","Initiating copy from asset file" + DBNAME + " to " + DBPATH);
        while (length >= buffer_size) {
            try {
                length = is.read(buffer,0,buffer_size);
            } catch (IOException e) {
                Log.e("CPYDB FAIL - RD ASSET",
                        "Failed while reading in data from the Asset. " +
                                String.valueOf(bytes_read) +
                                " bytes read successfully."
                );
                e.printStackTrace();
                return false;
            }
            bytes_read = bytes_read + length;
            try {
                os.write(buffer,0,buffer_size);
            } catch (IOException e) {
                Log.e("CPYDB FAIL - WR ASSET","failed while writing Database File " +
                        DBPATH +
                        ". " +
                        String.valueOf(bytes_written) +
                        " bytes written successfully.");
                e.printStackTrace();
                return false;

            }
            bytes_written = bytes_written + length;
        }
        Log.d("CPYDBINFO",
                "Read " + String.valueOf(bytes_read) + " bytes. " +
                        "Wrote " + String.valueOf(bytes_written) + " bytes."
        );
        try {
            os.flush();
            is.close();
            os.close();
        } catch (IOException e ) {
            Log.e("CPYDB FAIL - FINALISING","Failed Finalising Database Copy. " +
                    String.valueOf(bytes_read) +
                    " bytes read." +
                    String.valueOf(bytes_written) +
                    " bytes written."
            );
            e.printStackTrace();
            return false;
        }
        return true;
    }
    /*
    Checks to see if the database exists if not will create the respective directory (database)
    Creating the directory overcomes the NOT FOUND error
 */
    private boolean ifDBExists(Context context) {
        String dbparent = context.getDatabasePath(DBNAME).getParent();
        File f = context.getDatabasePath(DBNAME);
        if (!f.exists()) {
            Log.d("NODB MKDIRS","Database file not found, making directories."); //<<<< remove before the App goes live.
            File d = new File(dbparent);
            d.mkdirs();
            //return false;
        }
        return f.exists();
    }
}
  • As you can see your original getDataDB_TableItemNamesByItemType has been included unchanged.
  • As per the comments (I'd suggest reading them) the above is a little long-winded BUT this enables you to see what is happening. Obviously remove the logging before distributing the App.

Stage 4 - Invoke the database helper and extract the data from the database

In this case the App's main activity is used to invoke.

The activity used is :-

public class MainActivity extends AppCompatActivity {

    DataBaseHelper myDBHlpr;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        // Get an instance of the DatabaseHelper class (copy will be done IF DB does not exist)
        myDBHlpr = new DataBaseHelper(this); 

        // Get some data from the database using your method
        Cursor csr = myDBHlpr.getDataDB_TableItemNamesByItemType("type2");
        while(csr.moveToNext()){
            Log.d("DB_ROWINFO",
                    "ITEM NAME is " + csr.getString(csr.getColumnIndex(DataBaseHelper.ITEM_NAME))
                    + "ITEM TYPE is "
                    + csr.getString((csr.getColumnIndex(DataBaseHelper.ITEM_TYPE))
                    )
            );
        }
        // ========================================
        // ALWAYS CLOSE CURSORS WHEN DONE WITH THEM
        // ========================================
        csr.close();
    }
}

Stage 5 - The Results (the log)

The App if it exists is uninstalled then run and produces:-

03-06 09:32:52.759 5341-5341/a.a.so66390748 I/art: Rejecting re-init on previously-failed class java.lang.Class<androidx.core.view.ViewCompat$2>
03-06 09:32:52.787 5341-5341/a.a.so66390748 D/NODB MKDIRS: Database file not found, making directories.
03-06 09:32:52.787 5341-5341/a.a.so66390748 D/CPYDBINFO: Starting attemtpt to cop database from the assets file.
03-06 09:32:52.787 5341-5341/a.a.so66390748 D/CPYDBINFO: Initiating copy from asset fileTestDB.db to /data/user/0/a.a.so66390748/databases/TestDB.db
03-06 09:32:52.787 5341-5341/a.a.so66390748 D/CPYDBINFO: Read 8191 bytes. Wrote 8191 bytes.
03-06 09:32:52.805 5341-5341/a.a.so66390748 D/DBHELPER: METHOD onCreate called
03-06 09:32:52.811 5341-5341/a.a.so66390748 E/LogTag: res.getCount(): 2
03-06 09:32:52.811 5341-5341/a.a.so66390748 D/DB_ROWINFO: ITEM NAME is Item2ITEM TYPE is type2
03-06 09:32:52.811 5341-5341/a.a.so66390748 D/DB_ROWINFO: ITEM NAME is Item4ITEM TYPE is type2
03-06 09:32:52.822 5341-5355/a.a.so66390748 D/OpenGLRenderer: Use EGL_SWAP_BEHAVIOR_PRESERVED: true

The App is rerun a second time (not uninstalled) and the log is :-

03-06 09:35:37.876 5465-5465/a.a.so66390748 I/art: Rejecting re-init on previously-failed class java.lang.Class<androidx.core.view.ViewCompat$2>
03-06 09:35:37.908 5465-5465/a.a.so66390748 E/LogTag: res.getCount(): 2
03-06 09:35:37.908 5465-5465/a.a.so66390748 D/DB_ROWINFO: ITEM NAME is Item2ITEM TYPE is type2
03-06 09:35:37.908 5465-5465/a.a.so66390748 D/DB_ROWINFO: ITEM NAME is Item4ITEM TYPE is type2
03-06 09:35:37.956 5465-5498/a.a.so66390748 D/OpenGLRenderer: Use EGL_SWAP_BEHAVIOR_PRESERVED: true

i.e. The database, as it already exists and the database thus exists, doesn't copy the database but still extracts the data as expected.

Note the above has been written at my convenience so just Main activity and the database helper are used. You will obviously have to adapt the code accordingly

It is assumed that you followed the advice given in the comments and tried SELECT * FROM the_table_name (i.e. no WHERE clause). I says this as the query you have used is case sensitive and if the argument passed to your getDataDB_TableItemNamesByItemType method doesn't exactly match, then you would extract nothing. (e.g. passing Type2 instead of type2 shows 0 from the count)

Upvotes: 1

Related Questions