Mining SQLException for info

From: Ian Pilcher <i(dot)pilcher(at)comcast(dot)net>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Mining SQLException for info
Date: 2004-10-14 23:00:11
Message-ID: ckn0ds$8tu$1@sea.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

I am using Tomcat and PostgreSQL to create a browser-based "jukebox" for
my music collection. Actually, it's more accurate to say that I'm using
the jukebox project as a vehicle to learn Java, JDBC, servlets, JSPs,
SQL, etc. Either way, at this rate I should something quite usable in
about ten years. :-)

I'm currently wrestling with the problem of data integrity, and what
layer of the application should enforce it. I've identified three
possible high-level approaches:

1) Data integrity is enforced at the application (servlet) level
only. The database accepts whatever the application gives it.

2) Data integrity is enforced only at the database level. The
application attempts to put whatever the user inputs into the
database.

3) All data integrity checks are performed at both levels.

Ignore, for a moment, the fact that no application will ever fit
completely into one of these patterns.

Although option #1 may look silly, especially on a RDBMS-related list,
it does have one important benefit. The application can provide far
better feedback to the user if it has detailed information about what
the exact data integrity problem is. Nevertheless, I've ruled it out,
because there's really no point in using an RDBMS (at least not Post-
greSQL) if you're not going to use it's data integrity features.

Option #3 is somewhat tempting. The application will be able to
provide useful feedback to users about data problems, and the database
won't accept problematic data. Any data integrity violation at the
database level is, by definition, a bug in the application. Keeping
the two sets of data integrity rules in sync, however, will inevitably
prove problematic. It's also simply inelegant to do everything twice.

Which brings me to option #2, the option I've chosen. Given my desire
to provide good feedback, I want to squeeze as much information as
possible out of any SQLException that comes back -- at least those
related to integrity violations (23xxx) and exceptions in TRIGGER
functions.

The only technique I can come up with is to name all of my constraints
and, based on the code returned by getSQLState parse the String returned
by getMessage. Uugh!

Is there a better way that I haven't thought of?

If there isn't, are there anything I can do to make the message parsing
more robust -- across PostgreSQL version changes and different locales,
for example?

Thanks!
--
========================================================================
Ian Pilcher i(dot)pilcher(at)comcast(dot)net
========================================================================

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Dave Cramer 2004-10-14 23:08:31 Re: Mining SQLException for info
Previous Message Kris Jurka 2004-10-14 21:18:56 Re: RE : Postgres 8.0 + JDBC