Carrying a SQLite DB around

I created a very simple code with its SQLite DB attached. As I said, it’s very basic but I tried to build it as a standalone app and it works.
Being very new to SQLite, my question is: do I have to carry around the generated SQLite DB if I want to use the app in another computer or is there a way to insert it into the application so I can simply copy the app and start using it by itself?

Thank you

Probably you’ll want to add a Copy File step under the Compilation settings, so the SQLite database is stored, for example, in the app resources folder.

Then, once the user run your app, the first step should be copying the database file from the app resources folder into the user SpecialFolder.ApplicationData (just if it has not being copied before, you should check for that). I mean, copying the file from your app resources folder to the user ApplicationData folder should be done only the first time the app is run.

Once the database file is available in the ApplicationData folder, your app can connect to it and proceed as usual.

Thank you.
But I still will have an external DB, whether created by the app or copied, am I correct?. I hoped I could have something inside the app only

If build for Mac, Apple will not allow this. As Javier mentioned, there is a dedicated place on the computer for storing application data.

If you want you db “inside the app”… then you should create the db schema (tables et al) from code. Something you can do the first time the user runs the app.

Added because I think I realised what do you mean with “inside”.

If you don’t want to rely on an external file, then you may want to create an in-memory SQLite database too…

And still storing the data inside the ApplicationSupport folder.
Another place to store data is in the user’s Documents folder.

I think Armando wants the data to be used by other apps. Right? By using the folders mentioned above, you can have other applications use this database file. But be aware that SQLite is not really meant for multi-user usage.

If you want to use the data on an other machine, there is the backup method in the SQLite object. With that you can “export” the data to a USB thumb drive. In the other machine, you can open this backup copy and “sync” the data you need with the local database in the other computer.

That’s what I do with some apps, too. I was working on a subtitle app, where the project file was an encrypted SQLite DB, stored elsewhere (documents folder or external drive). To work with the project, I opened the “project file” and used the backup function to copy the data to an In-Memory database.
To save the project, I used the backup function to copy the data to a SQLite file, elsewhere on the machine.

I understand.
Probably, the easiest way is doing it “the Apple way”.
I’ll have the app create or store its own DB in the ApplicationData Folder.

Thanks everybody for your suggestions

1 Like

And, when needed, use the db.Backup method to export the data to use elsewhere.

May I ask one more thing?
Running my code (I have 6 columns), I fill each column with a number sequence except the first one which is filled withh a text sequence, just for testing purposes - i.e. “aa”, “bb”, “cc” atc.
When I close the debug app and restart it columns 1, 2 and 3 are shifted by 1 position (3 becomes 1 an 1 becomes 3), column 0 get shifted vertically.
I’d like to post my app and see where I’m doing wrong (as I said, I’m very green at SQLite and DBs in general). Is there a way to post it within this foru or should I use some link to an external file transfer service?

Thank you

External link, please.

1 Like

Here it is:

Looks fine to me. I opened the database a couple of times:

Try by typing 1 in column 1, 2 in column 2, etc. the close the app and restart it. Whenever I do this I find the column shifted by 1 position

Change LoadVis code from:

visiteList.AddRow(visiteRS.Field("Nome").StringValue, _
visiteRS.Field("Centro").StringValue, _
visiteRS.Field("UltVis").StringValue, _
visiteRS.Field("DataPren").StringValue, _
visiteRS.Field("Ora").StringValue, _
visiteRS.Field("Scaduta").StringValue)

to

visiteList.AddRow(visiteRS.Field("Nome").StringValue, _
visiteRS.Field("UltVis").StringValue, _
visiteRS.Field("DataPren").StringValue, _
visiteRS.Field("Centro").StringValue, _
visiteRS.Field("Ora").StringValue, _
visiteRS.Field("Scaduta").StringValue)

It was so simple. I was looking for some obscure piece of code…
One thing I learnt: SQLite is position dependent. I mean, things must be considered in order.

Thanks Alberto

This change was not because SQLite (it is not position dependent) but when you add a row to a Listbox it should be in order.