I’m relatively new to Xojo but have spent countless hours reviewing documentation and example programs. The goal is to have the application use the sqlite database file in the same directory as the application. It seems no matter what I do, the system will only use the database file as inserted into the project via the IDE. This works fine unless I want to move/copy the application to another directory and use the database file in that directory. On the flip side, if I don’t insert the database file into the project via IDE then the application fails at compile.
Here is the code I have in a method that is called in the very beginning of the Open event handler of the main form. However, this code executes ok but seems to be completey ignored by the system and the database connection stays with the database inserted into the project. FYI, this application runs in both Windows and Linux and the same errors occur in each environment. Also FYI, the name of the database inserted into the Project via IDE is dbTalents.
Var dbTalents As New SQLiteDatabase
Var f As New FolderItem
Var p As String //path
Var l As Integer //length of path
Var x As Integer = f.name.Length //extraneous characters in path
//full path of database file. NativePath include extraneous characters (f.Name) i.e. Debug-MyApplication
//that need to be removed from path.
l = Len(f.NativePath)
p = f.NativePath
p = Mid(p,0,l-x)
p = p + “dbTalents.sqlite”
dbTalents.DatabaseFile = New FolderItem(p)
If dbTalents.DatabaseFile.Exists Then
If dbTalents.Connect Then
Else
MessageBox("Error connecting to database. Please check that your database file is in the same directory as the program. Error code: " _
+ dbTalents.ErrorMessage)
End If
Else
MessageBox(“Database file does not exist. The file dbTalents.sqlite must be in the same folder as the application.”)
Return
End
The ONLY time you might expect this to work is if your app is in a location such as a USB drive. Program Files/ or Applications/ are treated as read-only locations by most OS’
And the Mac doesnt like having apps outside of Applications anyway… it will translocate your app (pseudo sandbox)
Like everyone else says, put the database in specialfolder.applicationdata , or better still in a subfolder of that, named for your company or app.
Then it will work no matter where you move your app.
Don’t do this. It locks the location of the file. If you want to use the file in a particular location, use a Copy Files build step to copy the file into your bundle and then on app startup, check for the presence of the file in your desired location (which should be SpecialFolder.ApplicationData) and copy it out if needed. Ie., your app copies the database file the first time it’s run and then just uses the one that’s there for subsequent launches.
For years I have used a small SQlite db with my programs to save details such as the location of windows when they close (to restore them to the same position the next time) plus other stuff they need. I always place this in the same directory as the program itself and I have never had problems with it with Windows and Linux (I don’t do Apple). This makes it easier to copy the program, say, to a new computer without having to search through directories for bits and pieces. Like I say, I’ve never had a problem with this.
if it is, Windows will try to stop you writing there.
You can force it on your own machines, but a distributed app will have trouble.
If the app is in Documents, or a USB drive, or C:/myapp then you probably will get away with it, yes.
Only virus checkers (spit) might complain about those locations
Ah! Now I see why I don’t get this problem. I have Windows running on a Dell box with a 490 Gb SSD as its C drive. This is fine for quick booting but due to its space limitations and my paranoia regarding SSDs (they can fail without warning) I keep everything else on the D hard drive (4 Tb) where there is a Program Files directory with, obviously, all my programs. There’s no problem there – Windows lets me do what I want. I tried copying one of my programs to the Programs File on the C drive and, yes, Jeff is right – it won’t write unless I tell Windows to allow it. I have an identical system running Fedora Linux where there seems to be no such problem.
Every access to an object (file or folder) on an NTFS volume has a security context anchored to the user account that started the process that is performing the access. The Program Files folder on the C: drive was created by the Windows Installer process. The Program Files folder on your D: drive was (most probably) created by your own user account.
When you start your application from the desktop it runs in the context of your own user account and so it can write to the database as ‘you’ created the D:\Program Files folder and the database file within it. ‘You’ did not create the C:\Program Files folder, so you do not own it and can not write to it without being granted a separate permission. From what I remember C:\Program Files is owned by the Trusted Installer account.
You can override Windows default permissions but it often creates unintended consequence. It’s easier to grasp the thorn and place your documents in Documents and program data in Program Data. Xojo even has the Special Folder object to help you out.
Linux (Unix/Posix) native permissions are far less granular. There is still a security context anchored to the process performing the access but permissions are assigned only to user (owner), group, other. There is a lot of poor advice on the internet which leads people to believe that all permissions issues should be solved with a sledgehammer. chmod 777 will quickly provide access to a file but is hardly ever the correct solution.
File access is such a fundamental thing that computers do it surprises me (disappoints even) so many technically literate people seem to be stuck at Windows 95. A little time invested in learning the basics of how modern file systems work can save a great deal of time in the future.
Well, after over 40 years in the computer world (some of which were spent as Editor of a major computer magazine) I think I’ve progressed beyond Windows 95 and even to learning about modern file systems. This was one of those cases where the blindingly obvious wasn’t obvious. Put it down to old age.
Upload an application you created (even a simple Hello wold ! application) onto a web sharing service.
Then download it and run it.
Last time I’ve done than (with MediaFiee.com), it tells me “the developer is unknow” and ask me permission to run it… (after the do you want to run this application that comes from Internet, of course)…
Thank you all for your responses. It was quite an education. One aspect of my question is unclear. It appears you have to insert the db into the project or there are compile errors. If the db is already in the project then why do we need code such as db.connect? In my application, the db.connect and related instructions listed above seem to be completely ignored. It this because the db is not is the right location as discussed above? Thanks in advance for your replies.
Not strictly true.
It can be dragged into the resources at compile time.
If you drag it into the project and refer to it by the project name, you will get compile errors if it is not there.
Consider that Xojo has the ability to connect to a db that is not in fact your own.
It exists somewhere- to connect to that you have a database variable , and a folderitem variable set to the location of the external db.
like this: (the example allows you to browse to an external file.
If yours is in a known location like specialfolder.applicationdata.child(“myapp”).child(“thedatabase.sqllite”)
then just swap out the browse for a valid folderitem
Var db As New SQLiteDatabase
Var dbFile As FolderItem = FolderItem.ShowOpenFileDialog("")
If dbFile <> Nil And dbFile.Exists Then
db.DatabaseFile = dbFile //tell the object where the external file is
Try
db.Connect //connect to it
MessageBox("Connected to database successfully!")
Catch error As DatabaseException
MessageBox("DB Connection Error: " + error.Message)
End Try
End If
No. As I stated before, you really shouldn’t do this. This functionality was intended to be used for quick-and-dirty apps that will never be shared or moved to another machine. For “real” apps, you should
Add a global property for the db.
On app Open, get a FolderItem pointing to the database and assign it to he db property.
Either open the database immediately and leave it open for the duration of the app, or open/close it as needed.
Can’t say I ever edited even a minor magazine. I started working at the coal face of IT support in 1990 and I’m still here and still enjoying it.
Wasn’t trying to have a go at you. Was a general observation that awareness of file permissions is not great. I learnt to check permissions as a first resort when Netware 386 was a thing. In the Win95 days I could understand why so many regarded permissions as a black art. Today though? Ownership and permissions have been a ubiquitous feature since XP and still I am getting called out to attend to simple permissions issues. I doubt it has much to do with age as several younger techs and devs will have had a go before it gets to my desk.
As has been said, your db shouldn’t be in a project unless you are using it as a resource; a template to copy or exclusively for static (read only) data.
A particular feature of SQLite is support for ‘in memory’ databases. If you call db.connect without setting a db file, the SQLite engine will create an empty database in RAM.