In our development efforts, we typically connect to the database when our applications start and assume that connection persists throughout the period that they are using our software; however, we are running into nilobjectexceptions that we have identified as really a loss network connection.
Unfortunately, it would be impossible to retrofit our applications to reconnect every time the user needs access to the database.
We have identified a number of cases where this error is caused because they have a screen saver set and whenever it kicks in they are disconnected from the network.
Obviously, the easy fix would be to determine if we could connect to the database whenever they needed to access it, but even this may not completely solve the problem since they could be sitting in the middle of an “add” or “edit” routine and because they leave their computer unattended it may lose the connection with their database.
Anyone else run into issues like this and have any suggestions?
Our middleware is all thread-based, and each thread gets, keeps, and uses a database connection. When done, the connection is closed, so no, we haven’t run into this.
But since you would prefer not to retrofit, what about something like this?
Presumably you get the database from a central location, like an App property. Turn that into a method instead. Within that method, keep track of the last time the connection was accessed. If it is longer than x seconds/minutes, test it. If it fails, create a new connection. No matter what, the accessing method will get a valid connection.
(Based on hunches about your code, of course. You actual code may make this suggestion impractical, but may give you ideas on other ways to tackle it.)
I pass the current database connection to the Method that creates the connection. This way it is created on the first run, if it is defined, I test if it is still connected with a SELECT 1, and if it has disconnected, it will reconnect, passing the new/existing/reconnected database to your routine.
you could use a class with setting super to the database class, that give you more control.
there i would add a method EnsureConnected with a boolean result.
so if you have a task call this Method first, it will reconnect.
somehow if App.DB.EnsureConnected = True Then … Else Message “not connected”
for database access use a transaction and error handling rollback + user message.
Sounds intriguing but I’m not sure I understand how to implement this. We do have a property named app.datadb that contains the connection to the database.
I also don’t understanding the “Select 1”, you mean do an SQLSelect for one record and see if it returns nil?
Turn app.datadb into a method as I described and use a shadow property like mdatadb to hold the actual instance. If you test it and it fails, you can reconnect and store the new instance in mdatadb.
And you just have to track the time between accesses since this is caused by a sleep condition. Every time datadb is accessed, store the time and compare it to the time of last access. If it’s more than, say, 1 minute, then SQLSelect("SELECT 1") and make sure there is no error. If there is, recreate mdatadb before sending it back to the caller.
btw why is there no event at xojo if a connection break?
especially tcp could offer this.
not see this in online documentation.
or is there a automatic connection on demand each time we use a query???
On a unrelated, but related topic, is there a way to get more pertinent information regarding where the error occurred? The stack information returned seems woefully lacking in details. Is this something you have to implement yourself if you want it?
There is no way to know if the database across a network isn’t available except for trying to access it and failing. There is no notification, tcp or otherwise, that the db is unavailable for whatever reason.
Because connections do not “break”. They just go away silently.
I use a timer, every 5 mins I send a select 1 to the database and it works.
of course it doesn’t deal with other interruptions of the connexion.
like if the computer goes to sleep and wakes up in another location.
but it is simple to implement.
A brief clarification here…TCP cannot ever tell you if a database is connected as TCP is transmission level communication. That means it can tell only if there is a valid path from your hardware to the target hardware. So while it’s true that if the TCP connection goes down you won’t be able to get to the database, the reverse isn’t a given as the database can become inaccessible while the connections between the hardware is still up and functioning.