Hi All,
Question please. I am just a hobbiest and many of the more technically complicated issues are way above my understanding. Nevertheless I do ok with several personal apps on my Mac that I use regularly involving using SQLite DB’s.
Anyway, I’ve decided to have a go at learning Web programming and want to move those apps across. I’ve run into an issue tho with the 1st one accessing the database.
This app uses a database file called data 2.db and it is already created and lives in the same folder as the app.
On the Mac version of the program I create a property called DB as an SQLiteDatabase in the App.class (is class correct here). In the App.Open event I have:
db = new SQLiteDatabase
db.DatabaseFile = new FolderItem(“data 2.db”)
Try
db.Connect
Catch err as DatabaseException
MessageBox err.Message
end try
This works ok and connects the database.
With the Web version I basically did the same thing but placed the property in Session and the code into the Session.Opening event. However I get an Exception Error stating that the database file doesn’t exist in the specified location.
I’m suspecting that Web apps don’t immediately look at the folder where the app is - is this right? If so, where do I have to look and/or how can I make it look at the folder I want it to?
Running the app on a Mac, there is a single user that needs to connect to the database. With a Web app, that means more than one user connects to the database. With SQLite, there is an issue because it’s not a database server since there is no database engine that may share the databases. With SQLite, you have a class that accesses the database opening a file.
You can use sqlite with a web app. You have to enable WAL, which allows multiple connections to the database from the same server. You may take a performance hit, though, as multiple writes to the database will be queued. So make your writes short and sweet.
But that doesn’t address the original problem of the database file not found. Print out the value of App.ExecutableFile.NativePath and compare it to where you put the database file.
Hi Gilles,
Thanks for the reply but I think you’ve missed the point a little. I am just a hobbiest and, whilst the apps I have written are fully functional and I use them daily, they are purely for my use only. I’m not a pro writing programs for sale. The reason I am looking at redoing them in Web is firstly, as a project but secondly, so I can access them when I don’t have my Mac available - eg from my iPad instead. As such, I would plan to eventually store the DB on something like my private iCloud storage. I don’t need it to be multi-user.
Having stated my aims, am I barking up the wrong tree? Is what I plan to do, such as the iCloud etc doable?
Tim,
I have done that. The app is called WebWorkTracker and the files are kept in Application/Worktracker2.
Printing the NativePath as you have suggested returns Application/WorkTracker2/WebWorkTracker.debug/WebWorkTracker.debug
Now, am I right in saying then, that it is looking in a directory where the program files are being “put together”? If I look, the last directory does not exist. The best I have is Applications/WorkTracker2/WebWorkTracker.debug/WebWorkTracker.debug Libs
Interestingly, the same command in the Mac App returns:
/Applications/WorkTracker2/Work Tracker 2.debug.app/Contents/MacOS/Work Tracker 2.debug
but it appears to find the DB in Applications/Worktracker 2
I’m surprised you haven’t run into this with your desktop version. When you Run the app from the IDE, it creates a new folder and assembles the app there. That is true of both Desktop and Web apps. Unless you have a FileCopy build step, the database won’t be there during debug. Check your Desktop version for a build step.
That said, it is much better to store your database in ApplicationData. Use SpecialFolder.ApplicationData to navigate to your file. That will work in both debug and built apps with no fussing around.
Suppose you create a folder in ApplicationData named MyCompanyName and inside it you create a folder named MyAppName and within that you put your file MyDatabase. To get to the database file use
var file as FolderItem
file = SpecialFolder.ApplicationData.Child("MyCompanyName")
if file not Nil and file.Exists then
file = file.Child("MyAppName")
end
if file not Nil and file.Exists then
file = file.Child("MyDatabase")
end
var db as New SqliteDatabase
db.DatabaseFile = file
Note that on Mac there is some special naming convention for what I call “MyAppName”. Something like “com.MyCompanyName.MyAppName”. I’ll let the others comment on that.
Tim,
Sorry I haven’t replied earlier, I’ve been away.
Question re your suggestion, is ApplicationData a folder I create or is it a system folder. I’ve searched my Mac and can’t find it anywhere? I’ve “unhidden” all folders before I searched.
For what it’s worth, I have put this code into the Opening function:
var f As New FolderItem
f = SpecialFolder.ApplicationData
MessageBox f.Name
Hi Greg,
Thanks for helping. Is “CopyFilesStep” a function I create (and if so, what’s in it) or is it a Xojo built in function? If the latter, I can’t find it.
However, I haven’t done this with the Mac version and, as initially stated, it has no problems finding the DB. That’s the thing that confuses me.
ApplicationData is a system folder. Use MessageBox f.NativePath to get the full path to the folder on your system.
That said, f = new FolderItem("data 2.db") does search for the file in the same location as the app, just like a Desktop app does. However, when you Run the app from the IDE, it creates a new folder to create the executable in. You saw that as Application/WorkTracker2/WebWorkTracker.debug/WebWorkTracker.debug. The .debug part is a fresh folder that is created when you run the debugger and then is removed when the debugger exits. Your database file will not exist in that folder unless you put it there somehow. There are a couple of ways to do that. I don’t want to overwhelm you with the various options, but you are free to chose one based on your comfort level and preferences.
Run Paused, instead of just Run. Then copy the file into the .debug folder and Resume.
Create a CopyFilesStep in the IDE to copy the file into the debug folder automatically. You create this step in the Build section of the IDE.
Place the file into a folder structure under ApplicationData, so it’s always available regardless of whether you’re running the debug or built version of the app.
I hope this is starting to make sense. I know it can be confusing.
Hi Tim,
First of all can I thank you heaps for all your help. I’m not sure if you are aware but over the years I have asked many questions as I have worked to learn Xojo and you are one of the ones who always jumps in to help, it is VERY much appreciated.
Anyway, SUCCESS!! Of your 3 options, I tried the Run Paused and copied the file when it stopped but, the problem was, the Resume button always remained greyed out so I couldn’t restart.
Using the NativePath still didn’t reveal the ApplicationsData folder, so is it possible Apple have changed things and removed that folder?
But, the CopyFilesStep worked beautifully I didn’t even know that existed. However that leads to a couple of followup question please.
Is CopyFilesStep something that is only executed during the Build when developing the app or is built in when the final compile occurs to create the standalone app?
Either way, when the database file is copied to the new location using this command, are any changes to the data transferred back to the original file or does that one stay unchanged?
Finally, a question re Web apps in general. As I said earlier I have these apps working for me on my Mac. Will rewriting them to work on a browser allow me to run them on my iPad or do I have to use iPadOS still? If yes, how do I access the finished app from the iPad?
Thanks again for your help
Barry
PS. Thanks also to the others who have offered advice. Greg - I know you suggested CopyFilesStep but I didn’t understand that at the time
The CopyFilesStep is executed on both debug and final build.
The original file will remain unchanged.
Web apps run as a server. Any other client, such as your iPad, connects via the browser. That makes your web app OS agnostic. It runs the same from an iPad, a MacOS desktop, a Windows machine or a linux installation.
If you look at the inspector when the CopyFileStep is selected, you will see that there are some options that determine when it will be executed. Things like Debug, Build or Both (or neither if you just want to disable it but not temove it from the project), the processor type in case the thing you are copying is for Intel but not ARM or vice-versa, and then you can set the location relative to the app. .
As the name states, it Copies the original file(s) to a location near or inside the built app. These steps (as well as the two scripting steps) only run at build/debug time and don’t affect the originals in any way.
That depends largely on your goals. Technically, if you write your app as a web app, users can access your app using a browser on their device. As with any web site, there are challenges here. Mobile browsers are more picky about connection stability than desktop apps are and their connectivity can change based on their battery level. As they are mobile devices, you (and your users) need to be aware that a tablet or phone moving around in a facility will need to have a consistent connection, including its lock state. If your user is sitting at a desk, using the app, not switching back and forth to other apps and not locking their device or letting it sleep, the experience will be relatively good. Not saying that it’s a bad choice, but being aware of these issues can let you plan your app better.
A native iOS (and soon Android) app does solve some of the limitations of a web app, but it also has its own challenges. There’s no “network file sharing” for one thing, so you would need to provide a way for your app to do that, something that you could do with a web app and it’s App.HandleURL event. You also have a lot more control over the appearance when using a native app. Keep in mind though, developing an iOS app is very different than desktop and even web (I don’t know about Android as I haven’t touched it in about a year). Layout is handled differently and the iOS environment is very locked down in comparison with what you can do elsewhere. Again, not a bad choice, just different.
These two both have their pros and cons. You’ll have to weigh your project needs and possibly discuss them with the community (even if vaguely) to get some better ideas of what will work for you.
Hi Greg,
Thanks for the info. In regards to my “general Web app” question, maybe I should explain little more.
As I’ve always stated when looking for help, I am very much a hobbiest. I’m not a pro and my apps are purely for my use (and interest, recreation etc). I am in my early 60’s and semi-retired from my career but I do occasional casual work for a couple of employers as a contractor at various locations (not in the tech field). The app I have is one for keeping track of that work (who for, when, where, how many hours). The Mac version works great and the app and DB are kept together in a folder on the MacBook. There are times however when I don’t have my Mac but have my iPad or iPhone and I thought a Web app with the DB moved to my iCloud would enable me to access/change the info. Am I right or wrong in that assumption? I have tried to tackle iOS (both Xojo & Apple Swift) but the concepts etc are just too much and I eventually lose track if I’m following a tute etc.
So, that’s what I’m trying to do. I might add, I’m not a total novice. I first got interested in all this WAAAAY back when I had a Tandy color computer and got into Basic, then Basic on a PC, that led to Pascal, Turbo Pascal and, when I moved to Apples I found RealBasic and, obviously Xojo. So I have some understanding, just the VERY technical has gotten past me, I think.
So… the trick is that cloud folders like iCloud periodically lock files when transfers occur due to changes. I can’t help but think that you’re going to run into this roadblock when your app tries to run an INSERT or UPDATE query and the database is locked, either causing a crash or lost data. You can program for this, but it’s a lot of extra work and tricky to get right. Being able to access this file from multiple devices simultaneously just makes the issue even more complicated.
My instinct would be that when you launch the app on one of your devices, you look for changes in the iCloud folder and copy it to a neutral location for use while that platform is in use. Periodically you use the SQLite backup command to send a current copy back to iCloud. It would probably be worth having a second “lock file” that you put next to the iCloud file which indicates that it’s currently in use which is removed when an app is done with the file.
Thanks again.
I sorta get the concepts of what you are saying, but don’t know (yet) how to do all of that. You guys have been great. I’m slowly working through it all. I’m sure I’ll be back again with more as I work through it.
FWIW, I had a go last night at the same thing with iOS. It recognises the DB (same code as their Mac and Web), but the CopyFiles routine returns another unable to find error. If only it was all the same
