On Wed, Mar 31, 2004 at 08:23:02PM -0800, Michael Peppler wrote:
> On Wed, 2004-03-31 at 17:56, Jonathan Leffler wrote:
> > Dean Arnold wrote:
> > >>A few months ago we had discussed on this list _standardizing_ error
> > >>handling for things like trying to insert duplicate records, but I kind
> > >>of dropped the ball - changing jobs and all didn't leave me enough time
> > >>to persue that standardization with members of this list. And since now
> > >>I don't use Perl in my new job, I don't have time to persue it at all.
> > >>Anyone else have time to revisit standardizing error handling for things
> > >>like "Trying to insert duplicate record"??? Basically all it involved
> > >>was putting together a list of common error conditions and finding the
> > >>corresponding ODBC error codes.
Note that all I was after as a fairly *short* list of the specific SQLSTATE
codes that correspond to errors that applications *often* want to explicitly
check for. Duplicate key being an obvious example.
> > > Er, isn't that what $h->state() is for ? In *theory*, state (aka SQLSTATE)
> > > codes should be fairly uniform across DBMS's (tho there's always a
> > > lot of platform specific state codes as well).
Yes. There are two things I want to do:
1. Specify a fairly short list of the specific SQLSTATE values that
I'd like drivers to support as a minimum.
2. Provide a simple way for drivers to map some of their private
err values into the corresponding SQLSTATE values.
Something like: $drh->{SQLSTATE_map} = $hash_ref_or_code_ref;
The DBI would then use that to set state() if it was left undef.
All the driver would need to do is:
$drh->{SQLSTATE_map} = {
NNN => '23000',
...just a few codes...
};
> > > However, I'm not certain
> > > that many drivers actually support $h->state(), or if they do, if they're
> > > conforming to the "standard" (presumably some SQL CLI std ?).
> > > If everybody adheres to that, then users can do a lookup of the
> > > appropriate SQLSTATE in the <insert SQL manual here> book,
> > > and key off the SQLSTATE value for disposition.
Ideally. It'll never be comprehensive, but that doesn't mean it
can't be useful.
I'd expect to see existing code like:
$dbh->do("INSERT ...");
if ($dbh->err =~ /duplicate/i) ...
look like this:
$dbh->do("INSERT ...");
if ($dbh->state eq '23505' or !$dbh->state && $dbh->err =~ /duplicate/i) ...
or just check for any of the "Integrity Constraint Violation" class of errors:
if ($dbh->state =~ /^23/ or !$dbh->state && $dbh->err =~ /duplicate/i) ...
And that's a good example because the first two chars of SQLSTATE are a good
way to check for a 'class' of error.
Naturally there's little point in doing all this for applications that'll only
ever work with one database. The real goal here is to help CPAN modules that
need to be able to work well with many databases.
> > I don't think it will fly. There are too many DBMS-specific problems
> > diagnosed by DBMS-specific values of SQLSTATE (or, in some cases,
> > DBMS-non-specific values - Informix has a tendency to set
> > SQLSTATE=IX000 - an Informix-specific error occurred; look at SQLCODE
> > to see what really went wrong) for it to be sensible to do the mapping.
>
> Sybase (when used with OpenClient, which is what DBD::Sybase does) does
> not set SQLSTATE at all...
>
> > I, for one, have zero intention of going through 30,000 message codes
> > and mapping them to anything resembling a standard.
>
> Indeed...
I wouldn't expect you to. But do you think you could manage 5 or 10?
Tim.
References:
http://www-rohan.sdsu.edu/doc/oracle/server803/A54661_01/err.htm#3146
http://www.postgresql.org/docs/7.4/interactive/errcodes-appendix.html
http://www.mysql.com/doc/en/Error-returns.html
http://www-306.ibm.com/software/data/db2/everyplace/doc/infocenters/enu/dbeapr0902.htm#HDRSQL11
http://developer.mimer.com/documentation/html_92/Mimer_SQL_Engine_DocSet/App_Return_Codes2.html#wp1112282
