I created an app that uses a database to retrieve data. This database will never be modified by the user, and it is in an outside sqlite file. I managed to make it working in my app, loading the database, then creating an in-memory database to hold the data while the app is running. It is working.
Anyway, my problem now is to send the app to App Store. When I create the executable, the database is not embedded in the file. How do I do that ?
Also, currently it seats besides the source file, where should I put it to be invisible to the final user ? I know I can use Library and other hidden folders, but I don’t know how to put an existing database there to be embedded in the compiled version. I know how to create an empty database there, but I do not know how to distribute an existing one with my app.
Thanks in advance for any help
You can put the database file in the application bundle (probably the resources folder) using a Copy File Build Step.
If you are just copying the data from the DB to an in-memory DB, you might be able to access it from the bundle. But it’s possible that SQLite might try to create a temp file, which would cause problems. If that happens, you’ll want to first copy the file out of the bundle (perhaps to SpecialFolder.ApplicationData) before you try to use it.
Amazing, always something new to learn! Never used that before, never had to. Thanks
Just one question though, something I couldn’t find explained in the literature, and is earned by experience: How is the best way to still test and debug the application in IDE, if the files are located elsewhere when compiled ?
You cannot access a Xojo SQLite database inside the bundle, from a sandboxed app. Xojo cannot open it in readonly mode.
You have to copy the database file from the resources folder (if you placed it there) out to the application support folder. From there you can open it and select data into your temp db.
I do a binary copy from the resources to the App support folder like this:
[code] Dim fSource As FolderItem
fSource = App.ExecutableFile.Parent.Parent.Child(“Resources”).Child(“mysqlite.db”)
If fSource.Exists Then
Dim SupportFolder As String = app.kAppName
Dim TargetFile As String = “mysqlite.db”
// Copy mysqlite.db out to Application support folder
Dim f2 As folderItem = SpecialFolder.ApplicationData.Child(SupportFolder).Child(TargetFile)
If im.BinaryCopyFile(fSource,f2) Then ...[/code]
[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]
@Oliver Osswald you are right, my app was blocked from apple store because of
[quote]
The application accesses the following location(s):
‘/Applications/Periodic Table.app/Contents/Resources/ptable.sqlite’
The majority of developers encountering this issue are opening files in Read/Write mode instead of Read-Only mode, in which case it should be changed to Read-Only.
Other common reasons for this issue include:
-
creating or writing files in the above location(s), which are not valid locations for files to be written as stated in documentation
-
writing to the above location(s) without using a valid app-id as a container for the written files[/quote]
But thats odd, because I do not change this database never, I even work with a in memory copy of it.
dadoselementos=new SQLiteDatabase
if dadoselementos.Connect then
Dim source as Folderitem
source=app.executablefile.parent.parent.child("Resources").Child("ptable.sqlite"
If dadoselementos.AttachDatabase(source,"Source") Then
dadoselementos.SQLExecute("CREATE TABLE periodic As SELECT * FROM Source.periodic")
end if
//continue
else
//Check Orders.Error
end if
Any idea ??
Thanks again
[quote=105009:@Alexandre Amato]@Oliver Osswald you are right, my app was blocked from apple store because of
But thats odd, because I do not change this database never, I even work with a in memory copy of it.
<…>[/quote]
As I wrote, Xojo simply cannot open an sqlite database file as readonly.
2 year old feature request here: <https://xojo.com/issue/21814>
When you CONNECT to an sqlite database file using the xojo included plugin, then you open it in read/write mode. If the file is inside of a sandboxed OSX bundle, then connection fails for this reason.
Workaround: copy file to a location where you can connect to it and read from it. See code above.
a. In the IDE, use menu INSERT → Build Step → Copy files
b. Select the new CopyFiles1 entry from BUILD SETTINGS (lower left corner of the IDE)
c. Drag your database file to main screen area of the IDE
d. In the inspector area, chose behavior “Both” and destination “Resources Folder”
Now you can use code similar to what I posted above, to copy your database file from the resources folder to the application support folder, for debug run as well as in the final build.
If you want to test sandbox mode in a debug run, then you will have to insert a build step “Script” in a similar way as above. The easiest way to get a script which is doing all the necessary steps to make your app sandboxed, is to use AppWrapper. AppWrapper can create the necessary script for you, and you just have to copy it to the BUILD SETTINGS script.
http://www.ohanaware.com/appwrapper/
Even if you lock the sqlite database so it cannot be changed and open it read-only, the MAS will reject it. I have tried! You have to copy it elsewhere, usually the Application Support folder.
Just wondering: HOW do you open a SQLite database read only with Xojo on-board means?
Or are you saying, that you opened a SQLite file read only with other means, like MBS plugins, and THEN it got rejected?
I tried to resubmit my app with the SQLite file locked and when opened only performing read operations, but it was rejected. You are right, I don’t think you can open an SQLite database as read-only per se.
So I am repeating myself: It doesn’t matter whether one intends to only select data. As soon as one connects, the databasefile is opened in writeable mode. A sandboxed app will fail then and any such submissions to MAS will be rejected.
This code does NOT work for sandboxed apps:
[code] Dim f As FolderItem
f = App.ExecutableFile.Parent.Parent.Child(“Resources”).Child(“mysqlite.db”)
If f <> Nil And f.Exists Then
Dim db As New SQLiteDatabase
db.DatabaseFile = f
If db.Connect Then // f is opened in read/write mode
// ... Proceed
Else
MsgBox("Could not connect. Error: " + db.ErrorMessage)
End If
End If[/code]
Hi Oliver. I am new to coding. As a matter of fact I am not a programmer but a physician. I have wrote an application and sandboxed it with appwrapper but trying to us your turnaround for a sqlite database I always get a NilObject error for the file to be copied to the application support folder. I have no clue on what is going on. I would really appreciate your help.
Please start your own thread including more details like where you’re copying from, the path your copying to, and any code involved in the copying process. We’ll need as much information as possible to try to help.