delphi - How can I detect that a TadoConnection lost the communication with the server?

Question

I need to detect when a TAdoConnection component has lost the connection with the server. I've tried using the OnDisconnect event but this only fires when the Close method is called or the Connected property is set to false.

Another option I've tried is using a TTimer and executing a query like this

SELECT 1 RESULT FROM DUAL

in the OnTimer event, catching any exception that occurs.

Is there a better option to detect that the connection was lost?

Answer 1

I see the DUAL table. Means, you are using the Oracle :)

For most (all ?) client / server DBMS's there is no way to detect, that a connection is losted, other than to ask a DBMS for some action. And there are a lot of reasons, why a connection is losted. May be a network failure, may be ..., may be a DBA shutdowned a DB.

Many DBMS API's, including Oracle OCI, have the special functions, allowing to ping a DBMS. The "ping" is a smallest possible request to a DBMS. The above SELECT requires much more job, than such ping.

But not all data access components, including ADO, allows to ping a DBMS, using the DBMS API ping call. Then you have to use some SQL command. So, the above SELECT is correct with ADO. Other option - BEGIN NULL; END;. It may be using less DBMS resources (no need for optimizer, no need to describe a result set, etc).

TTimer is OK. The query should be performed in a thread, where the corresponding connection is used. Not a must although, but it is a different issue.

The potential problem may be to close a connection, when a connection is losted. As a connection closing may raise an exception due to the DBMS API may be in a failed state.

Kind of that ...