Re: Connection reset on MS JDBC driver

From: Silvio Bierman (sbierman_at_idfix.nl)
Date: 02/12/04 

Date: Thu, 12 Feb 2004 23:11:43 +0100

"Andy Fish" <ajfish@blueyonder.co.uk> wrote in message
news:vlQWb.2874$Q21.28879119@news-text.cableinet.net...
>
> "Silvio Bierman" <sbierman@idfix.nl> wrote in message
> news:OwDx1jZ8DHA.4060@tk2msftngp13.phx.gbl...
> >
> > "Andy Fish" <ajfish@blueyonder.co.uk> wrote in message
> > news:c925c3dc.0402120636.46c141c4@posting.google.com...
> > > Hi,
> > >
> > > I have a problem with the MS JDBC driver whereby when I try to use a
> > > connection after a long time idle it reports:
> > >
> > > [Microsoft][SQLServer 2000 Driver for JDBC]Connection reset by peer
> > >
> > > I've read plenty discussion on the subject and am convinced that
> > > rather than trying to stop sockets from ever being closed, I need to
> > > detect and re-open the connection.
> > >
> > > I can't afford to rejig all the database code to detect this exception
> > > and retry so I want to have my home made connection pool test the
> > > connection before giving it out.
> > >
> > > My question is, is there a way of pinging the server through JDBC to
> > > see if the physical connection is still alive? If I have to do a SQL
> > > command, what would be a good example of something that is guaranteed
> > > to get through to the server but not take up too much processing or
> > > network?
> > >
> > > it seems to be accepted that any good connection pool should have this
> > > feature - so if anyone knows how they do it I would like to know.
> > >
> > > many thanks for any suggestions
> > >
> > > Andy
> >
> > That is possible but can be quite expensive. Why not keep a last-used
> > timestamp with the connections in the pool and refresh connections when
> they
> > are about to be taken from the pool and are older than some treshold.
This
> > approach works great in our long-running server applications.
> >
> > Silvio Bierman
> >
> >
>
> Thanks, I think I will use this approach.
>
> FWIW I am doing a 'select @@spid' command to test if the server is still
> there
>
> Andy
>
>
>

The query may be light, it still takes a full roundtrip which, when taking
network latency into account, can be quite noticeable, especially when using
small bandwidths.

Silvio Bierman