Custom constraint violation error messages with TMDBC

January 12, 2010 at 9:52 pm | Posted in PostgreSQL, TMDBC | Leave a comment

One thing I’ve never seen is in database client libraries is good, boilerplate-free support for supplying ones own error messages for integrity constraint violations, so I’m pleased to have added it to TMDBC. The standard messages the database gives when a constraint is violated aren’t really useful to present to end-users as-is, constraint names would for example be just plain confusing to a lowly end-user. I’ve solved this with the option of providing a custom list of error messages, optionally containing the data that was given as arguments, for each pair of DML command (well, any SQL command really) and constraint (or not-null column). If a constraint is violated and a custom message is found it will be used in place of the one provided by the database.

Enabling it requires first that a specific exception is added to the method-mapped-to-SQL-command in question to signal that it may throw an exception due to a constraint violation. A generic method on an interface could then look like this:

 @SQL(paramNames={"emp.salary", "emp.name"},
         value=
            "INSERT INTO employee ( salary, name )\n" +
            "VALUES ( ?, ? )\n" +
            "RETURNING emp_id")
 int insertEmployee( Employee emp ) throws ConstraintViolationException;

ConstraintViolationException has two methods that provide the name and type of the violation, but it’s not neccesary to use them for anything. The exception will look up a properties file named constraint-violation.properies in the package the of class with the @SQL annotated method to see if there is an error message defined for it. If the “name” column of the “employee” table has a unique constraint named “employee_name_key” and the method above is in an interface named “Sample”, then this file could contain:

Sample.insertEmployee.employee_name_key=Cannot add a new employee with name '%2$s', one already exists with that name.

If employee_name_key is violated during invocation of that method then the detail message of the exception would contain that text, with the second parameter to the SQL command interpolated into the message. One can also provide only the name of the constraint to provide a default message whenever it is encountered in the given package, but those cannot reference the arguments given to the SQL command as above.

Since this uses Java properties files it’s easy to localize them. To provide error messages in my native language I’d use one of the ways TMDBC supports providing a error message locale, and create a constraint-violation_no_NO.properties file with the following:

Sample.insertEmployee.employee_name_key=Kan ikke legge til en ny ansatt med navn '%2$s', det finnes allerede en ansatt med det navnet.

And that’s pretty much all there is to it.

This is tagged PostgreSQL because…

Short answer: It’s PostgreSQL specific.

I haven’t found any interfaces that let’s me get the names and types of constraint violations, so for now I’m parsing the error messags I get from the database. Since so far TMDBC only supports PostgreSQL I’ve hard-coded a set of regexp’s to check the response and fish out the constraint name if there’s a match. A stable interface that will provide that, both for database independence and stability across versions, is one of the things I’d like to see in PostgreSQL, but this approach works for me today and would be necessary for current releases anyhow.

Advertisements

Leave a Comment »

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: