Sqlite Database in Sandoboxed App

I am trying to access a sqlite database in my sandboxed (with appwrapper) app using the Oliver Osswald turnaround but I consistently get a NilObject.
I copy my database file to the resources folder with a Build Step. I included the Tim Parnell’s TPSF (TPSpecialFolder) module to access the resources folder and the Bundle Identifier.

Here is my code (replacing my variables in his code):

Dim fSource As FolderItem
fSource = TPSF.Resources.Child(“RecetaElectronica.sqlite”)
If fSource.Exists Then
// Copy RecetaElectronica.sqlite out to Application support folder
Dim f2 As folderItem = TPSF.AppSupport.Child(“RecetaElectronica.sqlite”)
If f2.Exists Then

Here I get the NilObject

What path does TPSF.AppSupport return?

MsgBox TPSF.AppSupport.NativePath

/Users/drjuancarlosalcedovelarde/Library/Containers/com.juanalcedo.recetaDesktop/Data/Library/Application Support/com.juanalcedo.recetaDesktop

If f2 is Nil then something goes wrong in your file copy method. Did you debug step through it? I guess that your sqlite file never arrives in the folder /com.juanalcedo.recetaDesktop …

The Build Step actually copies the sqlite database to the Resources Folder but not to the App Support Folder. I have also tried other locations like desktop (SpecialFolder.Desktop.Child(“RecetaElectronica.sqlite”) but nothing gets copied there neither.

I am using code similar to this one:

A method to copy a database file from the resources folder within a sandboxed app to its application support folder:

[code]Private Function CopyDatabase() As FolderItem
Dim fSource As FolderItem

fSource = App.ExecutableFile.Parent.Parent.Child(“Resources”).Child(“dbfile.rsd”)

If fSource.Exists Then

Dim SupportFolder As String = app.kAppName
Dim TargetFile As String = "dbfile.rsd"

// Copy database file out to Application support folder
Dim f2 As folderItem = SpecialFolder.ApplicationData.Child(SupportFolder).Child(TargetFile)

If im.BinaryCopyFile(fSource,f2) Then
  fSource = SpecialFolder.ApplicationData.Child(SupportFolder).Child(TargetFile)
  
  If Not fSource.Exists Then
    // Report Error here
    Return Nil
  End If 
  
Else
  // Report Error here
  Return Nil
End If

Else
Return Nil
End If

Return fSource

End Function
[/code]

My own Copy method:

[code]Function BinaryCopyFile(fSource As FolderItem, fTarget As FolderItem) As Boolean
Dim bs1 As BinaryStream
Dim bs2 As binaryStream

//make sure it exists before we try to read it
If fSource.exists Then

//open the folderitem as a binary file without write privelages
//     To open with write privelages, use true instead of false
bs1 = BinaryStream.Open(fSource, False)

//create a binary file with the type of text (defined in the file types dialog)
bs2 = BinaryStream.Create(fTarget, True)  // True = overwrites existing file

//make sure we have a binary stream to read from
If bs1 <> Nil And bs2 <> Nil Then
  Try
    //read and write the whole binaryStream
    bs2.write(bs1.read(bs1.length))
    
    //close the binaryStream
    bs1.close
    bs2.close
    Return True
  Catch exc as IOException
    MsgBox "ERROR - failed to create the output file."
    Return False
  End Try
Else
  Return False
End If

Else
Return False
End If

End Function
[/code]

I must be doing something wrong definitely. Now I get the NilObject Exception in this line:
bs2 = BinaryStream.Create(fTarget, True) // True = overwrites existing file

Maybe it is related to this instruction: //create a binary file with the type of text (defined in the file types dialog)
Where should I define the type of text? and what kind of text should I define?
Please be patient with me, I am not a programmer just a physician trying to write code, jaja.

I quickly made a sample app which you can download here:
osswald.com/xojo/samples/copydb_test.zip

The zip-file contains a folder with 3 files in it:

  • copydb.xojo_binary_project
  • mydatabase.sqlite
  • CopyDB.app (compiled and sandboxed version of the code file)

It contains a build step which copies the database file to the resources folder of the app.
In Window1.Open event handler, the folder “CopyDB” is created in Application Data and the database file is copied to to it.

On Window1, there is a pushbutton to show the parent folder of the database file.

[quote=179960:@Juan Carlos Alcedo Velarde]<…>
Maybe it is related to this instruction: //create a binary file with the type of text (defined in the file types dialog)
Where should I define the type of text? and what kind of text should I define?<…>.[/quote]
I’m sorry, this old comment in the code is misleading. It is a leftover from another version of that function. Sorry about that.

Thanks. I will test and let you know

Thank you. It worked flawlessly.

Sorry. It seems to be a problem. Every time I run the application a fresh copy of the database is created so the data already stored in previous sessions is not there.

:slight_smile:
My example app is just a stripped down showcase, demonstrating one way on how to distribute a file with a sandboxed app and copy it to a place where one can write to it.

Now you have to think about all the possible errors and caveats yourself, and program accordingly.

In this case you have to check if the database file is already existing in the targeted location and do the copy only if not.

See: https://documentation.xojo.com/index.php/FolderItem.Exists

To avoid these issues. Let just Xojo create (CreateDatabaseFile) a sqllite database in SpecialFolder.ApplicationData Folder and put in your tables and initial data with SQLExecute("CREATE TABLE … ").

Works perfect for me…

I already figured it out. Thank you all for your help.