
Checking for Oracle Conection
Quote:
>We're using a VB5 ODBCdirect connection to do some simple updates on
>some records in an Oracle 7.3 database. Now we need to create
>contingency code for the times when the database is down. The current
>idea is to create an "OracleUp()" function to check that the database is
>there.
>There are a couple of ways this could go and I'm looking for comments.
>One idea is to establish the connection during the form load and then
>check the properties of the connetion before updates. I'm not sure if
>these properties will be current if the connection has not been used.
>So it may return true because it got a connection during form load even
>though the connection has been lost.
>The other idea is to establish a new connection in the OracleUp()
>function. I'm guessing this will take too much time.
>The last idea is to call Oracle's TNSPING.EXE.
>Any experiences/comments apprecitiated.
>Patrick Murphy
>Database Administrator
>Cutrale Citrus Juices, USA
If you have resource limits enabled or do a shutdown immediate, your
VB app can think it is connected when in fact it is not. The simplest
thing to do would be to issue a SELECT USER FROM DUAL statement within
your OracleUp funcion. If the connection is good, you won't get an
error. If not, you can examine the error number to determine why the
connection is gone.
We found the hard way that this isn't necessarily going to catch all
the connection problems. For instance, a lot of our customers do a
shutdown immediate before starting the backup. In this case, the app
can be right in the middle of a query and still get knocked off. We
created a centrailized error-checking routine to determine the cause
of most Oracle errors.
HTH
--
Mark Wagoner
To reply, remove no.spam from my e-mail address