Reconnect after timeout

[dwilbourn]

My application may go for hours between queries and so the Server often times out and closes the connection. As far as I can see, the only way to deal with this is to add

Code: Select all

if not(Myconnection.Connected) then
    Myconnection.Connect;

to every query's BeforeOpen, BeforeExecute, BeforePost, etc.... event. This seems very inefficient and poor coding to me! I have seem comments about using the OnConnectionLost event but I feel that is almost as bad, it seems it does not work in all circumstances and is really just a way to keep a connection open, even when it's not needed. In fact I have added the example code to my app and it does not seem to help. I feel I just need a centralized way to check for and re-open a connection before any attempt is made to access the DB. Is there some recommended way to do this - or I am completely missing something?

As a secondary question, I have been using both MyQuery.Open and MyQuery.Execute in my code. Both work fine but I see that MyQuery.Execute is basically a MyQuery.Prepare followed by MyQuery.Open. Would I be better off changing all MyQuery.Open to MyQuery.Execute (or the other way round)?

David.

[ViktorV]

1. MyDAC does not automatically initiate checking connection to the server. Therefore, after successfully connecting to the server if the connection to the server disconnects the TUniConnection.Connected property will be set to True and the OnConnectionLost event will not be initiated until there is an attempt to connect to the server.
To solve your task you can use the TMyConnection.Ping method. This method is used to test the connection to MySQL server if the connection was disconnected, there will be an error and an attempt to reconnect to the server.
As well this method can be used in order for the server not to close a connection due to timeout connection activity. In this case, you can use TMyConnection.Ping as follows - call it from the timer and set the interval timer to a value less than wait_timeout of the server (so that the intervals between commands receiving by the server do not exceed wait_timeout).
2. You can use the Execute method: if SQL statement is a query, the Execute method calls the Open method; the query will be automatically prepared if the TCustomDADataSet.Options.AutoPrepare property is set to True and the query has not been prepared yet: https://devart.com/mydac/docs/devart.da ... cute().htm

[dwilbourn]

Hi Victor,

Thanks for taking the time to reply, but I feel it does not really answer the question. It seems that the approved way to handle this is to just keep the connection open with pings - i.e. lots of dummy traffic. This seems very inefficient when there may be hours between real querys! Surely it would be better if there was some way to make a query and if it fails because of a lost connection, reconnect and try again?

David.

[ViktorV]

You can call the Ping method only when you want to check the server connection. The Ping method sends a small packet to the server and, if correctly used, the traffic will be minimal.
Also, please pay attention to the Disconnected mode. The Disconnected mode allows DataSet to work without an active connection. More details can be found at https://devart.com/mydac/docs/?work_disconnectmode.htm