in reply to Mapping database errors to user errors

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. 
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 there's a few known errors, you could check for those to display a different user message: 
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]
Even better is to try to trap the conditions that create the error .. for example, in this case, a SELECT to see if the "url" column already exists could be done first and if a row is found toss an error to the user. 
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]
One more advantage of using the last two code blocks above is that you have code that's hit IFF a certain error condition arises, so if you check the test coverage it won't be 100% for these blocks unless you have explicit tests for those errors -- so it explicitly outlines the errors in the code, and makes it easier to list the possible run-time conditions to test for.

Replies are listed 'Best First'.
Re^2: Mapping database errors to user errors
by Aristotle (Chancellor) on Nov 26, 2005 at 01:25 UTC

    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.

[reply]

      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.

[reply]

      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.

[reply]

        I think what’s really necessary is basically for things that talk SQL to acknowledge that they’re not a world of their own; syntactic support for constraint annotation should be available in SQL so it can be done right in the schema. As well, details for errors raised should be available as structured data. Then something vaguely like Data::FormValidator could be built around this. I’m not quite sure of the line where the concerns would be split between database and client code, though. Absent of direct support in SQL, a lot of this might still be achievable with some sort of SQL preprocessor and a wrapper layer over the database, although the at least the wrapper will probably have to be rewritten for each RDBMS.

        A great many details need to be thought through, and so far all I have is vague intuitions and ill-defined gut feeling. Maybe all I wrote above is wrong.

        The scope of the problem is staggering; I don’t believe I can surmount it on my own. But no one seems to have a particularly avid interest. I am disheartened to find how little work has been done in this area, generally. We’ve been using RDBMS for how long, now? The sorry state of error reporting in that field is actually known well enough. Surely, that would have gotten notice so far. But no, everyone just seems to scrape by with kludges and minimal solutions. I really don’t understand this.

        Nevertheless, a sketch of what it all should work like seems feasible, so I occasionally spend time scribbling and mulling.

        Makeshifts last the longest.

[reply]

In Section Seekers of Perl Wisdom