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.
I have been having similar issues. All I wanted was do have my database in a subfolder where my app is stored but Xojo does things differently and is causing me a few issues.
I have seen many different types of software on Windows 11 that put files in a subdirectory of the folder the .exe is running from. Why is this so difficult to do with XOJO?
You don’t actually put them in folders in your Xojo project. As you’ve seen, that structure is just for the project layout, not the final build.
If you use a copy files step, you can specify the destination directory and it will put it there whenever you build or run. Then when you’re connecting to it, you’ll set DatabaseFile like this:
So, I inserted a BuiltStep to copy files. I dragged my databases folder into the window and it shows up in CopyFiles1. When I build though it doesn’t copy anything over.
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.
Also, at development stage, create a text file you will use to import data into the db file.
When you do that, if you made any error with the .sqlite file, you only have to trash it and re-create / re-import the data and you can continue to test, improve the db code.
Then add code to export the data to .txt (and some other probabmly useful formats: xml, html, pdf, json… what is needed).
I don’t know if this is helpful, but when I develop an application that uses a SQLite database, I will also implement a way to select the database by the application user.
You can also check within the opening event of your application if your database is present in a specific folder and if not, just create the database.
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.
I seem to have found the problem. Even though the database is a property of the window, I still have to declare it as a new sqlite database. No more crash. Thank you to every one.
