DBError after lost connection

Hello all,

I'm querying an Oracle database via ODBC, which is to say via an Oracle
client (lots of sloooooooowww Java).  It's working, at least until something
bad happens either to the server or to the network (not sure which), after
which executing SQL statements results in a DBError with text explaining
that the client is not connected to the server.

Do I need to parse the error text to detect the problem, or is there a
better way to distinguish a network connection problem from key violations,
etc.?

BTW, in this situation, I can probably just quit/reconnect on any DBError,
so I'm really asking about future scenarios in which I might be updating a
MySQL database under similar circumstances.

Have a good one,

Bill

--
Wilhelm K. Schwab, Ph.D.
[hidden email]

Bill Schwab wrote:
> Do I need to parse the error text to detect the problem, or is there a
> better way to distinguish a network connection problem from key violations,
> etc.?
>
> BTW, in this situation, I can probably just quit/reconnect on any DBError,
 [snip]

I'm not aware of any slick way to differentiate various DBErrors. Also,
I've seen a number of different error messages occur when a connection
gets broken, presumably depending on precisely what the driver was doing
when it detected a problem. That's the ODBC driver to MySQL in my case.

My approach has been to explicitly catch any DBError just while logging
in, and presenting the message to the user to correct and try again.
Problems with no connection or with the entered dataSourceName, userName
or password.

At other times, I use the general handling of unhandled errors [sic] and
separate out DBErrors. When one occurs then, it has always seemed to be,
at least in my case, some type of connection problem. I inform the user
and then after they hit retry (perhaps having reestablished a dialup
connection) have it quit and re-login to the database.

I do that without worrying about exactly what type of DBError it is. You
talk about "key violations". I'm not sure what that means. But if it can
occur unexpectedly during normal queries or updates, and a blanket
quit/reconnect for all DBErrors isn't appropriate, then you probably
will have to parse or match on the error text, or delve into the
DBExceptionDetails and DBErrorDetails to separate those out.

hope this is of some help,
-Bill

-------------------------------------------
Bill Dargel            [hidden email]
Shoshana Technologies
100 West Joy Road, Ann Arbor, MI 48105  USA

Bill,

> I'm not aware of any slick way to differentiate various DBErrors. Also,
> I've seen a number of different error messages occur when a connection
> gets broken, presumably depending on precisely what the driver was doing
> when it detected a problem. That's the ODBC driver to MySQL in my case.

What do you think of MySQL?  Which version?  I'm using 4.x on a few
machines, one of which is in pre-production use.  My first attempt (with 3.x
and 4.x) to use it on my primary cash cow didn't go well.  IIRC, you'll find
the details (CPU @ 99%+) are in the archives.  I tracked it down to one
query that didn't quite do what I wanted anyway, and replaced it with
something more useful, but less efficient.  So far, a few half-hearted
attempts to reproduce it on another machine haven't worked.  I'm ready to
give it another shot on the offending machine, but need to re-import the
Access data, which takes just enough time to be a small problem.


> My approach has been to explicitly catch any DBError just while logging
> in, and presenting the message to the user to correct and try again.
> Problems with no connection or with the entered dataSourceName, userName
> or password.

I'll keep that in mind for end-user kinds of things that might arise later.
My current problem is on an auxillary server, so bad user input really isn't
a problem.


> I do that without worrying about exactly what type of DBError it is. You
> talk about "key violations". I'm not sure what that means. But if it can
> occur unexpectedly during normal queries or updates, and a blanket
> quit/reconnect for all DBErrors isn't appropriate,

It would work this time; in general, I'd be constrained by it.  In some
situations, rather than query first and then conditionally insert or insert
vs. update, I've been known to go so far as to create an otherwise
unnecessary index just to deny duplicates, and wait for the DBMS to complain
if I try to do something bad.  It's not unlike keeping primitives simple,
and having other code waiting for the rare but complicated cases in which
the primitives fail.  Here it's not so much for speed as to keep my SQL
statement maintenance work to a minimum.


> then you probably
> will have to parse or match on the error text, or delve into the
> DBExceptionDetails and DBErrorDetails to separate those out.
>
> hope this is of some help,

It does - though I'll admit that I would have rather been proven wrong in my
assumption  ~:(

Thanks!

Bill

--
Wilhelm K. Schwab, Ph.D.
[hidden email]