FreeTDS Development Group

["James K. Lowden" <jklowden AT freetds.org>]

martin A. wrote:
> Hello, I have a question about connection and disconnection. How can I 
> detect if a network connection is dead using db-lib? I have seen that
> there  is a function called  dbdead, but this function does not check 
> if the  network socket is alive ( I use  MS Sql Server), it never
> connects to the  server... 

The DBDEAD() function simply checks the status of the connection as
maintained by lower levels.  When the connection is dead, the library
marks it so.  As in so many things, it's easy to know if something's dead;
it's much harder to know if it will be dead soon.  

TCP has no heartbeat mechanism that I know of; TDS definitely doesn't. 
For that reason, idle connections are strictly idle: no packets are
exchanged.  If the remote end dies unexpectedly -- say, the machine loses
power -- the remote OS has no opportunity to tear down the connection, and
the local OS never receives a packet indicating the connection should be
closed.  The local status will remain "open" until a write fails or a read
times out.  

> I need this because I have a connection pooling and I havte to 
> detect connections dead to reconnect if it is needed, I keep the
> connection  without calling a dbclose for a long time. But if the dbdead
> does not check  the socket the result of calling dbdead is not reliable.
> How can I solve this problem?

The correct solution IMHO is not available to you yet.  It is described by
the db-lib docs, but not correctly implemented in FreeTDS.  

What you should be able to do is set a short timeout, say 5 seconds, and
an error handler that understands what's happening.  You issue your query.
 For easy queries, the server responds quickly, no problem.  For longer
ones, the error handler knows to wait, and tells the library to retry. 
When the error handler loses patience, it can tell the library to cancel
the operation, returning an error to the caller of dbnextrow() or
whatever.  

AFAIK that's been the standard answer for 20 years.  We're almost there.  

Regards, 

--jkl