Programming the Perl DBI

Standard Error Codes

If an INSERT statement fails, how can you tell whether it was because the table already has a record with the same primary key? With ODBC, you’d check the SQLSTATE error indicator to see if it was “23000”, regardless of the database being used. With DBI, you’re on your own.

ODBC defines a large number of standard error codes that you can use to determine in reasonable detail what went wrong. They’re not often needed, but when they are, they’re very useful. Having said that, this idyllic picture is tarnished by the fact that many of the codes change depending on the version of the ODBC driver being used. For example, while an ODBC 2.x driver returns “S0011” when a CREATE INDEX statement names an index that already exists, an ODBC 3.x driver returns “42S11”. So much for standards!

The DBI leaves you with having to check for different $DBI::err values or $DBI::errstr strings, depending on the database driver being used. The DBI does provide a $DBI::state variable and $h- >state() method that drivers can use to provide the standard error codes, but few do at the moment.