Perl Dbi Error Handling Eval
Contents |
the database, then wrap database calls in eval: $dbh = DBI->connect($DSN, $user, $password, { RaiseError > 1 }); eval { $dbh->do($SQL); $sth = $dbh->prepare($SQL2); $sth->execute( ); while (@row = $sth->fetchrow_array) perl dbi error handling examples { # ... } }; if ($@) { # recover here using $DBI::lasth->errstr perl dbi execute return value to get # the error message } 14.11.3. Discussion The logic here is simple: first tell DBI to die
Perl Dbi Connect Error Handling
if there's a problem with your SQL (otherwise, the database can't do what you wanted it to). Then, wrap the code that might die in eval to catch fatal errors. Next, check
Perl Dbi Handleerror
$@ (either the error message you would have die d with or empty if there was no error) to see whether something went wrong. If it did, somehow deal with the error. DBI supplies the $DBI::lasth variable, containing the last handle acted on. If something went wrong, that's the handle that caused it. You could use the $@ error message, but that also has perl dbi errstr the "died at file ... line ..." text from die that you might not want. To discover the SQL statement that died, use $DBI::lasth->{Statement}. If you are using only the one handle, you can call the methods directly on your handle instead of on $DBI::lasth: $msg = $dbh->errstr; $sql = $dbh->{Statement}; An alternative approach is to disable RaiseError and check the return value for each database call. Methods such as do and execute return a true value if successful, so you can say: $dbh->do($SQL) or die $dbh->errstr; $sth->execute( ) or die $sth->errstr; The do method returns the number of rows affected, but in such a way that it always returns a true value if successful. (If you're curious how to do this, see the Introduction to Chapter 1 for the gory details of how Perl decides what's true and what's false.) If you're debugging error catching and error handling, you might want to add the PrintError attribute to your database connection: $dbh = DBI->connect($DSN, $user, $password, { RaiseError => 1, PrintError => 1 }); When problems occur on the handle, PrintError issues a warning before RaiseError calls die. So ev
Go to comments The DBI module lets you handle errors yourself if you don't like its built-in behavior. DBI lets you handle the errors at either the database or the statement handle level by specifying
Perl Dbi Try Catch
attributes: my $dbh = DBI->connect( ..., ..., \%attr ); my $sth = $dbh->prepare( ..., \%attr dbi error fatal ); There are several attributes that affect error handling, each of which you can use with either a connection or a statement try catch in perl handle: Attribute Type Default PrintWarn Boolean On PrintError Boolean On RaiseError Boolean Off HandleError Code Ref Off ShowErrorStatement Boolean Off These attributes are inherited by anything derived from the handle where you set them. The PrintWarn and http://docstore.mik.ua/orelly/perl4/cook/ch14_12.htm PrintError attributes do just what they say. They are on by default, and they don't stop your program. In this example, you prepare a statement that expects one bind parameter, but when you execute it, you give two parameters instead: use DBI; my $dbh = DBI->connect( 'dbi:SQLite:dbname=test.db', '', '', {} ); my $sth = $dbh->prepare( 'SELECT * FROM Cats WHERE id = ?' ); $sth->execute( 1, 2 ); while( my @row = $sth->fetchrow_array ) { https://www.effectiveperlprogramming.com/2010/07/set-custom-dbi-error-handlers/ print "row: @row\n"; } print "Got to the end\n"; Since PrintError is true by default, DBI prints the error, but it allows the program to continue even though there was an error: DBD::SQLite::st execute failed: called with 2 bind variables when 1 are needed at dbi-test.pl line 12. Got to the end If you set the ShowErrorStatement attribute, you get a better error message because DBI appends the SQL statement that you tried to execute. You can set this either database handle or the statement handle, but if you don't know which statement is causing the problem, it's easier to set it as part of the database handle: # The rest of the program is the same my $dbh = DBI->connect( 'dbi:SQLite:dbname=test.db', '', '', { ShowErrorStatement => 1, } ); The error message shows the SQL statement, but the program still continues: DBD::SQLite::st execute failed: called with 2 bind variables when 1 are needed [for Statement "SELECT * FROM Cats WHERE id = ?"] at dbi-test.pl line 12. Got to the end The RaiseError attribute turns errors into fatal errors that you can trap with eval { ... } or Try::Tiny (Item 103: Handle Exceptions Properly) (or not trap if you want your program to die): # The rest of the program is the same my $dbh = DBI->connect( 'dbi:SQLite:dbname=test.db', '',
native database engine error message from the last DBI method called. $h->state()Returns a state code in the standard SQLSTATE five character format The above three methods deal with error messages. DBI dynamic attributeDescription $DBI::errEquivalent http://zetcode.com/db/sqliteperltutorial/err/ to $h->err() $DBI::errstrEquivalent to $h->errstr() $DBI::stateEquivalent to $h->state() The second table gives a list http://www.easysoft.com/developer/languages/perl/dbi-debugging.html of DBI dynamic attributes, which are related to error handling. These attributes have a short lifespan. They should be used immediately after the method that might cause an error. Default error handling By default, the errors are returned by Perl DBI methods. #!/usr/bin/perl use strict; use DBI; my $dsn = "dbi:SQLite:dbname=test.db"; my $user = ''; perl dbi my $password = ''; my $dbh = DBI->connect($dsn, $user, $password) or die "Can't connect to database: $DBI::errstr"; my $sth = $dbh->prepare( q{ SELECT Id, Name, Price FROM Cars } ) or die "Can't prepare statement: $DBI::errstr"; my $rc = $sth->execute() or die "Can't execute statement: $DBI::errstr"; while (my($id, $name, $price) = $sth->fetchrow()) { print "$id $name $price\n"; } # check for problems which may have terminated the fetch early warn perl dbi error $DBI::errstr if $DBI::err; $sth->finish(); $dbh->disconnect(); In the first script we deal with the default behaviour of returning error codes. my $dbh = DBI->connect($dsn, $user, $password) or die "Can't connect to database: $DBI::errstr"; We call the connect() method to create a database connection. If the attempt fails, the method returns undef and sets both $DBI::err and $DBI::errstr attributes. The die() method prints the error message in case of a failure and terminates the script. my $sth = $dbh->prepare( q{ SELECT Id, Name, Price FROM Cars } ) or die "Can't prepare statement: $DBI::errstr"; We call the prepare() statement. If the method fails, the die() method prints an error message and terminates the script. my $rc = $sth->execute() or die "Can't execute statement: $DBI::errstr"; Again. We call the execute() method and check for errors. The method returns undef if it fails. warn $DBI::errstr if $DBI::err; We check for problems which may have terminated the fetch method early. Raising exceptions Checking for errors each time we call a DBI method may be tedious. We could easily forget to do so if we have a larger script. The preferred way of dealing with possible errors is to raise exceptions. To raise exceptions, we set the RaiseError attribute to true. #!/
Check the returns from DBI callsRaiseErrorTest inserts/updates workedProtect calls you expect could legitimately fail with evalsUse transactions, protect them with eval and commit or roll them back after the evalConsider adding comments to SQLWhat you get in DBI out of the box to help youDBIx::Log4perl Advantages of using DBIx::Log4perlWhere to get DBIx::Log4perlDBIx::Log4perl dependenciesDBIx::Log4perl documentationDBIx::Log4perl quick startExample DBIx::Log4perl outputCatching errors with DBIx::Log4perlAdding additional error logging to your Perl scriptLog4perl configuration fileChanging the default logging in DBIx::Log4perlLogging only SQL and parametersAppendix A: ResourcesIntroductionYou’ve read our other Perl tutorials, installed Perl DBI and a DBD and have started writing your application but it is not working properly. What can you do? This tutorial ignores syntax errors in your Perl (which you can easily track down with perl -cw) and concentrates on locating those tough to find SQL, incorrect use of the DBI interface, transaction and logic errors.Good DBI programming practiseCheck the returns from DBI callsTest the return from DBI method calls return a success value. DBI->connect("dbi:ODBC:mydsn", "xxx", "yyy") or die "$DBI::errstr"; Normally this is a true and non-zero value, but in some cases it is a true but zero value e.g. DBDs will return a true value for an insert operation that inserts zero rows, but if you know the insert should insert a row, you test the returned value is true and not "0E0" (which is true but zero). See the next section, "RaiseError" and "Test inserts worked".RaiseErrorThe handle attribute RaiseError is, by default, turned off. When the RaiseError attribute is turned on for a handle, it causes die to be called (or your HandleError routine) when an error occurs using DBI. If you turn RaiseError on in the DBI connect method, it will be inherited by all other handles created off that connection handle.Without RaiseError, the following code (by default) will print the error (because PrintError is on by default) but not cause your program to die: use DBI; $dbh = DBI->connect("dbi:ODBC:mydsn","xxx", "yyy"); $sth = $dbh->prepare("invalid sql"); $sth->