Connection issue (maybe a bug?)

[stoffman]

Hi,

I encounter what I think is a bug.

1. I'm connecting to a database and performing a query. Works fine.
2. I can keep using this TZReadOnlyQuery and issuing new queries without a problem
3. After some period of time, if I don't use it, I issue a query and get an exception "SQL Error: no connection to the server"
4 [The bug]: When checking TZReadOnlyQuery.Connection.Connected the result is TRUE. while I expect it to be FALSE

I'm working with the postgresql driver, windows 10 and lazarus 2.0.10

Any ideas?

Thanks,

[marsupilami]

Hello stoffman,

I think this is expected behavior. TZConnection.Connected is what Zeos thinks about the conenction state. It doesn't reflect the connection state. Also we don't get notified when the connection gets disconnected for some reason. We only get this information when we try to do something. Assume a situation like this:

1) Connect to the SQL Server -> TZConnection.Connected is true
2) Do something -> TZConnection.Connected is true
3) Remove the network cable -> The operating system inernally closes the connection. libpq and Zeos will not know that. -> TZConnection.Connected = True
4) Try to do something, like do a query -> libpq tries to use the network connection and get's an error state. -> Exception is raised. In Zeos 7.2 you are doomed. Zeos 7.3 will clean up nicely and you will need top reopen the connection.

I wonder how your connection becomes disconnected. According to the PostgreSQL documentation on connection strings, the keepalives option should be enabled by libpq. Zeos supports setting the parameters that control tcp keepalives:

• keepalives
• keepalives_idle
• keepalives_interval
• keepalives_count

Each of these parameter translates directly into the according libpq parameter from the documentation mentioned above.

Best regards,

Jan

[stoffman]

Hi Jan

First I would like to thank you for the answer.

So I wish to suggest a little change that will make Zeos a little more robust if I may: currently I've to parse the error message of the exception to know what exactly happen so if you can make a specific a ConnectionLostException that would be great. And let the user decide what exactly can or should be done.

I know about the keep_alive option but in many cases it will not help (like in the example you gave, when the network cable is removed) so having an explicit Exception will help the user to handle that case more easily.

Thanks,

[marsupilami]

Hello stoffman,

So I wish to suggest a little change that will make Zeos a little more robust if I may: currently I've to parse the error message of the exception to know what exactly happen so if you can make a specific a ConnectionLostException that would be great. And let the user decide what exactly can or should be done.

As far as I know that is what happens in Zeos 7.3/8.0:
All Datasets are closed and the OnConnectionLost event is called on TZConnection.

Best regards,

Jan

[Fr0sT]

Btw, there is abstract TZExceptionSpecificData class and property EZSQLThrowable.SpecificData: TZExceptionSpecificData for DBC-specific data if Errorcode or SQLcode is not enough. Currently implemented for Firebird only AFAIK but could be extended for other drivers as well