Unable to read SQLite database from app

from the CommonsWare Community archives

At February 3, 2021, 5:30pm, AlanHaden asked:

My app was working fine until the latest Android update and then everything broke. You have already helped me repair the issues I had reading and writing CSV files with OpenCSV.
However, the database still does not work.
I’m assuming that the problem is very similar.

This copies the db file in from an external source…

    public boolean copyFileIn(Context context)
{
    try
    {
        File sd = new File(Environment.getExternalStorageDirectory() + "/Android/data/com.gmail.alan.j.haden.bestmobile/files");
        File data = new File(Environment.getDataDirectory() + "/user/0/com.gmail.alan.j.haden.bestmobile");
        SQLiteDatabase db = this.getReadableDatabase();
        String dbPath = db.getPath();


        if (sd.canWrite())
        {
            String currentDBPath = "/data/com.gmail.alan.j.haden.bestmobile/databases/ProductData.db";

            String backupDBPath = "/BEST/ProductDB/ProductData.db";
            File currentDB = new File(data, currentDBPath);

            File backupDB = new File(sd, backupDBPath);

            if (currentDB.exists()) {
                FileChannel src = new FileInputStream(backupDB).getChannel();
                FileChannel dst = new FileOutputStream(currentDB).getChannel();
                dst.transferFrom(src, 0, src.size());
                src.close();
                dst.close();
                System.out.println("Import successful");
                return true;
            } else {
                System.out.println("Can't find currentDBPath");
            }

        }
    }
    catch (Exception e) {
        Log.w("Settings Backup", e);
    }
    return false;
}

and this exports it…

public boolean copyFileOut(Context context)
{
try
{
    File sd = context.getExternalFilesDir(null);
    File data = context.getDataDir();
    SQLiteDatabase db = this.getReadableDatabase();
    String dbPath = db.getPath();

        if (sd.canWrite())
        {
            String currentDBPath = "/data/com.gmail.alan.j.haden.bestmobile/databases/ProductData.db";
            System.out.println("dbPath = " + dbPath);
            String backupDBPath = "/BEST/ProductDB/ProductData.db";
            File currentDB = new File(data, currentDBPath);
            System.out.println("currentDBPath = " + currentDBPath);
            File backupDB = new File(sd, backupDBPath);

            if (currentDB.exists()) {
                FileChannel src = new FileInputStream(currentDB).getChannel();
                FileChannel dst = new FileOutputStream(backupDB).getChannel();
                dst.transferFrom(src, 0, src.size());
                src.close();
                dst.close();
                System.out.println("Export successful");
                return true;
            } else {
                System.out.println("Can't find currentDBPath");
            }

        }
    }
    catch (Exception e) {
        Log.w("Settings Backup", e);
    }
    return false;
}

After I’ve got this part working again, I’ll be able to see if the database can still be read.


At February 3, 2021, 6:06pm, mmurphy replied:

From prior conversations, this app appears to be just for your personal use. That means we do not have to worry about Play Store restrictions and the like.

You have not indicated what specific messages you are seeing in Logcat from any of this. That is really one of the first things that you need to do when running into problems: if you are logging information to Logcat (System.out.println() and Log.w() in your code), see what logs you are getting.

I’ll be honest: your code is… not great. It seems based on some ancient awful examples. I am not in position to completely clean it up, but by eyeball, I would switch copyFileIn() to:

public boolean copyFileIn(Context context)
{
    try
    {
        File sd = context.getExternalFilesDir(null);
        String backupDBPath = "BEST/ProductDB/ProductData.db";
        File currentDB = context.getDatabasePath("ProductData.db");
        File backupDB = new File(sd, backupDBPath);

        if (backupDB.exists()) {
            FileChannel src = new FileInputStream(backupDB).getChannel();
            FileChannel dst = new FileOutputStream(currentDB).getChannel();
            dst.transferFrom(src, 0, src.size());
            src.close();
            dst.close();
            System.out.println("Import successful");
            return true;
        } else {
            System.out.println("Can't find currentDBPath");
        }
    }
    catch (Exception e) {
        Log.w("Settings Backup", e);
    }
    return false;
}

…and copyFileOut() to something like:

public boolean copyFileOut(Context context)
{
    try
    {
        File sd = context.getExternalFilesDir(null);
        String backupDBPath = "BEST/ProductDB/ProductData.db";
        File currentDB = context.getDatabasePath("ProductData.db");
        File backupDB = new File(sd, backupDBPath);

        if (currentDB.exists()) {
            FileChannel src = new FileInputStream(currentDB).getChannel();
            FileChannel dst = new FileOutputStream(backupDB).getChannel();
            dst.transferFrom(src, 0, src.size());
            src.close();
            dst.close();
            System.out.println("Export successful");
            return true;
       } else {
            System.out.println("Can't find currentDBPath");
        }
    }
    catch (Exception e) {
        Log.w("Settings Backup", e);
    }
    return false;
}

Of note:

Beyond that, though, these revised methods should copy the files between the same two locations as before.

Try this stuff. If it does not work, reply on this thread with as much specifics as possible as to what your symptoms are. For example, if you are crashing or are seeing your log messages in Logcat, that would be useful to know.


At February 3, 2021, 6:12pm, AlanHaden replied:

I/System.out: Can't find currentDBPath

Although I know it’s in the directory.
That was an attempt to import the db.


At February 3, 2021, 6:25pm, mmurphy replied:

One possibility is that com.gmail.alan.j.haden.bestmobile is not your application ID. For example, perhaps you changed it somewhere along the line and still kept trying to use the old directory. That is one of the reasons why I recommend not hard-coding paths, but instead using methods like context.getExternalFilesDir(null).


At February 3, 2021, 6:34pm, AlanHaden replied:

Screenshot 2021-02-03 183101 !

As you can see, its actually in the directory.
Does that help?


At February 3, 2021, 6:35pm, AlanHaden replied:

Screenshot 2021-02-03 183247

Its also within the app directory.
Although it still isn’t being accessed by the app.


At February 3, 2021, 6:52pm, mmurphy replied:

I do not know if you are using my suggested replacement code or your original code. You might want to improve your logging to log specifically the value that was not found.

For example, in your original code, you are trying to blend:

        File data = new File(Environment.getDataDirectory() + "/user/0/com.gmail.alan.j.haden.bestmobile");

and:

            String currentDBPath = "/data/com.gmail.alan.j.haden.bestmobile/databases/ProductData.db";

via:

            File currentDB = new File(data, currentDBPath);

That does not feel like that would work. In your original code, you are looking there for an import operation (which did not make sense, which is why I changed it in my replacement). You might consider logging the value for currentDB in your System.out.println("Can't find currentDBPath");, so you can see what you are using.


At February 3, 2021, 6:54pm, AlanHaden replied:

That wasn’t actually the original code. I’ve already had a couple of days tampering with it in desperation to get it working again.
I had tried to bring in the way showed me but I failed.


At February 3, 2021, 7:00pm, AlanHaden replied:

I’m going to have to abandon this for tonight as I need to get an early night. I’m up at 4am tomorrow. Sorry.


At February 4, 2021, 6:25am, AlanHaden replied:

I’m trying to think this through logically.
Ignoring the code I posted earlier (because I’d spent a couple of days tampering with it and really messed it up), the app was working until the update.

So far in this discussion, you have repaired the damage I did trying to fix it myself. So, I’m now back at square one with regards to the database not working.

  1. I can see that the database is present in both directories. The apps own folder and the public one.

  2. Neither the copy in or the copy out feature work.

  3. The app can’t access the database in order to read, display and work with it.

When I get home from work this evening, I will post the manifest so you can see if there’s anything wrong there.
Is there anything else you think would be helpful to see?


At February 4, 2021, 11:36am, mmurphy replied:

Do you know what the update was from and to?

If you have a typical Android Studio project, your source code and stuff is in an app/ directory (“the app module”). In the app/ directory there is a build.gradle file. Seeing that would help.


At February 4, 2021, 1:56pm, AlanHaden replied:

Do you know what the update was from and to?

I don’t know what it was from but my current version is Android 11, 1 January 2021 patch.

If you have a typical Android Studio project, your source code and stuff is in an app/ directory (“the app module”). In the app/ directory there is a build.gradle file. Seeing that would help.

plugins {
id 'com.android.application'

}

android {
compileSdkVersion 30
buildToolsVersion “30.0.2”

defaultConfig {
    applicationId "com.gmail.alan.j.haden.bestmobile"
    minSdkVersion 27
    targetSdkVersion 30
    versionCode 1
    versionName "1.0"

    testInstrumentationRunner "androidx.test.runner.AndroidJUnitRunner"
}

buildTypes {
    release {
        minifyEnabled false
        proguardFiles getDefaultProguardFile('proguard-android-optimize.txt'), 'proguard-rules.pro'
    }
}
compileOptions {
    sourceCompatibility JavaVersion.VERSION_1_8
    targetCompatibility JavaVersion.VERSION_1_8
}

}

dependencies {

implementation 'androidx.appcompat:appcompat:1.2.0'
implementation 'com.google.android.material:material:1.2.1'
implementation 'androidx.constraintlayout:constraintlayout:2.0.4'
implementation 'androidx.navigation:navigation-fragment:2.3.3'
implementation 'androidx.navigation:navigation-ui:2.3.3'
implementation 'androidx.lifecycle:lifecycle-livedata-ktx:2.2.0'
implementation 'androidx.lifecycle:lifecycle-viewmodel-ktx:2.2.0'
implementation 'com.google.android.gms:play-services-maps:17.0.0'
implementation 'com.google.android.gms:play-services-location:17.1.0'
implementation 'androidx.legacy:legacy-support-v4:1.0.0'
testImplementation 'junit:junit:4.13.1'
androidTestImplementation 'androidx.test.ext:junit:1.1.2'
androidTestImplementation 'androidx.test.espresso:espresso-core:3.3.0'
implementation 'com.opencsv:opencsv:5.3'
implementation 'net.sourceforge.jexcelapi:jxl:2.6.12'

}

I’m about to email a copy of the two files not currently working. Please feel free to use them as an example of how not use Stackoverflow.


At February 4, 2021, 2:15pm, mmurphy replied:

The key element from there that I wanted to see was:

applicationId "com.gmail.alan.j.haden.bestmobile"

I was concerned that perhaps your hard-coded paths were not matching that applicationId.

It is not obvious to me why you would be having problems on Android 11. While there were storage changes, the two places you are working with should still be accessible without permissions, particularly in the code that I posted earlier (which, based on an email you sent me, seems to be what you are using now).

Let me run some experiments. I will try to get to that later today my time.


At February 4, 2021, 2:18pm, AlanHaden replied:

Thank you very much Mark.
The email I’ve sent might help.


At February 5, 2021, 12:23am, mmurphy replied:

OK, I was unable to reproduce the problem.

https://commonsware.com/misc/HadenTest.zip contains the project that I used. All of the code, including lightly-tweaked renditions of copyFileIn() and copyFileOut(), is in MainActivity. The only changes that I made were to add mkdirs() calls, to create the directories to which the files would be copied.

I then:

IOW, everything worked fine.

So, I cannot explain why you are having problems, other than it does not seem to be tied to this code.

You might try importing my test project into Android Studio (assuming that you are on 4.1.2 or newer) and try it out. If it works, then something somewhere got seriously screwed up with your file permissions on your device, and other than to uninstall/reinstall the app, I don’t know how to fix that.


At February 5, 2021, 6:05am, AlanHaden replied:

Importing your project into Android Studio gives me this…

A problem was found with the configuration of task ':app:processDebugManifest' (type 'ProcessMultiApkApplicationManifest').
> File 'F:\HadenTest\app\build\intermediates\merged_manifest\debug\out\AndroidManifest.xml' specified for property 'mainMergedManifest' does not exist.

It’s not the first time I’ve encountered this issue while importing projects.
And I’ve never been able to create a new project. The way Ive found to get things working again was to change the setting to…
New Project settings
That was a solution I found on Stackoverflow a while back and until now, it’s been working.

After doing that, your project ran but failed with the usual System.out message.

I/System.out: Can't find currentDBPath

So now I’m thinking that maybe there’s a problem with the way my Android Studio is creating my projects.
I’ve decided to call my office and take today off as Holiday. Hopefully, it’ll give me time to get this working again.
I know it’s very late where you are (probably around 1am) so I’m going to spend my time today trying to figure out how to get Android studio to use the latest version of everything.


At February 5, 2021, 10:56am, AlanHaden replied:

So far I’ve changed the settings to Android Gradle Plugin Version 4.1.2 and the Gradle Version to 6.8.1
Clean the project and rebuild.
Now when I attempt to build, I get…
Execution failed for task ‘:app:processDebugMainManifest’.

com.android.manifmerger.ManifestMerger2$MergeFailureException: java.io.FileNotFoundException: F:\HadenTest\app\build\intermediates\navigation_json\debug\navigation.json (The system cannot find the path specified)

Googled the error message.
After scouring Google for answers, most of which are very old, all I can find are suggestions that the solution is to downgrade to the settings I had before.
So moving on and trying to find other solutions. I’ve now tried the following…

Uninstalled the SDK for Android 11 R and reinstalled, twice. No change.

Uninstalled and reinstalled Android Studio. No change.

Searched for a UK phone number for help and support. There doesn’t appear to be one.

Read through the Known Issues with Android Studio and Android Gradle Plugin. Found nothing that appears to relate to my problem.

Next, I’ve tried deleting the AppData/Roaming/Google folder. This should reset Android Studio to its default state and clear out things I’ve messed up. Gone through the setup process again. No change.

Currently banging my head on the desk and wondering if I’ve wrecked everything beyond repair.
Going to have coffee and cry for a bit while wishing that time zones weren’t a thing.


At February 5, 2021, 12:43pm, mmurphy replied:

That is not a good sign for the state of your development machine.

Note that this is only likely to work if you are running Android Studio 4.1.2. Generally, we set the plugin to match the IDE that we are using, and Gradle to match the plugin.

There is no navigation.json in the project that I supplied to you, so I have no idea where that error message is coming from.

Your development machine does appear to be having its issues. There is not very much that I can do about that — for example, I have not used Windows for software development in a couple of decades.


So, on the whole, I am back to where I was in my last message. As far as I can tell, your code is not the problem.

FWIW, https://commonsware.com/misc/HadenTest-debug.apk contains a compiled edition of the test project. You could uninstall the copy of the test app that you tried, “sideload” this APK, and try running through my steps again. If this works, then the problem purely is your development machine. If this fails, then either there is a problem with your device or there is a discrepancy in the test steps.


At February 5, 2021, 1:28pm, AlanHaden replied:

I am using Android Studio 4.1.2.
Current settings are…
Android Gradle Plugin Version 4.1.2
Gradle Version 6.8.1

Just attempted to rebuild the project after another reinstall of Android Studio and…

F:\HadenTest\app\build\intermediates\navigation_json\debug\navigation.json (The system cannot find the path specified)

At February 5, 2021, 1:30pm, AlanHaden replied:

I’m now going to reinstall JRE.
And yes, I imported the project. But doing it again now.


At null, null replied: null