I have a Web app that connects to a SQLite database, and all is working great. Only a few users (< 20) hit the database, a few times a week each. I have the app connect with the database in the App.Open where it has an App Property point to the database. The only other thing that happens in App.Open is a timer is created that shuts down the app in 5 seconds if the CGI file is missing (Phillip Zedalis code over at 1701 Software).
However, after noticing something in the documentation of Session, it looks like I should use Session.Open to store reference to the database in a Session Property.
Accommodating that change will not be difficult, as I can use Search/Replace to adjust all code for it.
What is the negative consequence of connecting to the database via the App object?
Transactions happen per connection. Putting it in the session means that each ‘user’ has their own connection and thus can do proper database transactions.
Thanks, Bob. Right now, all database interactions have immediate commits and don’t need transactions. But I now see the negative consequence of using App instead of Session, in terms of transactions.
In terms of the App object itself. I had thought that each user Session ran App.Open, but I’m starting to think I had that all wrong. I assume now that App.Open and Session.Open are run once when User1 hits the app, and when User2 hits the app a few minutes later, Session.Open is run again (this time for User2), but App.Open is not run for User2.
App.Open fires one time when the application launches. Session.Open fires once for each new user session and is specific to that user.
No. The App only has one instance. Sessions are created for each connection to the app.
You can get away with the db in app but just realize that it’s not the ideal way of doing and if you ever get into transactions or parent-child datasets you’ll need to move it into the Session.
FWIW, we use both since a lot of our apps can do things on the main app thread without a session. And because threads should have their own connection we also do some checking to see if we’re on the main thread, a session thread, or another thread. If in a non-main, non-session thread we create a new connection specific for that thread and then do some clean up later on.
Thanks, Bob. I have moved the database connection method and its property, from App to Session. I imagine I’ll be needing transactions in the near future anyway.