I am trying to build a small program with a SQLite database and it is giving me fits. Nothing seems to work like it used to. I haven’t worked with a database since they rewrote the language.
I add a database using the Insert > Database menu and it works perfectly on my computer. But when I try to run it on a different computer it cannot find the database. If I even move it on my own computer it stops working. I have tried connecting to an external database, but Xojo seems to hard code it to look for the database on my computer.
I have spent about 6 hours on this, and yes I used the language reference, for what it’s worth. The code works, but in the past the database would easily move to other computers, so I don’t even know where to look next. Build settings are Windows 64-bit.
When you add a database, it creates a file on disk wherever you save it. You’ll need to make sure this file gets copied with your app.
That said, you’ll have better luck if you connect to the database in code and use a CopyFile build step to put it in a location where you’ll be able to locate it… like in the Resources directory that’s available for every app using SpecialFolder.Resources.
Thats bad and unwanted behaviour (according to MS and Apples guidelines) - under WIndows and macOS apps will (most of the time) be installed in the “Program files” or “Applications” folder and this folder is not meant to contain any user changeable data (like databases, prefs files etc.).
Better use the approbiate location, specified by SpecialFolder.ApplicationData which points to \Users\UserName\AppData\Roaming\ (Windows) or /Users/UserName/Library/Application Support (macOS).
It is certainly possible to have files ‘close to’ your app.
But Mac and Windows both demand that such files are read only.
You cannot amend them.
Files you need to amend should go into the documents or application support folders.
In the specific case of SQLLite files, I believe I read on this forum recently that if a SQLLite file is readonly, you cannot open it - as SQLLite demands that it be writable .
So you could reasonably keep readonly text files, bitmaps, PDFs, XML , etc locally.
But a SQLLite file has to be read-write.
And as others have said, the way to do that is to bundle the file in your resources, and copy it to a specialfolder the first time your app runs, or create a new empty database when the app first runs, and use SQL to create the tables within it.
Thank you. So far that hasn’t worked, but I will take another look and see what I am doing wrong. I did get it to work if I put it directly on the C drive, but that has to be fixed before I distribute it to the other users.
My recommendation would be to let the program create the whole database directly in the code when the program is started for the first time and not to try to move a copy somewhere.
The storage location is the directory provided by Micorsoft or Apple, which can be reached via SpecialFolder.ApplicationData
When starting the program, you test whether the database file is in the selected storage location and if not, it is recreated (and filled with content if necessary). Once the file has been created or already exists, it can be opened.
This avoids problems when copying files (and access privileges), you can maintain the database directly within Xojo (e.g. also update tables, triggers etc) and you can be sure that it is always available.
Ok, I got it to copy the database to the appData folder, when I try to get the program to connect to it, I get an unhandled nil object exception error. The problem is, I check for nil object and it passes, so I am going to try to post the code that I used and see if anyone can point out a stupid mistake that I must have made.