Hi,

While I think your heart is in the right place, I don't see this as very practical to implement for all database systems. The reason being is that some commercial DBMS vendors have literally thousands of possible errors with some being 'numbered' and others not. Also, depending on the version of the DBMS, a single error message might mean two completely different things.

If anyone was thinking of creating such a database of error messages, I would recommend to concentrate on the free and open source database systems first. Postgres, MySQL, SQL Lite, etc. If it becomes feasible, expanding it out to the commercial DBMS systems.

The real problem isn't so much the error messages but more of the context of the errors with respect to the application. I *do* like the idea though I suspect it would be better off explaning them at the application level instead. (duplicate key = dependents for Customer # 1883 already submitted. Would you like to resubmit them?)

my $errmsg = '';
my $sql = "INSERT INTO url (url, url_id) VALUES (?, ?)";
my @bind = ( 'blah.com', 3 );
$dbh->do($sql,{},@bind);
if( $dbh->err ){
  warn "INSERT url FAILED: " . $dbh->errstr;
  $errmsg = "There was an error adding the URL";
}
[download]

if( $dbh->err ){
  warn "INSERT url FAILED: " . $dbh->errstr;
  if( $dbh->err eq '123' ){
    $errmsg = "Please fix ___ before adding the URL.";
  }elsif( $dbh->errstr =~ /not unique/ ){
    $errmsg = "This URL already exists.";
  }else{
    $errmsg = "There was an error adding the URL";
  }
}
[download]

my $tmp_id = $dbh->selectrow_array("select url_id from url where url =
+ ?",{},$url);
if( $ct ){
  $errmsg = "The URL '$url' already exists w/id=$tmp_id.";
  return;
}
...
INSERT INTO ...
[download]

If you don’t want the user to see the nitty-gritty, there’s no point in parsing it before showing it to them – just don’t show it at all. Trap the error and make it a generic “Something went wrong” message to the user, and dump the real message to the error log.

Sorry, no. If a user fills a registration form and picks a username that already exists, and the database rejects the row because it violates the uniqueness constraint, I don’t want to tell the user “something went wrong,” nor do I want to tell them “column username is not unique(1) at dbdimp.c line 397.” I want to tell them “the username you picked is taken, please try another.”

Don’t repeat good-sounding advice without understanding where it applies.

Even better is to try to trap the conditions that create the error

That’s not very clever either. Now you have to keep the database schema constraints and the application error checks in synch – a violation of Don’t Repeat Yourself.

I’ve been thinking about this particular problem of mapping from schema constraint violations to user error messages, lately. It’s neither a trivial problem nor one that anyone seems to have done much work on. The usual answer is the one you gave – check constraints in the client code. The more clueful people will advise to check only in the client code, to avoid the synchronisation problems. I don’t think that’s anything approaching a good answer. The constraints belong in the schema; all else violates separation of concerns.

Some means of annotating constraints is the solution, but what form it should take is a hard question.

Makeshifts last the longest.

I think I'm pretty much in agreement with this advice, primarily because of your last line: Some means of annotating constraints is the solution, but what form it should take is a hard question.

That's the million dollar question that most cannot solve for any reasonbly complex system. For constraints which are clearly global (your password can never be longer than 32 characters), then it's OK to trap and report them in client code. The problem with allowing the database errors to bubble up is that you theoretically want to parse the error message to make it more understandable to a user.

For my work, I want to follow that Pareto rule: 80% of your results stem from 20% of your actions. With this, if I can properly identify the most common errors users are likely to commit, I focus on eliminating the possibility then can commit these (give them a country drop down intead of a text field where they can type the country name) or give them a useful error message for the common cases rather than ever hit the database. Those situations, when properly identified and combined with an intuitive interface, should ameliorate the bulk of application problems. For situations you've not anticipate/covered, you'll have to figure out a reasonable solution and what that solution is generally needs to be customized for an application as "one size fits all" approaches generally mean "one size fits none".

Fortunately, with a well-designed database, many of the constraints are easy to identify. One-to-many relationships imply a select list if you're using HTML. Length constraints in fields imply that you can easily push those constraints to forms (try entering long passwords on Perlmonks, for example, even though you can theoretically enter a longer one). Data level constraints (age must be greater than 20 and less than 65) are more difficult to handle in a generic matter. Whether or not it's worth the programmers time to handle all of those depends upon your business needs.

Don't take this as criticism, though. I completely agree with what you're saying. I'm just filling in some detail :)

Cheers,
Ovid

New address of my CGI Course.

This _is_ a hard problem, but I personally believe that an elegant solution will present itself some day. I've always assumed the solution would be either to generate some parts of the application code from the db schema, or generate the schema from the application code, or both from a common source (like a sql schema with annotations).

There are lots of partial solutions like this (eg the *::Loader modules to read the schema. Solutions like DBIx::DBO2::Fields to set column level contstraints (its in the TODO to read these from the db schema). But I've not seen anything that puts it all together.

• many DBD's don't implement any/all SQLSTATE mapping
• some DBMS vendors have resisted support for SQLSTATE (e.g., Oracle)
• most DBMS's have significant numbers of reported errors that do not map to any "standard" SQLSTATE
• in some cases, a single SQLSTATE maps to multiple error codes for a single DBMS

You may be able to find SQLSTATE mappings for most dbms's, which you could then stick into a DBMS, along w/ their associated error code(s). And JOIN away...but be sure to use OUTER JOINs, cuz theres going to be a lot of NULLs in there.

I've been looking for a similar solution (but only for SQLSTATE <=> specific error codes), and its proven quite a task. I had hoped Google would surface enough pieces of the solution that I could pop into a dbms and boil down, but thus far I've had limited success.