detecting a dead connection

Hi all -
I need a way to detect a dead connection.
I realize there has been a problem with SQL_ATTR_CONNECTION_DEAD , so
I use
SQLGetConnectAttr with SQL_COPT_SS_CONNECTION_DEAD instead.
I still dont get the expected result.
(btw, SQL_COPT_SS_CONNECTION_DEAD is defined as
SQL_ATTR_CONNECTION_DEAD. What kind of a fix is that?)
Any help appreciated,
Gideon.
I'm curious what problem you've heard exists with SQL_ATTR_CONNECTION_DEAD?
Why do you need to test whether or not the connection is dead? Just try to
use it and have the correct error handling in place.
What happens if you successfully test that the connection is alive, and then
it dies a second later? You have to handle that error case anyway, so why
worry about testing to see if it's dead or not?
Brannon
"Gideon" <grashkes@.yahoo.com> wrote in message
news:fb0829e1.0409170355.70ffd16b@.posting.google.c om...
> Hi all -
> I need a way to detect a dead connection.
> I realize there has been a problem with SQL_ATTR_CONNECTION_DEAD , so
> I use
> SQLGetConnectAttr with SQL_COPT_SS_CONNECTION_DEAD instead.
> I still dont get the expected result.
> (btw, SQL_COPT_SS_CONNECTION_DEAD is defined as
> SQL_ATTR_CONNECTION_DEAD. What kind of a fix is that?)
> Any help appreciated,
> Gideon.
|||Hi Brannon, and thanks for you reply.
I've done some more research and here's what I came up with.
Lets focus for now solely on SQLGetConnectAttr.
I need to use it to find out whether the connection is alive or not
before I attempt to use it, as I want to refrain from using exception
handling in this case.
I've found out that on SQL Server 2000, the const
'SQL_COPT_SS_CONNECTION_DEAD' has a different value than
'SQL_ATTR_CONNECTION_DEAD' (1244 and 1209, respectively).
However, I am still experiencing some strange behaviour.
Using SQLGetConnectAttr with 'SQL_COPT_SS_CONNECTION_DEAD' on a dead
SQL Server connection (i.e. using a SQL Server 2000 driver) I can
detect that the connection is dead. After reconnecting however, the
function does not detect that the connection is running again, and
says that the connection is dead still.
As my system needs to work with Oracle and DB2 connections as well,
things get messier.
With DB2, there's no problem, other than I have to use the old const
'SQL_ATTR_CONNECTION_DEAD' to make it work.
With the Oracle driver, neither seem to work.
To sum up:
On SQL Server - I cannot detect that the connection is running again.
On DB2 - fine.
On Oracle - the function always says the connection alive.
Thanks for your help,
Gideon.
"Brannon Jones" <brannonjNOSPAM@.gmail.com> wrote in message news:<#mv$brOnEHA.1412@.tk2msftngp13.phx.gbl>...
> I'm curious what problem you've heard exists with SQL_ATTR_CONNECTION_DEAD?
> Why do you need to test whether or not the connection is dead? Just try to
> use it and have the correct error handling in place.
> What happens if you successfully test that the connection is alive, and then
> it dies a second later? You have to handle that error case anyway, so why
> worry about testing to see if it's dead or not?
> Brannon
>
|||Hmm, I think that the SQL driver must cache the state of the connection
after the first check. Sounds like a bug.
But I don't think you should use the SQL_ATTR_CONNECTION_DEAD attribute at
all (or SQL_COPT_SS_CONNECTION_DEAD). You need to have code that can handle
disconnect errors anyway, so why have a special check? Also, the bigger
problem is, what happens if you detect that the connection is alive, and
then try to use it, and during that brief period of time, the connection
dies? If you don't handle that case, then your app will experience random
weird behavior. However, if you correctly handle that case, then you don't
even NEED to detect if the connection is alive or not.
Brannon
"Gideon" <grashkes@.yahoo.com> wrote in message
news:fb0829e1.0409181058.74858989@.posting.google.c om...
> Hi Brannon, and thanks for you reply.
> I've done some more research and here's what I came up with.
> Lets focus for now solely on SQLGetConnectAttr.
> I need to use it to find out whether the connection is alive or not
> before I attempt to use it, as I want to refrain from using exception
> handling in this case.
> I've found out that on SQL Server 2000, the const
> 'SQL_COPT_SS_CONNECTION_DEAD' has a different value than
> 'SQL_ATTR_CONNECTION_DEAD' (1244 and 1209, respectively).
> However, I am still experiencing some strange behaviour.
> Using SQLGetConnectAttr with 'SQL_COPT_SS_CONNECTION_DEAD' on a dead
> SQL Server connection (i.e. using a SQL Server 2000 driver) I can
> detect that the connection is dead. After reconnecting however, the
> function does not detect that the connection is running again, and
> says that the connection is dead still.
> As my system needs to work with Oracle and DB2 connections as well,
> things get messier.
> With DB2, there's no problem, other than I have to use the old const
> 'SQL_ATTR_CONNECTION_DEAD' to make it work.
> With the Oracle driver, neither seem to work.
> To sum up:
> On SQL Server - I cannot detect that the connection is running again.
> On DB2 - fine.
> On Oracle - the function always says the connection alive.
> Thanks for your help,
> Gideon.
> "Brannon Jones" <brannonjNOSPAM@.gmail.com> wrote in message
news:<#mv$brOnEHA.1412@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
SQL_ATTR_CONNECTION_DEAD?[vbcol=seagreen]
to[vbcol=seagreen]
then[vbcol=seagreen]
why[vbcol=seagreen]
|||Hi again.
What I initially wanted to do was this:
In my exception handling, find out if it was a disconnection error and if
so, try to reconnect and then deal with it.
The problem with that was, that the SQLStates for disconnection errors, are
different from one version of a driver to the other;
I dont want to have to maintain a list of SQLStates that may change when a
new version of one of the drivers I support comes out.
I have also found on a few occasions that the SQLStates are not %100
reliable. In some cases, it throws a general warning when a disconnection
has occurred, and that isnt specific enough.
So I needed an alternate way of detecting a disconnection.
Thanks again for your help,
Gideon.
"Brannon Jones" <brannonjNOSPAM@.gmail.com> wrote in message
news:OdNunD1nEHA.556@.tk2msftngp13.phx.gbl...
> Hmm, I think that the SQL driver must cache the state of the connection
> after the first check. Sounds like a bug.
> But I don't think you should use the SQL_ATTR_CONNECTION_DEAD attribute at
> all (or SQL_COPT_SS_CONNECTION_DEAD). You need to have code that can
> handle
> disconnect errors anyway, so why have a special check? Also, the bigger
> problem is, what happens if you detect that the connection is alive, and
> then try to use it, and during that brief period of time, the connection
> dies? If you don't handle that case, then your app will experience random
> weird behavior. However, if you correctly handle that case, then you
> don't
> even NEED to detect if the connection is alive or not.
> Brannon
> "Gideon" <grashkes@.yahoo.com> wrote in message
> news:fb0829e1.0409181058.74858989@.posting.google.c om...
> news:<#mv$brOnEHA.1412@.tk2msftngp13.phx.gbl>...
> SQL_ATTR_CONNECTION_DEAD?
> to
> then
> why
>
|||Ah .. I see the problem. It's unfortunate that SQL States can vary between
drivers. I don't have any good suggestions though.
Brannon
"Gideon Rashkes" <gideon@.theglcompanyMAPS_ON.com> wrote in message
news:e1dB1FJoEHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi again.
> What I initially wanted to do was this:
> In my exception handling, find out if it was a disconnection error and if
> so, try to reconnect and then deal with it.
> The problem with that was, that the SQLStates for disconnection errors,
are[vbcol=seagreen]
> different from one version of a driver to the other;
> I dont want to have to maintain a list of SQLStates that may change when a
> new version of one of the drivers I support comes out.
> I have also found on a few occasions that the SQLStates are not %100
> reliable. In some cases, it throws a general warning when a disconnection
> has occurred, and that isnt specific enough.
> So I needed an alternate way of detecting a disconnection.
> Thanks again for your help,
> Gideon.
>
> "Brannon Jones" <brannonjNOSPAM@.gmail.com> wrote in message
> news:OdNunD1nEHA.556@.tk2msftngp13.phx.gbl...
at[vbcol=seagreen]
random[vbcol=seagreen]
and[vbcol=seagreen]
so
>