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.
SQL-99 (ISO/IED 9075-2:1999) defines standardized SQLSTATE values in Table 27, which spreads over 6 pages (952-957). How many of those are you concerned about?
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.
I'd prefer to have it called something else -- I'd say DBISTATE except that is overloading a term -- so that there is no confusion about whether this is what the DBMS said vs this is the mappe value produced by DBI or DBD::DBMS based on what the DBMS actually said. As long as there's a (standard!) way to get at what the DBMS actually said, I can stomach a short list of mappings - as long as there's a good generic "something went wrong" message.
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... };
Maybe...with a different name for it, this would be tolerable.
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) ...
Hmmm; yes, if you're writing generic DBMS code, then the search is badly designed - or vulnerable to misreporting errors. There's always been a dichotomy between those using Perl to get at a specific (brand of) database and those using Perl to get at any brand of database. I'm primarily concerned about the former - maybe I should be more generous, but I don't want to impede the former from getting exactly what they need even if the latter also need help. (One reason why AutoCommit still completely rankles with me!)
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.
So, call it CPANSTATE :-)
I wouldn't expect you to. But do you think you could manage 5 or 10?
Maybe - which ones are you thinking of?
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
http://www.ibm.com/software/data/informix/pubs/library/errors_ids94.pdf
--
Jonathan Leffler ([EMAIL PROTECTED], [EMAIL PROTECTED]) #include <disclaimer.h>
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
