Re: Mining SQLException for info

Well if you want to skip a number of steps in this process have a look
at tapestry, and hibernate.

tapestry is a web framework
hibernate is a persistence layer

Neither of which will answer your question directly, however tapestry
does a good job at 1, and 3 below.

Dave
On Thu, 2004-10-14 at 19:00, Ian Pilcher wrote:
> 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!
--
Dave Cramer
519 939 0336
ICQ # 14675561
www.postgresintl.com