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