How do I save and load a SQL lite database and where is the best place to do that in IOS?
It depends on how you will be using it.
If it is a persistant database
Save it in SpecialFolder.ApplicationData
If it is some cached data you could place it in SpecialFolder.Caches
If it is a temporary database, place it in SpecialFolder.Temporary
It would be a permanent retrievable database the usual name, address, phone number and up to 8 photos per record. I would only need to search the name field
I save my app’s SQLite database in SpecialFolder.Documents which follows Apple’s guidelines that if the app is storing records added by a user and you want that data to be backed up so that the user doesn’t lose data in the event of a device issue then you should save your data to your app’s own Documents folder. I do this even though my app will eventually POST new data to our API, thereby eventually rendering the local database redundant, because my users are able to use my app offline and so that local data store is important for a few minutes or several days, depending on when the app can sync.
However I also cache data – things such as images downloaded from our API for faster loading and offline loading – and I put these into SpecialFolder.Caches which is Library\Caches on the device as these are ephemeral. Apple says that you should only put things in here that you are prepared to download again as the device can delete them at any time if it needs the space, so it’s perfect for temporary image files or data that you can recreate. It won’t get backed up though.
2 Likes
Little bit confused with SQL (not used before) If the following code creates the database, what write the records to and how do I read it back in?
Var dbFile As FolderItem = SpecialFolder.Documents.Child("example.sqlite")
If dbFile <> Nil And dbFile.Exists Then
dbFile.Remove
End If
DB = New SQLiteDatabase
DB.DatabaseFile = dbFile
Try
DB.CreateDatabase
IsConnected = True
CreateDBStatusLabel.Text = "Created."
Catch e As DatabaseException
IsConnected = False
CreateDBStatusLabel.Text = "Error: " + e.Reason
End Try
Numerous examples come with Xojo @Martin_Fitzgibbons have you looked ? Once you connect then any sqlite example (e.g. desktop and more) will give you all you need
Yes Steve I did look for a saving database example but found none in the IOS folder. I thought Desktop and IOS were different for saving but looked in the Desktop area as well and I am still no closer to seeing how you write out the database to file.
The code you mentioned earlier creates a database. In your code it deletes it when it exists and creates a new one.
When you call DB.CreateDatabase, the DB object is connected to that file.
You could call DB.Connect, if you want to open an existing database, to continue using it.
When you are connected, either by CreateDatabase or just Connect, you can get RowSets via DB.SelectSQL
All actions will reflect the database immediately. So, there is not really a need to load or save to the SQLite file.
In a couple of my own projects, I used an InMemory database. I used SQLite as some sort of project file for a subtitle app.
That database doesn’t reside in a file, just in RAM. When I am ready to save my work on disk, I used DB.Backup.
To open a project file, I used the same DB.Backup method, in reverse.
So, in order to save, I backup I create a new DB object, give it a filename, create the file and backup from my InMemory DB to the file.
And to open the file I do the same, I create a new DB object, give it the filename. I also create a new InMemory DB object and use Backup to backup from the file to the InMemory object.
To create an InMemory DB is simple. Just create the DB Object and don’t give it a filename. Just Connect to it.
Var DB As New SQLiteDatabase
Try
DB.Connect
Catch e As DatabaseException
System.DebugLog "SQLite error in " + CurrentMethodname + EndOfLine + e.Reason
End Try
1 Like
You are asking multiple confused questions at the same time. First you ask about sql then you revert to a saving db question.
Seems to me over a couple of years that when stuck you just reach out here rather than trying to find an answer yourself.
I may be wrong but i doubt it.
Just my qualified opinion. Take or ignore. Your choice, of course.
Steve
Thank you so much Edwin for taking the time to explain the concept. So when you create and connect an SQL database it is an open file that is written to every time you add records??? That’s the bit I wasn’t following. Like I said I’m not a professional programmer and there is a lot of gaps in my knowledge. I can read the manuals and examples for days and not get it sometimes… so again appreciate the time you took to answer.
Cheers
Martin
Exactly
Neither am I. I usually make apps to automate my life…
I often watch old Xojo webinars on Youtube. They are really inspiring. Just remember that Xojo moved on to their new API 2.0. So, some video’s may be outdated. So, syntax might differ here and there.
A short video about SQLite can be found on Xojo’s Youtube channel as well.
You’re very welcome
1 Like