SQLException reporting best practice

From: E. Lefty Kreouzis (lefty_at_localhost.localdomain)
Date: 11/11/04 

Date: Thu, 11 Nov 2004 22:04:29 +0200

Hi to all,

When one writes a Java Web Database application we get a lot of help in
validating syntactic input e.g. the commons validator framework,
the JSF validation framework etc.

However we might get some database error (e.g. a unique constraint
violation) that ideally we want to report to the user just as if it was a
more syntactic kind of validation error.

For example we might have a table Clients ( id int4 primary key, name text
unique, .... more stuff);

The various frameworks help us in ensuring that 'id' say is numeric,
however the only way we will know if 'name' already exists in the database
is if we try to insert a row and get an SQLException. In order for that to
be translated back to a meaningful application exception (eg 'Duplicate
entry for column "name"' or some such) means that we have to parse the
SQLException message string.

Is there an accepted best practice that makes this as simple (and portable
across databases) as possible?

Meaningful SQL exception handling is a shortcoming of all Object
Relational mapping tools I have come across.

Lefty Kreouzis