Pulling Sqlite database from android gives empty database

Question

I am developing a mobile application that uses Sqlite and I would like to pull the .db file from the phone and view it in DB Browser for Sqlite.

The issue comes from the fact that when I run the adb commands the .db file I get is empty, even though I have creatd tables and added data to those tables in the database.

In the application I query the database for the inserted records and they are returned. Any thoughts?

adb command: adb exec-out run-as projectname cat databases/my.db > my.db

db creation code:

if(!Sqlite.exists("my.db")){
        new Sqlite("my.db")
        .then(
            (db) => {
            //create table here
                db.execSQL("CREATE TABLE IF NOT EXISTS Times (Id INTEGER PRIMARY KEY AUTOINCREMENT, clockIn TEXT, clockOut TEXT)")
                .then(
                    () => {
                        console.log("succcess")
                        db.execSQL("INSERT INTO Times (clockIn, clockOut) VALUES (?, ?)", ["Nic", "Raboy"]).then(() => {
                            db.all("SELECT * FROM Times").then((rows) => {
                                console.log(rows)
                            })
                        })

                    }, 
                    (error) => { 
                        console.log('Error occurred creating table');
                    });
        },
            (error) => {
                console.log("Error creating Database");
            });
    }

In my project I do not have the `if(!Sqlite.exists("my.db"))` and it doesn't mess with my existing database. Does removing this line work for you? I believe that the new Sqlite('my.db') will read the database if it exists and hand you the reference to it. — Shawn Pacarar, May 10 '19 at 17:30
@ShawnPacarar No, the database is still empty after running the adb command — cvb, May 10 '19 at 18:27
@Manoj Android version 9. I'm using an emulator if that makes a difference. — cvb, May 10 '19 at 18:29
Then it should be working, may be you want to give a try for the alternative commands in [this SO thread](https://stackoverflow.com/questions/11598152/how-do-i-pull-the-sqlite-database-from-the-android-device) — Manoj, May 10 '19 at 18:31
@Manoj I have tried most, if not all, of those. Same result. I get the .db file but my db is has not tables or records — cvb, May 10 '19 at 18:44
Then may be the alternative is to copy and share it programatically. — Manoj, May 10 '19 at 18:45
@Manoj I was able to figure it out and added it as an answer. Seems like it was specific to Android 9. Thanks for the help. — cvb, May 10 '19 at 19:01

cvb · Accepted Answer · 2019-05-10T19:16:58.597

9

I figured out how to solve this issue. On android 9 you have to include the -wal and -shm files when pulling using adb. Then when the .db file is opened you will see your database as you have built it and not an empty skeleton db.

This may not be the best way of doing this but it will work:

adb exec-out run-as projectname cat databases/my.db > my.db
adb exec-out run-as projectname cat databases/my.db-shm > my.db-shm
adb exec-out run-as projectname cat databases/my.db-wal > my.db-wal

edited May 10 '19 at 19:16

answered May 10 '19 at 19:00

cvb

723
7
21

1

You should also be able to download the database through the Device Manager in Android Studio (if that is what you are using). You navigate to your device (which can be a simulator AVD) and search your app's data/data/[database] folder. – Michael Dougan May 10 '19 at 19:39
2

An alternative is to ensure that the database is fully checkpointed using [see PRAGMA schema.wal_checkpoint;](https://www.sqlite.org/pragma.html#pragma_wal_checkpoint). In which case the -shm and -wal files will be empty and not required. It comes to light on Android 9 because from Android 9 the default is to use WAL as opposed to the SQLite default of journal mode. – MikeT May 10 '19 at 21:43

score 0 · Answer 2 · answered Jul 21 '20 at 13:02

0

You have to use appDatabase.close() in order to work it.

After closing the db in your activity's onStop(), pull the db file from Android Device File Explorer in Android Studio and use any Broswer sqlite viewer to view the tables in it.

answered Jul 21 '20 at 13:02

Deepak J

184
1
7

Pulling Sqlite database from android gives empty database

2 Answers2

Linked