Detecting Errors from SQLException

[Heinz Kabutz]

A problem I've encountered again and again is detecting what went wrong when
I get a SQLException. The most obvious suspicion is that the query sent
to the connection was faulty, but sometimes the program might be running
on a flaky network and the connection to the database might go down. Another
source of SQLException, when executing a query, is when a deadlock is detected
on the server and the query is killed.

The solution I always use for this is to have a known safe query, for example
something like "SELECT 1". If that passes, the connection is ok; otherwise
we have to try and reconnect to the database. The next question is whether
a deadlock appeared, or whether the query was incorrect.

Have you encountered this problem before? How did you resolve it? The best
way is probably to use the SQLState String inside SQLException. Have you
ever used that?

Regards

Heinz

[Bjarki Holm]

Heinz,

this is a common problem, especially when dealing with a complex data store
setup. The best way to detect specific errors, is either to evaluate the
vendor-specific SQL message provided by the exception with SQLException.getMessage(),
or to look up the XOPEN error code returned by SQLException.getSQLState().

What RDBMS are you using?

- Bjarki

[Heinz Kabutz]

Bjarki,

The RDBMS should not have anything to do with the solution - or rather -

I'm looking for a solution that will work on ALL RDBMSes and ALL JDBC
drivers.

Have you implemented such a solution using the XOPEN codes?

Have you found that the JDBC drivers correctly provide the XOPEN codes?

The culprit is Microsoft SQL Server running over the JDBC-ODBC bridge. If
you know the bugs, and how to avoid them (e.g. getTimestamp() produces a
native code memory leak) then it's actually quite usable.

Hopefully if this is a common problem there'll be a common solution.

Heinz


"Bjarki Holm" <holm@vyre.com> wrote:
>
>Heinz,
>
>this is a common problem, especially when dealing with a complex data store
>setup. The best way to detect specific errors, is either to evaluate the
>vendor-specific SQL message provided by the exception with SQLException.getMessage(),
>or to look up the XOPEN error code returned by SQLException.getSQLState().
>
>What RDBMS are you using?
>
>- Bjarki

[Bjarki Holm]

Heinz,

yes, I have actually used the XOPEN codes to identify errors, with good results
(at least with drivers from Oracle, Ashna Inc. and Merant). My company had
a copy of the XOPEN SQL specification, which I used to do the mappings.

I tried to find this specification for you, but with no success. I'll let
you know if I come across it - you might want to check out xopen.org and
send them a query.

- Bjarki