Android room database won't export all the data

Question

I'm trying to setup Room database backup functionality. Problem is that sql database file doesn't contain latest set of data in the app once downloaded. It always misses some most recent records. Is there a proper way to export room database? P.S. I didn't face similar problems when handled my db with sqliteHelper, so I suppose it must have something to do with Room.

Way I'm doing it:

@Throws(IOException::class)
private fun copyAppDbToDownloadFolder(address: String) {
    val backupDB = File(address, "studioDb.db") 
    val currentDB = applicationContext.getDatabasePath(StudioDatabase.DB_NAME)
    if (currentDB.exists()) {
        val src = FileInputStream(currentDB).channel
        val dst = FileOutputStream(backupDB).channel
        dst.transferFrom(src, 0, src.size())
        src.close()
        dst.close()
    }
}

try this https://stackoverflow.com/questions/44986465/where-room-save-database-android hope helpful for you — Lokesh, Apr 13 '18 at 17:41
Try [this](https://stackoverflow.com/questions/50987119/backup-room-database/51560124#51560124). — Bertram Gilfoyle, Jul 29 '18 at 04:05
you should close your database before copy it , try this https://stackoverflow.com/a/53976242/4858757 — Shahab Saalami, Dec 30 '18 at 08:48

score 13 · Answer 1 · answered Nov 06 '18 at 07:11

You need to use

JournalMode.TRUNCATE

in your AppDatabase.java:

private static AppDatabase sInstance;

public static AppDatabase getDatabase(final Context context) {
    if (sInstance == null) {
        synchronized (AppDatabase.class) {
            if (sInstance == null) {
                sInstance = Room.databaseBuilder(context, AppDatabase.class, DATABASE_NAME)
                        .setJournalMode(JournalMode.TRUNCATE)
                        .build();
            }
        }
    }
    return sInstance;
}

This method will not create db.bad and db.wal files that's creating hindrance in exporting room db.

For exporting the DB file:

Link: Exporting db with creating folder on daily basis

score 6 · Answer 2 · edited Jul 12 '18 at 18:24

6

I had same issue. you don't need to copy wal (write ahead log file) it's a temporary file. According to documentation we need to close all connection to database before importing or exporting database. This solved my problem and now i have to copy only main database file.

Example of database class:

public abstract class AppDB extends RoomDatabase {    

    private static final Object sLock = new Object();

    private static AppDB INSTANCE;

    // create new database connection
    public static AppDB getInstance(final Context context) {
        synchronized (sLock) {
            if (INSTANCE == null) {
                INSTANCE = Room.databaseBuilder(context.getApplicationContext(), AppDB.class, "packagename")                    
                    .build();
                }
            return INSTANCE;
        }
    }

    // close database
    public static void destroyInstance(){
        if (INSTANCE.isOpen()) INSTANCE.close();
        INSTANCE = null;
    }
}

edited Jul 12 '18 at 18:24

matrix

513
3
8

answered May 17 '18 at 18:01

Muzammil Husnain

1,218
1
10
24

Thank you, this is a great news! And how exactly do you close all the connections to the database? – Tuesday Four AM May 18 '18 at 10:46
2

just call close() function on all your room database objects. – Muzammil Husnain May 18 '18 at 12:42
@MuzammilHusnain "*all your room database objects*" There should only be one instance of an open database. – rupinderjeet May 24 '18 at 03:56
@rupinderjeet yes ideally there should be only one instance of an open database. – Muzammil Husnain May 24 '18 at 09:57
3

The problem is that if you reopen the database and try to execute anything, it fails, as explained here: https://stackoverflow.com/questions/50987119/backup-room-database – Marco Hernaiz Jul 26 '18 at 07:28
The idea is really useful, you need to close the database, then the original database can be copied or backed up – Chanh Jul 26 '19 at 08:50

score 3 · Accepted Answer · answered Apr 15 '18 at 19:01

3

I've solved it. When exporting (saving) sql database which you handle with Room, you have to export(and later import) both - your_database.bd and your_database.wal files. Later is a journal and afaiu keeps latest records.

answered Apr 15 '18 at 19:01

Tuesday Four AM

1,186
1
11
18

what is path of both file please explain i have same problem – Lokesh Apr 18 '18 at 17:22
@Lokesh There are several ways to get db path. I'm using the following: `val currentDBPath = "//data//" + packageName + "//databases//" + DatabaseHelper.DATABASE_NAME + "" val backupDBPath = DatabaseHelper.DATABASE_NAME + ".db" val currentDB = File(data, currentDBPath)` – Tuesday Four AM Apr 19 '18 at 18:06
Another approach is this (note that I use it to get .wal file path): `val currentdbW = applicationContext.getDatabasePath(StudioDatabase.DB_NAME + "-wal")` – Tuesday Four AM Apr 19 '18 at 18:10

score 2 · Answer 4 · answered Jan 19 '21 at 10:24

kotlin:This works for me

private fun exportDb() {
    val TABLE_NAME = "order_table"

    val exportDir = File(getExternalStorageDirectory(), "/CSV")// your path where you want save your file
    if (!exportDir.exists()) {
        exportDir.mkdirs()
    }

    val file = File(exportDir, "$TABLE_NAME.csv")


    try {
        file.createNewFile()
        val db: MenuItemsDatabase = MenuItemsDatabase.getDatabase(requireActivity())
        val csvWrite = CSVWriter(FileWriter(file))
        val curCSV: Cursor = db.query("SELECT * FROM order_table", null)
        csvWrite.writeNext(curCSV.getColumnNames())
        while (curCSV.moveToNext()) {
            //Which column you want to exprort
               val arrStr = arrayOfNulls<String>(curCSV.getColumnCount())
        
            for (i in 0 until curCSV.getColumnCount() - 1) arrStr[i] = curCSV.getString(i)
            csvWrite.writeNext(arrStr)
        }
        csvWrite.close()
        curCSV.close()
        Toast.makeText(context, "Exported", Toast.LENGTH_SHORT).show()

    } catch (sqlEx: java.lang.Exception) {
        //Log.e("Payment fragment", "Exported error", sqlEx)
        Toast.makeText(context, "Exported error", Toast.LENGTH_SHORT).show()
    }
}

score 0 · Answer 5 · answered Jul 20 '22 at 21:42

I had the same problem and I solved it by copying the 3 files that are generated. I work correctly. And the same is done to import.

copyDataFromOneToAnother(application.getDatabasePath(AppDatabase.DATABASE_NAME).absolutePath, Environment.getExternalStorageDirectory().toString() + "/DATABASE_NAME.db")
                    copyDataFromOneToAnother(application.getDatabasePath(AppDatabase.DATABASE_NAME).absolutePath + "-shm", Environment.getExternalStorageDirectory().toString() + "/DATABASE_NAME.db" + "-shm")
                    copyDataFromOneToAnother(application.getDatabasePath(AppDatabase.DATABASE_NAME).absolutePath + "-wal", Environment.getExternalStorageDirectory().toString() + "/DATABASE_NAME" + "-wal")

 private fun copyDataFromOneToAnother(fromPath: String, toPath: String) {
            val inStream = File(fromPath).inputStream()
            val outStream = FileOutputStream(toPath)

            inStream.use { input ->
                outStream.use { output ->
                    input.copyTo(output)
                }
            }
        }

Android room database won't export all the data

5 Answers5

Linked