I am building an application which retrieves data from an sql server. The specifications are the app to be running in either one or more machines which, however, are in the same LAN with the one that the sql server is hosted. 5 days ago I faced the situation about what would happen if for some reason the network cable of either one of the computers that run the app, or the computer that hosts the sql server, gets unplugged. I realized that the application gets frozen and then Delpi-7 produces error messages. Eventually the app terminates in an unfriendly way.
I tried to find a way to handle this event trough the "connection:= false" of the ADO1 control or through the table events that the applications was retrieving at the time of disconnection but unfortunately I failed.
So I am asking whether there is a way to detect when the LAN network cable - or the connection with the sql server - gets loose, so to show a message to the user to inspect his connections and - In the worst scenario - to end the application in a more friendly way (e.g. using the "application.terminate" command).
Thank you in advance
Usually, Delphi database clients raise exceptions in case of errors, which you should handle in your application code, by showing error messages, writing log files, etc., before terminating gracefully or re-trying (doing a re-connect).
If your code uses transactions (which is highly recommended), the connection loss should be painless.
You can also run 'health-check' queries in regular intervals to detect connection loss early. This can be done for example in a connection pools with a background thread which detects stale connections. These 'ping' queries also keep connections active, which can be an advantage in some environments.
A different strategy to make the application less vulnerable is to keep connections and transactions as short as possible. YMMV.
See also: Efficient SQL test query or validation query that will work across all (or most) databases