Perl Database Error Handling
Contents |
login Username: * Password: * Create new accountRequest new password Home » DBI: handling database errors ( categories: databases ) Basically, there are two ways perl dbi connect error handling of handling database errors, check (almost) every DBI call for errors or set perl dbi execute return value 'RaiseError' attribute to '1ยด: -- Manual checking This way, you have to add code yourself to check for database error conditions, perl dbi handleerror so after nearly every method call you should check if the operation completed successfully. There are two DBI methods that are very helpful to manually check for database errors: 'err' and 'errstr'. 'err' returns perl dbi errstr the native database engine error code from the last DBI method called. The code returned is usually an integer. 'errstr' returns the native database engine error message from the last DBI method called. Example: $dbh = DBI->connect($data_src, $user, $pwd) or die $DBI::errstr;
my $sth = $dbh->prepare("DELETE FROM table WHERE count < '?'");
$sth->execute(25);
if ( $sth->err )
{
die "ERROR! return code:
Dbi Error Fatal
. $sth->err . " error msg: " . $sth->errstr . "\n";
}
-- Setting 'RaiseError' attribute If DBI 'RaiseError' attribute is set to '1' (is '0' by default), then any database error will cause the DBI module to 'die' with an appropriate message. When using 'RaiseError', is recommended to set the 'PrintError' atribute to '0') Example: my $dbh = DBI->connect($dsn, $user, $pw, { RaiseError => 1, PrintError => 0 });
Bookmark/Search this post with: | | | | » login or register to post comments You can also provide a Submitted by Kelicula on Wed, 04/15/2009 - 00:58. You can also provide a custom sub to handle errors with the RaiseError flag set. After establishing server connection: $dbh->{HandleError} = sub { my $error = shift; # do something with error...; }; Or in attributes: my $dbh = DBI->connect("DBI:......, { RaiseError => 1, HandleError => \&DBerror })|| die $DBI::errstr; sub DBerror { my $error = shift; # do something with error... } etc... Only disadvantage is knowing what line the error originated from in your script. -------- I'm unique just like everyone else! » login or register to post comments Home | Links | RSS feed | Forums Copyright © 2006 T
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
Perl Dbi Error String
with error messages. DBI dynamic attributeDescription $DBI::errEquivalent to $h->err() $DBI::errstrEquivalent to $h->errstr() try catch in perl $DBI::stateEquivalent to $h->state() The second table gives a list of DBI dynamic attributes, which are related to error handling. dbi err fatal 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 http://www.perlhowto.com/dbi_handling_database_errors methods. #!/usr/bin/perl use strict; use DBI; my $dsn = "dbi:SQLite:dbname=test.db"; my $user = ''; 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, http://zetcode.com/db/sqliteperltutorial/err/ $name, $price) = $sth->fetchrow()) { print "$id $name $price\n"; } # check for problems which may have terminated the fetch early warn $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
Go to comments The DBI module lets you handle errors yourself if you don't like its built-in behavior. DBI lets you handle https://www.effectiveperlprogramming.com/2010/07/set-custom-dbi-error-handlers/ the errors at either the database or the statement handle level by specifying http://www.perl.com/pub/1999/10/DBI.html attributes: my $dbh = DBI->connect( ..., ..., \%attr ); my $sth = $dbh->prepare( ..., \%attr ); There are several attributes that affect error handling, each of which you can use with either a connection or a statement handle: Attribute Type Default PrintWarn Boolean On PrintError Boolean On RaiseError Boolean perl dbi 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 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 perl database error 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 ) { 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
databases started to get to be a big deal in the 1970's, andthey're still a big deal today, which is a little peculiar, because they're a 1960's technology. A relational database is a bunch of rectangular tables. Each row of a table is a record about one person or thing; the record contains several pieces of information called fields. Here is an example table: LASTNAME FIRSTNAME ID POSTAL_CODE AGE SEX Gauss Karl 119 19107 30 M Smith Mark 3 T2V 3V4 53 M Noether Emmy 118 19107 31 F Smith Jeff 28 K2G 5J9 19 M Hamilton William 247 10139 2 M The names of the fields are LASTNAME, FIRSTNAME, ID, POSTAL_CODE, AGE, and SEX. Each line in the table is a record, or sometimes a row or tuple. For example, the first row of the table represents a 30-year-old male whose name is Karl Gauss, who lives at postal code 19107, and whose ID number is 119. Sometimes this is a very silly way to store information. When the information naturally has a tabular structure it's fine. When it doesn't, you have to squeeze it into a table, and some of the techniques for doing that are more successful than others. Nevertheless, tables are simple and are easy to understand, and most of the high-performance database systems you can buy today operate under this 1960's model. About SQL SQL stands for Structured Query Language. It was invented at IBM in the 1970's. It's a language for describing searches and modifications to a relational database. SQL was a huge success, probably because it's incredibly simple and anyone can pick it up in ten minutes. As a result, all the important database systems support it in some fashion or another. This includes the big players, like Oracle and Sybase, high-quality free or inexpensive database systems like MySQL, and funny hacks like Perl's DBD::CSV module, which we'll see later. There are four important things one can do with a table: SELECT Find all the records that have a certain property
INSERT Add new records DELETE Remove old records UPDATE Modify records that are already there Th