++ed by:

2 PAUSE users

Michael Brown


DBIx::Error - Structured exceptions for DBI


    use DBIx::Error;

    # Configure exception handler for DBI
    my $db = DBI->connect ( $dsn, $user, $password,
                            { HandleError => DBIx::Error->HandleError,
                              ShowErrorStatement => 1 } );

    # Catch a unique constraint violation (by class name)
    use TryCatch;
    try {
    } catch ( DBIx::Error::UniqueViolation $err ) {
      die "Name $name is already in use";

    # Catch a unique constraint violation (by SQLSTATE)
    use TryCatch;
    try {
    } catch ( DBIx::Error $err where { $_->state eq "23505" } ) {
      die "Name $name is already in use";

    # Catch any type of database error
    use TryCatch;
    try {
    } catch ( DBIx::Error $err ) {
      die "Internal database error [SQL state ".$err->state."]:\n".$err;


DBIx::Error provides structured exceptions for DBI errors. Each five-character SQLSTATE is mapped to a Perl exception class, allowing exceptions to be caught using code such as

    try {
    } catch ( DBIx::Error::NotNullViolation $err ) {
    } catch ( DBIx::Error::UniqueViolation $err ) {

The exception stringifies to produce the full DBI error message (including a stack trace). The original DBI error attributes (err, errstr and state) are also provided for inspection.

See "EXCEPTION CLASS HIERARCHY" below for a list of supported exception classes.



The error message, as produced by DBI.

If ShowErrorStatement was set to a true value when connecting to the database, this message will include the SQL statement that caused the error.


A stack trace, as produced using StackTrace::Auto.


The native database engine error code, as obtained from $DBI::err.


The native database engine error message, as obtained from $DBI::errstr.


The state code in the standard SQLSTATE five character format, as obtained from $DBI::state.


define_exception_classes ( %states )

See "SUBCLASSING" below.

exception_class ( $state )

Get the exception class for the specified SQLSTATE.


Returns a code reference suitable for passing as the HandleError database connection parameter for DBI (or DBIx::Class, or Catalyst::Model::DBIC::Schema).


Returns a code reference suitable for passing as the exception_action configuration parameter for DBIx::Class::Schema.


Using plain DBI

    use DBI;
    use DBIx::Error;
    use TryCatch;

    my $db = DBI->connect ( $dsn, $user, $password,
                            { HandleError => DBIx::Error->HandleError,
                              ShowErrorStatement => 1 } );

    my $sth = $db->prepare ( "INSERT INTO people VALUES ( ? )" );
    try {
      $sth->execute ( $name );
    } catch ( DBIx::Error::UniqueViolation $err ) {
      die "$name is already present in the database";
    } catch ( DBIx::Error::IntegrityConstraintViolation $err ) {
      die "$name is not allowed";

Using DBIx::Class

    package MyDB::Schema;
    use base qw ( DBIx::Class::Schema );
    use DBIx::Error;
    __PACKAGE__->exception_action ( DBIx::Error->exception_action );

    use MyDB::Schema;
    use TryCatch;
    my $db = MyDB::Schema->connect ( {
        dsn => $dsn,
        user => $user,
        password => $password,
        HandleError => DBIx::Error->HandleError,
        ShowErrorStatment => 1,
        unsafe => 1,
    } );
    try {
      $db->resultset ( "People" )->create ( { name => $name } );
    } catch ( DBIx::Error::UniqueViolation $err ) {
      die "$name is already present in the database";
    } catch ( DBIx::Error::IntegrityConstraintViolation $err ) {
      die "$name is not allowed";

Using Catalyst::Model::DBIC::Schema

    package MyDB::Schema;
    use base qw ( DBIx::Class::Schema );
    use DBIx::Error;
    __PACKAGE__->exception_action ( DBIx::Error->exception_action );

    package MyApp::Model::MyDB;
    use base qw ( Catalyst::Model::DBIC::Schema );
    use MyDB::Schema;
    use DBIx::Error;
    __PACKAGE__->config (
      schema_class => "MyDB::Schema",
      connect_info => {
        dsn => $dsn,
        user => $user,
        password => $password,
        HandleError => DBIx::Error->HandleError,
        ShowErrorStatment => 1,
        unsafe => 1,


Database engines

DBIx::Error can produce an exception of the correct subclass only if the underlying database engine supports SQLSTATE. (If the underlying driver does not support SQLSTATE then all exceptions will have the class DBIx::Error and the only way to determine what actually caused the error will be to examine the text of the error message.)

DBIx::Error is known to work reliably with PostgreSQL databases.

Bulk operations

Bulk operations using DBI->execute_array() may not work as expected. The underlying DBI code will not raise an exception until the entire bulk operation has completed, and the exception will represent the most recent failure at the time the operation completes.

In most (but not all) cases, this will result in a DBIx::Error::InFailedSqlTransaction exception, since the transaction will already have been aborted. To raise an exception corresponding to the original error (the one which caused the transaction to abort), the caller would have to parse the ArrayTupleStatus array and use DBI->set_err() to regenerate the exception corresponding to the appropriate row.

This limitation also applies to operations built on top of DBI->execute_array(), such as DBIx::Class::Schema->populate().

As a general rule: assume that the only sensible exception class that can be caught from any bulk operation is the root class DBIx::Error:

    try {
      $sth->execute_array ( @args );
    } catch ( DBIx::Error $err ) {
      # Could be any type of DBI error



Any plain errors (i.e. die "some_message"; statements) within a txn_do() block will be converted into DBIx::Error::GeneralError exceptions:

    try {
      $db->txn_do ( sub {
        die "foo";
      } );
    } catch ( $err ) {
      print "Caught ".( ref $err )." exception with message: ".$err;
    # will print "Caught DBIx::Error::GeneralError exception with message: foo"

To avoid this behaviour, use exception objects instead of plain die:

    try {
      $db->txn_do ( sub {
        My::Error->throw ( "foo" );
      } );
    } catch ( $err ) {


Applications can define custom SQLSTATE codes to represent application-specific database errors. For example, using PostgreSQL's PL/pgSQL:

    CREATE FUNCTION test_custom_exception() RETURNS void AS $$
        RAISE EXCEPTION 'Something bad happened'
            USING ERRCODE = 'MY001';
    $$ LANGUAGE plpgsql VOLATILE;

These custom SQLSTATE codes can be mapped to Perl exception classes by subclassing DBIx::Error and defining the appropriate mappings using the define_exception_classes() class method. For example:

    package MyApplication::Error::DBI;

    use Moose;
    extends "DBIx::Error";

    __PACKAGE__->define_exception_classes (
      "MY000" => "General",
      "MY001" => "SomethingBadHappened"

    __PACKAGE__->meta->make_immutable ( inline_constructor => 0 );


The resulting exception classes will be subclasses of the package which calls define_exception_classes(). In the above example, the exception classes will be:


The exceptions can be caught in the usual way:

    use TryCatch;
    try {
    } catch ( MyApplication::Error::DBI::SomethingBadHappened $err ) {
    } catch ( DBIx::Error::UniqueViolation $err ) {

Mappings for the generic SQLSTATE codes (ending in "000") act as superclasses for the more specific SQLSTATE codes (ending in anything other than "000"). You must define mappings for the generic SQLSTATE codes corresponding to any application-specific SQLSTATE codes.

By default, the exception classes will be prefixed with the name of the package which calls define_exception_classes(). You can override this by specifying full class names for the exception subclasses, e.g.

    __PACKAGE__->define_exception_classes (
      "QA000" => "MyApp::Error::General"


DBIx::Error::SqlStatementNotYetComplete (03000)
DBIx::Error::ConnectionException (08000)
DBIx::Error::SqlclientUnableToEstablishSqlconnection (08001)
DBIx::Error::ConnectionDoesNotExist (08003)
DBIx::Error::SqlserverRejectedEstablishmentOfSqlconnection (08004)
DBIx::Error::ConnectionFailure (08006)
DBIx::Error::TransactionResolutionUnknown (08007)
DBIx::Error::ProtocolViolation (08P01)
DBIx::Error::TriggeredActionException (09000)
DBIx::Error::FeatureNotSupported (0A000)
DBIx::Error::InvalidTransactionInitiation (0B000)
DBIx::Error::LocatorException (0F000)
DBIx::Error::InvalidLocatorSpecification (0F001)
DBIx::Error::InvalidGrantor (0L000)
DBIx::Error::InvalidGrantOperation (0LP01)
DBIx::Error::InvalidRoleSpecification (0P000)
DBIx::Error::CaseNotFound (20000)
DBIx::Error::CardinalityViolation (21000)
DBIx::Error::DataException (22000)
DBIx::Error::StringDataRightTruncation (22001)
DBIx::Error::NullValueNoIndicatorParameter (22002)
DBIx::Error::NumericValueOutOfRange (22003)
DBIx::Error::NullValueNotAllowed (22004)
DBIx::Error::ErrorInAssignment (22005)
DBIx::Error::InvalidDatetimeFormat (22007)
DBIx::Error::DatetimeFieldOverflow (22008)
DBIx::Error::InvalidTimeZoneDisplacementValue (22009)
DBIx::Error::EscapeCharacterConflict (2200B)
DBIx::Error::InvalidUseOfEscapeCharacter (2200C)
DBIx::Error::InvalidEscapeOctet (2200D)
DBIx::Error::ZeroLengthCharacterString (2200F)
DBIx::Error::MostSpecificTypeMismatch (2200G)
DBIx::Error::NotAnXmlDocument (2200L)
DBIx::Error::InvalidXmlDocument (2200M)
DBIx::Error::InvalidXmlContent (2200N)
DBIx::Error::InvalidXmlComment (2200S)
DBIx::Error::InvalidXmlProcessingInstruction (2200T)
DBIx::Error::InvalidIndicatorParameterValue (22010)
DBIx::Error::SubstringError (22011)
DBIx::Error::DivisionByZero (22012)
DBIx::Error::InvalidArgumentForNtileFunction (22014)
DBIx::Error::IntervalFieldOverflow (22015)
DBIx::Error::InvalidArgumentForNthValueFunction (22016)
DBIx::Error::InvalidCharacterValueForCast (22018)
DBIx::Error::InvalidEscapeCharacter (22019)
DBIx::Error::InvalidRegularExpression (2201B)
DBIx::Error::InvalidArgumentForLogarithm (2201E)
DBIx::Error::InvalidArgumentForPowerFunction (2201F)
DBIx::Error::InvalidArgumentForWidthBucketFunction (2201G)
DBIx::Error::InvalidRowCountInLimitClause (2201W)
DBIx::Error::InvalidRowCountInResultOffsetClause (2201X)
DBIx::Error::CharacterNotInRepertoire (22021)
DBIx::Error::IndicatorOverflow (22022)
DBIx::Error::InvalidParameterValue (22023)
DBIx::Error::UnterminatedCString (22024)
DBIx::Error::InvalidEscapeSequence (22025)
DBIx::Error::StringDataLengthMismatch (22026)
DBIx::Error::TrimError (22027)
DBIx::Error::ArraySubscriptError (2202E)
DBIx::Error::FloatingPointException (22P01)
DBIx::Error::InvalidTextRepresentation (22P02)
DBIx::Error::InvalidBinaryRepresentation (22P03)
DBIx::Error::BadCopyFileFormat (22P04)
DBIx::Error::UntranslatableCharacter (22P05)
DBIx::Error::NonstandardUseOfEscapeCharacter (22P06)
DBIx::Error::IntegrityConstraintViolation (23000)
DBIx::Error::RestrictViolation (23001)
DBIx::Error::NotNullViolation (23502)
DBIx::Error::ForeignKeyViolation (23503)
DBIx::Error::UniqueViolation (23505)
DBIx::Error::CheckViolation (23514)
DBIx::Error::ExclusionViolation (23P01)
DBIx::Error::InvalidCursorState (24000)
DBIx::Error::InvalidTransactionState (25000)
DBIx::Error::ActiveSqlTransaction (25001)
DBIx::Error::BranchTransactionAlreadyActive (25002)
DBIx::Error::InappropriateAccessModeForBranchTransaction (25003)
DBIx::Error::InappropriateIsolationLevelForBranchTransaction (25004)
DBIx::Error::NoActiveSqlTransactionForBranchTransaction (25005)
DBIx::Error::ReadOnlySqlTransaction (25006)
DBIx::Error::SchemaAndDataStatementMixingNotSupported (25007)
DBIx::Error::HeldCursorRequiresSameIsolationLevel (25008)
DBIx::Error::NoActiveSqlTransaction (25P01)
DBIx::Error::InFailedSqlTransaction (25P02)
DBIx::Error::InvalidSqlStatementName (26000)
DBIx::Error::TriggeredDataChangeViolation (27000)
DBIx::Error::InvalidAuthorizationSpecification (28000)
DBIx::Error::InvalidPassword (28P01)
DBIx::Error::DependentPrivilegeDescriptorsStillExist (2B000)
DBIx::Error::DependentObjectsStillExist (2BP01)
DBIx::Error::InvalidTransactionTermination (2D000)
DBIx::Error::SqlRoutineException (2F000)
DBIx::Error::InvalidCursorName (34000)
DBIx::Error::ExternalRoutineException (38000)
DBIx::Error::ExternalRoutineInvocationException (39000)
DBIx::Error::SavepointException (3B000)
DBIx::Error::InvalidSavepointSpecification (3B001)
DBIx::Error::InvalidCatalogName (3D000)
DBIx::Error::InvalidSchemaName (3F000)
DBIx::Error::TransactionRollback (40000)
DBIx::Error::SerializationFailure (40001)
DBIx::Error::TransactionIntegrityConstraintViolation (40002)
DBIx::Error::StatementCompletionUnknown (40003)
DBIx::Error::DeadlockDetected (40P01)
DBIx::Error::SyntaxErrorOrAccessRuleViolation (42000)
DBIx::Error::InsufficientPrivilege (42501)
DBIx::Error::SyntaxError (42601)
DBIx::Error::InvalidName (42602)
DBIx::Error::InvalidColumnDefinition (42611)
DBIx::Error::NameTooLong (42622)
DBIx::Error::DuplicateColumn (42701)
DBIx::Error::AmbiguousColumn (42702)
DBIx::Error::UndefinedColumn (42703)
DBIx::Error::UndefinedObject (42704)
DBIx::Error::DuplicateObject (42710)
DBIx::Error::DuplicateAlias (42712)
DBIx::Error::DuplicateFunction (42723)
DBIx::Error::AmbiguousFunction (42725)
DBIx::Error::GroupingError (42803)
DBIx::Error::DatatypeMismatch (42804)
DBIx::Error::WrongObjectType (42809)
DBIx::Error::InvalidForeignKey (42830)
DBIx::Error::CannotCoerce (42846)
DBIx::Error::UndefinedFunction (42883)
DBIx::Error::ReservedName (42939)
DBIx::Error::UndefinedTable (42P01)
DBIx::Error::UndefinedParameter (42P02)
DBIx::Error::DuplicateCursor (42P03)
DBIx::Error::DuplicateDatabase (42P04)
DBIx::Error::DuplicatePreparedStatement (42P05)
DBIx::Error::DuplicateSchema (42P06)
DBIx::Error::DuplicateTable (42P07)
DBIx::Error::AmbiguousParameter (42P08)
DBIx::Error::AmbiguousAlias (42P09)
DBIx::Error::InvalidColumnReference (42P10)
DBIx::Error::InvalidCursorDefinition (42P11)
DBIx::Error::InvalidDatabaseDefinition (42P12)
DBIx::Error::InvalidFunctionDefinition (42P13)
DBIx::Error::InvalidPreparedStatementDefinition (42P14)
DBIx::Error::InvalidSchemaDefinition (42P15)
DBIx::Error::InvalidTableDefinition (42P16)
DBIx::Error::InvalidObjectDefinition (42P17)
DBIx::Error::IndeterminateDatatype (42P18)
DBIx::Error::InvalidRecursion (42P19)
DBIx::Error::WindowingError (42P20)
DBIx::Error::CollationMismatch (42P21)
DBIx::Error::IndeterminateCollation (42P22)
DBIx::Error::WithCheckOptionViolation (44000)
DBIx::Error::InsufficientResources (53000)
DBIx::Error::DiskFull (53100)
DBIx::Error::OutOfMemory (53200)
DBIx::Error::TooManyConnections (53300)
DBIx::Error::ProgramLimitExceeded (54000)
DBIx::Error::StatementTooComplex (54001)
DBIx::Error::TooManyColumns (54011)
DBIx::Error::TooManyArguments (54023)
DBIx::Error::ObjectNotInPrerequisiteState (55000)
DBIx::Error::ObjectInUse (55006)
DBIx::Error::CantChangeRuntimeParam (55P02)
DBIx::Error::LockNotAvailable (55P03)
DBIx::Error::OperatorIntervention (57000)
DBIx::Error::QueryCanceled (57014)
DBIx::Error::AdminShutdown (57P01)
DBIx::Error::CrashShutdown (57P02)
DBIx::Error::CannotConnectNow (57P03)
DBIx::Error::DatabaseDropped (57P04)
DBIx::Error::SystemError (58000)
DBIx::Error::IoError (58030)
DBIx::Error::UndefinedFile (58P01)
DBIx::Error::DuplicateFile (58P02)
DBIx::Error::ConfigFileError (F0000)
DBIx::Error::LockFileExists (F0001)
DBIx::Error::FdwError (HV000)
DBIx::Error::FdwOutOfMemory (HV001)
DBIx::Error::FdwDynamicParameterValueNeeded (HV002)
DBIx::Error::FdwInvalidDataType (HV004)
DBIx::Error::FdwColumnNameNotFound (HV005)
DBIx::Error::FdwInvalidDataTypeDescriptors (HV006)
DBIx::Error::FdwInvalidColumnName (HV007)
DBIx::Error::FdwInvalidColumnNumber (HV008)
DBIx::Error::FdwInvalidUseOfNullPointer (HV009)
DBIx::Error::FdwInvalidStringFormat (HV00A)
DBIx::Error::FdwInvalidHandle (HV00B)
DBIx::Error::FdwInvalidOptionIndex (HV00C)
DBIx::Error::FdwInvalidOptionName (HV00D)
DBIx::Error::FdwOptionNameNotFound (HV00J)
DBIx::Error::FdwReplyHandle (HV00K)
DBIx::Error::FdwUnableToCreateExecution (HV00L)
DBIx::Error::FdwUnableToCreateReply (HV00M)
DBIx::Error::FdwUnableToEstablishConnection (HV00N)
DBIx::Error::FdwNoSchemas (HV00P)
DBIx::Error::FdwSchemaNotFound (HV00Q)
DBIx::Error::FdwTableNotFound (HV00R)
DBIx::Error::FdwFunctionSequenceError (HV010)
DBIx::Error::FdwTooManyHandles (HV014)
DBIx::Error::FdwInconsistentDescriptorInformation (HV021)
DBIx::Error::FdwInvalidAttributeValue (HV024)
DBIx::Error::FdwInvalidStringLengthOrBufferLength (HV090)
DBIx::Error::FdwInvalidDescriptorFieldIdentifier (HV091)
DBIx::Error::PlpgsqlError (P0000)
DBIx::Error::RaiseException (P0001)
DBIx::Error::NoDataFound (P0002)
DBIx::Error::TooManyRows (P0003)
DBIx::Error::GeneralError (S1000)
DBIx::Error::InternalError (XX000)
DBIx::Error::DataCorrupted (XX001)
DBIx::Error::IndexCorrupted (XX002)


Michael Brown <mbrown@fensystems.co.uk>