Android Question Replace SQLite DB with Backup from Web Server

[Kevin Hartin]

I have a facility in my POS app that enables subscribers to backup their DB to a web server. This can then be restored or downloaded to another POS device.

The Upload and Download logic works fine, but I get the following error when I try to use the DB after restoring it.

pagesuppliers$ResumableSub_lblRefreshSuppliers_Clickresume (java line: 877)
android.database.sqlite.SQLiteReadOnlyDatabaseException: attempt to write a readonly database (code 1032 SQLITE_READONLY_DBMOVED[1032])
    at android.database.sqlite.SQLiteConnection.nativeExecuteForChangedRowCount(Native Method)
    at android.database.sqlite.SQLiteConnection.executeForChangedRowCount(SQLiteConnection.java:1164)
    at android.database.sqlite.SQLiteSession.executeForChangedRowCount(SQLiteSession.java:756)
    at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:66)
    at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:2438)
    at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:2359)
    at anywheresoftware.b4a.sql.SQL.ExecNonQuery(SQL.java:74)
    at com.bookingmy.travel.samoaPOS.pagesuppliers$ResumableSub_lblRefreshSuppliers_Click.resume(pagesuppliers.java:877)
    at anywheresoftware.b4a.BA.checkAndRunWaitForEvent(BA.java:267)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:207)
    at anywheresoftware.b4a.keywords.Common$11.run(Common.java:1178)
    at android.os.Handler.handleCallback(Handler.java:938)
    at android.os.Handler.dispatchMessage(Handler.java:99)
    at android.os.Looper.loop(Looper.java:246)
    at android.app.ActivityThread.main(ActivityThread.java:8512)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:602)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1139)

I save the file to TEMP.db, do an SQL.Close, Copy the file to POS.db, Delete TEMP.db and finally Initialise the SQL, but still get the error.

If I restart the POS app, it works fine with the new DB.

I am using B4Xpages, if that makes a difference...

Thanks,
Kev

 

[jahswant]

Try to copy and just replace ! Don’t close the object !

 

[Kevin Hartin]

Try to copy and just replace ! Don’t close the object !

That works. I replaced the POS.db directly without and close or initialise.

I thought I was taking a more conservative approach by saving a copy, closing then deleting and re initialising

Thanks,
Kev

 

[Erel]

Note that if SQL journal mode is 'wal', which is the default mode in newer versions of Android, the database will be made of up to three files.
You should explicitly set the journal mode to DELETE after you open the database.

 

[Kevin Hartin]

Note that if SQL journal mode is 'wal', which is the default mode in newer versions of Android, the database will be made of up to three files.
You should explicitly set the journal mode to DELETE after you open the database.

Should this be done immediately after SQL.Initialise?

 

[Erel]

Yes:

sql.ExecNonQuery("PRAGMA journal_mode = DELETE")

 

[Mahares]

sql.ExecNonQuery("PRAGMA journal_mode = DELETE")

It should be:

rs = sql.ExecQuery("PRAGMA journal_mode = DELETE")

 

[Erel]

Let me try...

 

[Erel]

@Mahares is 100% correct.
The behavior is a bit tricky, it is not updated if you don't iterate over the ResultSet. Best to write it like this:

sql.ExecQuerySingleResult("PRAGMA journal_mode = delete")
Log("Current mode: " & sql.ExecQuerySingleResult("PRAGMA journal_mode"))

 

[Erel]

[B4X] SQL - setting the journal mode

The modes are explained here: https://www.sqlite.org/pragma.html#pragma_journal_mode The two cases where it is important to set the journal mode: 1. WAL - B4J server solutions: https://www.b4x.com/android/forum/threads/webapp-concurrent-access-to-sqlite-databases.39904/#content 2. DELETE - when...

www.b4x.com