0

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

kostas
  • 75
  • 1
  • 13
  • The nice thing with TCP is that the **connection will come back** if you plug the cable in again. – mjn Oct 19 '14 at 13:34
  • See (related to Datasnap): http://stackoverflow.com/a/556096/80901 – mjn Oct 19 '14 at 13:38
  • Related (BDE): http://stackoverflow.com/questions/885686/how-can-i-keep-my-db-connection-from-failing-when-the-network-is-unstable – Jan Doggen Oct 19 '14 at 13:59

1 Answers1

0

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

Community
  • 1
  • 1
mjn
  • 36,362
  • 28
  • 176
  • 378
  • @mjin, thank you for your answer. So if I get this right, since I am using adoqueries for the appropriate tables of my db, and since I open them only when the relative forms ( the grids inside these forms), need to parse their data which then close, I shloudn't worry. Well I don't know because... in one of my forms I have a grid that through "bottlesQ" adoquerry parses the table bottlesT of my db successfully. Now if I loose the net cable and scroll I won't have any problem since I already have the data and the "bottlesQ" is closed. But if I try either to insert/edit or delete a row, then – kostas Oct 20 '14 at 15:33
  • i have problem. That is because the "keep" parameter of the adoconnection1 is being put to "true" and thus even though the connections is lost the adoconnection1.connected property is still "true" !!! And there is no such event or property - as far as i know - not in adoconnection1 or in my example table "bottlesT" that can ask about the state of the connection before any transaction take place. the trick with the timer loop seems to give a bypass to this obstacle. However i am not aware which exactly event or property of the adoconnection1 - or other control - should i check in this loop – kostas Oct 20 '14 at 15:40
  • in order to examine the state of the db connectivity. Moreover i don't know whether this loop will make much more heavier the whole application leading to time gaps during its execution. If you could provide me any code example how to handle this issue i would be much obliged. – kostas Oct 20 '14 at 15:44