External SQLite DB File

Databases
1

I’m new to Xojo. I searched thru the forum but I can’t seem to find an answer to my question.

My question is, if I have a sqlite file, do you drag and drop the file in the IDE to make it aware of the file. Or do you store the file is a special place and reference it. What is the correct method to bring an external sqlite file to xojo as opposed to create and populate a local DB. Thanks

2

I’m confused by your terms. How are you differentiating “external sqlite file” and “local DB”? Aren’t they the same thing? I mean, sqlite is by definition a “local DB”.

You should store your sqlite file in SpecialFolder.ApplicationData (hidden) or SpecialFolder.Documents (visible to user).

3

You can drag it into the IDE so it is part of the bundle… BUT ONLY TO SEED A WORKING COPY.
DO NOT, ATTEMPT TO READ OR WRITE RECORDS TO THE IN-BUNDLE COPY, this will be harmful to the continued health of your app

Have you app on startup check to see if the DB exists in an external folder, and if not, copy the in-app version to an external folder and always use that one.

Better still unless you have a ton of data that you want to seed the DB with, use code to simply check for existance of DB and use SQL statements to create the DB and the tables required.

4

Thanks Tim and Dave

I am using local as a DB that is created and populated by code, external as a pre-built DB with values already loaded. (Ex: XXX.sqlite)

So I want to connect to the DB file, read my canned values and store other data in Tables that are already defined in the same file.

Can you please direct me to code that demonstrates this concept? All the samples I find seem to create the DB in code.

Thanks again

5

The concepts you will need to familiarize yourself with are

FolderItem
SpecialFolder
SQLiteDatabase

dim f as folderitem = SpecialFolder.Desktop.Child("database.sqlite")
db = New SQLiteDatabase
db.DatabaseFile = f
if db.Connect then
   // all is good to go
else
   msgbox db.ErrorMessage
end
6

Thanks Tim

Got it all working. I was able to store the SQLite file, make a DB connection, read out canned values and write new data.