Android Question Export SQLite database - changes not being exported

[rlocke]

Hi all,

I'm developing an app for internal use (not to be distributed to the Google Play store) which uses a SQLite database.

For archival purposes, the SQLite database can be exported from the app - I've got this working using the ExternalStorage library. My issue is that when I view the exported database, one of two things happens:

1. When I view the exported database, none of the changes I've made to the data are present - the exported database is the original database that gets copied to DirInternal when the app first starts, or
2. The exported database is zero bytes (that is: the database isn't exported at all)

To illustrate the first issue, here is a screenshot of the original database (fresh app install) - the Film is named "Star Wars - Episode IV):

I then edit the record so it says "Movie - Episode IV":

To double-check that the record was saved correctly, on the Database export page I display the record in red:

But when I press the "Export DB" button (which copies the database to the Downloads folder) and then view the database, my changes aren't present (it's like it's exporting the database that was included in the APK, and not the database from DirInternal):

Here's my export code:

Private Sub cmdExport_Click
 
    Dim sourceDB As String = "data.db"
    Dim Guid As String = "export_" & "_"
 
    Log("Exported DB name = " & Guid & sourceDB & " (inside the Export folder)")
 
    If File.Exists(File.dirinternal, sourceDB) = True Then
        'MsgboxAsync("DB exists","Ok")
   
        Dim inpstr As InputStream = File.OpenInput(File.DirInternal, sourceDB)         'Create an Inputstream from the Sourcefile to copy
   
        Storage.SelectDir(False)
        Wait For Storage_ExternalFolderAvailable
   
        Dim DirName1 As ExternalFile = Storage.FindDirOrCreate(Storage.Root, "Export")       'creates the folder at selected location(Root)
        Dim destfile As ExternalFile = Storage.CreateNewFile(DirName1, Guid & sourceDB)               'create the file
        Dim os As OutputStream     = Storage.OpenOutputStream(destfile)                            'Create an Outputstream to the destfile

        File.Copy2(inpstr,os)                                                                  'Copy file
        inpstr.Close                                                                        'Close inputstr
        os.Close

        MsgboxAsync("The database has been exported to: " & DirName1.Name & "\" & Guid & sourceDB, "Message")
    End If
 
End Sub

I've also tried using Erel's SaveAs code:

Private Sub cmdExport_Click

    Wait For (SaveAs(File.OpenInput(File.DirInternal, "data.db"), "application/octet-stream", "export.db")) Complete (Success As Boolean)
    Log("File saved successfully? " & Success)

End Sub

And while it successfully exports the database, the exported database still doesn't have any of my changes.

What do I need to do so that the exported database includes all of my changes? Do I need to close it first?

Searching through the forums, @Cliff McKibbin 's post is how I would like to ideally just grab the database:

This was all possible as the file was visible in 'internal storage', 'android', 'data', 'b4a.myappname', 'files', 'mydir'. The file could be replaced as necessary by moving it from the download directory to 'MyDir'.

And later on there's this comment in the third post:

Alternatively just target SDK 28 and things should work as before.

But I think my manifest is already targeting SDK 28?

AddManifestText(
<uses-sdk android:minSdkVersion="5" android:targetSdkVersion="28"/>
<supports-screens android:largeScreens="true"
    android:normalScreens="true"
    android:smallScreens="true"
    android:anyDensity="true"/>)

But when I browse the "Android > Data" folder, I don't see my app (com.dbexport.test) listed.

And if it helps, here's how the database originally gets copied to DirInternal in the Starter class:

Sub Service_Create

    If File.Exists(File.DirInternal, "data.db") = False Then
        'copy the default DB
        File.Copy(File.DirAssets, "data.db", File.DirInternal, "data.db")
   
        'if not, initialize it
        db.Initialize(File.DirInternal, "data.db", True)
   
        Log("DB does not exist, copying it to DirInternal")
    Else
        'if yes, initialize it
        db.Initialize(File.DirInternal, "data.db", False)
        Log("DB already exists - initialized")
    End If

End Sub

This app won't be in the Google Play Store, and it's for a barcode scanner running Android 9, so I have complete control over the distribution and target device.

Apologies for the long post, I wanted to include as much info as possible.

Thank you for your help!

 

[Erel]

Don't assume that that the database is stored in a single file. With the default journal mode (wal), there are another two files. Check the folder content with File.ListFiles.

 

[rlocke]

Hi @Erel, thank you - so assuming there are an additional two files - would the proper procedure be to also copy these two files, to ensure all of the data is correctly exported?

The exported SQLite database is used by an external application, and I thought SQLite databases were a single, self-contained file - so if there are two additional files, how does that factor into the data integrity?

 

[rlocke]

Or would disabling WAL be a viable option?

 

[Erel]

Disabling wal is a good option.

 

[rboeck]

I think, the easiest way is to close your database; then the additional files should be gone.

 

[Erel]

I think, the easiest way is to close your database; then the additional files should be gone.

Not in wal mode.

 

[rlocke]

Thanks all, I’ll try disabling wal.

Could that be the reason the exported database was zero bytes? Or is there something else I should also look at?

 

[Erel]

Zero bytes is a bit strange, however you should try it.

 

[rlocke]

Disabling WAL seems to have fixed everything (this post also helped):

'must initialize database first
If p.SdkVersion >= 26 Then
   Dim r As Reflector
   r.Target = db
   r.Target = r.GetField("db")
   r.RunMethod("disableWriteAheadLogging")
End If

WAL needs to be disabled every time the database is initialized

Thank you @Erel!

 

[Mahares]

Disabling WAL seems to have fixed everything

Strange, because I ran your project on OS11 and OS8 without changing to any journal and I was able to run it and copy the database as expected to Download folder:

 

[rlocke]

@Mahares That’s interesting that it worked for you - I tried everything from quitting the app to rebooting the phone, and none of the changes were exported.

I am using Android 9, so not sure if that would matter?

 

[KMatle]

I mentioned this behaviout, too (WAL mode is "always" on). I think it's for safety reasons as an app can be wiped and SQlite want to be rock solid in any case using WAL.

 

[Mahares]

I tried everything from quitting the app to rebooting the phone, and none of the changes were exported.

Could you show the journal type in effect in your database before you disabled it and show the code that you disabled it with. I don't think it has to do with your WAL journal mode that you had the problem.
A couple of remarks: about your app You don't need the sub and button: Private Sub cmdPermissions_Click. But even if you need it, Wait For Activity_PermissionResult should be Wait For B4XPage_PermissionResult. But, it is not the reason for your issue

 

[rlocke]

Hi @Mahares - thank you for the remarks about the cmdPermissions.

I'm not sure how to find out the default DB journal type, but here's the code I use to disable WAL:

    Dim p As Phone
    Log(p.SdkVersion)
    
    If p.SdkVersion >= 26 Then
        Dim r As Reflector
        r.Target = db
        r.Target = r.GetField("db")
        r.RunMethod("disableWriteAheadLogging")
    End If

Once I call this code after initializing the database, I was able to export it with all changes present.