DBI error handling

[Lars Purschke]

Hi!

I've a perl script which inserts data to a database. Sometime I get an
error on the execute() statement. Now I want the script not to die but
to exit the loop and to try again with the next record. Does anyone know
how to realize that?


my $dbh = DBI->connect("dbi:ODBC:$dsn", "xyz", "xyz",
{ PrintError => 0,
RaiseError => 0}) or die "$DBI::errstr\n";

while( my(@row) = $sth->fetchrow_array ) {
die $sth->errstr if $sth->err;

$stmt = "Insert into .... ";

$rs = $dbh->prepare($stmt);

$rs->execute();
}

thanks
lars

 

[Tore Aursand]

I've a perl script which inserts data to a database. Sometime I get an
error on the execute() statement. Now I want the script not to die but
to exit the loop and to try again with the next record.

Well. You could start by telling Perl _not_ to die when something goes
wrong. That would be a grand opener.

You could also check the return value of the execute() method. As it says
in the DBI documentation;

An "undef" is returned if an error occurs. A successful "execute"
always returns true regardless of the number of rows affected, even if
it's zero (see below). It is always important to check the return status
of "execute" (and most other DBI methods) for errors if you're not using
"RaiseError".

So;

my $sth = $dbh->prepare( ... );
if ( $sth->execute() ) {
# Everything went fine, obviously
}
else {
# An error occured
}
$sth->finish();

See 'perldoc DBI' for more information.

 

[Ron Reidy]

Well. You could start by telling Perl _not_ to die when something goes
wrong. That would be a grand opener.

You could also check the return value of the execute() method. As it says
in the DBI documentation;

An "undef" is returned if an error occurs. A successful "execute"
always returns true regardless of the number of rows affected, even if
it's zero (see below). It is always important to check the return status
of "execute" (and most other DBI methods) for errors if you're not using
"RaiseError".

So;

my $sth = $dbh->prepare( ... );
if ( $sth->execute() ) {
# Everything went fine, obviously
}
else {
# An error occured
}
$sth->finish();

See 'perldoc DBI' for more information.

And perldoc -f eval

 

[ctcgag]

Hi!

I've a perl script which inserts data to a database. Sometime I get an
error on the execute() statement. Now I want the script not to die but
to exit the loop and to try again with the next record.

If it exits the loop, then it is NOT going to try again with the next
record, is it?

Does anyone know
how to realize that?

I would think that the code you show below would
to about what you want, because if $rs->execute causes an error, the
fetchrow_array in the next loop iteration should reset the err variable, so
you shouldn't find $sth->err to be inheriting $rs's errors, but I might be
wrong on that (I never use RaiseError=>0). I would do this:

(don't unset RaiseError),

while( my(@row) = $sth->fetchrow_array ) {
# no need to die here, RaisErroe does it for you
# die $sth->errstr if $sth->err;

$stmt = "Insert into .... ";
$rs = $dbh->prepare($stmt);
eval {$rs->execute()};
}

On an unrelated issue, you probably shouldn't be doing the prepare
inside the loop. If you need to do so, you may as well just use $dbh->do.

Xho

 

[Matthew Braid]

Hi!

I've a perl script which inserts data to a database. Sometime I get an
error on the execute() statement. Now I want the script not to die but
to exit the loop and to try again with the next record. Does anyone know
how to realize that?


my $dbh = DBI->connect("dbi:ODBC:$dsn", "xyz", "xyz",
{ PrintError => 0,
RaiseError => 0}) or die "$DBI::errstr\n";

while( my(@row) = $sth->fetchrow_array ) {
die $sth->errstr if $sth->err;

$stmt = "Insert into .... ";

$rs = $dbh->prepare($stmt);

$rs->execute();
}

thanks
lars

I always make the connection options set to { PrintError => 0,
RaiseError => 1} and then wrap all prepare/execute calls in an eval.

So your code would become:

my $dbh = DBI->connect("dbi:ODBC:$dsn", "xyz", "xyz",
{ PrintError => 0,
RaiseError => 1}) or die "$DBI::errstr\n";

while( my(@row) = $sth->fetchrow_array ) {
die $sth->errstr if $sth->err;

$stmt = "Insert into .... ";

eval {$rs = $dbh->prepare($stmt)};
if ($@) {
# Do something to handle error
}
eval {$rs->execute()};
if ($@) {
# Do something to handle error
}
}


You could even shrink the evals down to:
eval {$rs = $dbh->prepare($stmt); $rs->execute};
if you don't really need to distinguish between prepare and execute errors.

MB

 

[James Willmore]

I've a perl script which inserts data to a database. Sometime I get
an error on the execute() statement. Now I want the script not to
die but to exit the loop and to try again with the next record. Does
anyone know how to realize that?


my $dbh = DBI->connect("dbi:ODBC:$dsn", "xyz", "xyz",
{ PrintError => 0,
RaiseError => 0}) or die "$DBI::errstr\n";

while( my(@row) = $sth->fetchrow_array ) {
die $sth->errstr if $sth->err;

$stmt = "Insert into .... ";

$rs = $dbh->prepare($stmt);

$rs->execute();
}

What would be better is the following (untested)

my $dbh = DBI->connect("dbi:ODBC:$dsn", $user, $pass,
{PrintError=>1,
RaiseError=>1}
or die "Can't connect: ", $DBI::errstr,"\n";

#create a statement with placeholders
my $stmt =
'INSERT INTO table field1,field2, ... fieldn VALUES(?,?,... ?)';

#prepare *once*
#you *should* die here if you can't prepare the statement
#especial if you're only printing errors
$rs = $dbh->prepare($stmt)
or die "Can't prepare: ", $dbh->errstr,"\n";

#now execute the statement foreach value
while(my(@row) = $sth->fetchrow_array){
$rs->execute(@row)
or warn "Insert failed for the following:\n", join("|",@row),"\n",
$rs->errstr,"\n";
}

Again, untested.

You should read about using placeholders and binding columns in the
DBI documentation. This would help you're code.

HTH

--
Jim

Copyright notice: all code written by the author in this post is
released under the GPL. http://www.gnu.org/licenses/gpl.txt
for more information.

a fortune quote ...
A jury consists of 12 persons chosen to decide who has the better
lawyer. -- Robert Frost

 

[James Willmore]

What would be better is the following (untested)

my $dbh = DBI->connect("dbi:ODBC:$dsn", $user, $pass,
{PrintError=>1,
RaiseError=>1}

forgot the ....

)


This is what I get for *not* following direction

--
Jim

Copyright notice: all code written by the author in this post is
released under the GPL. http://www.gnu.org/licenses/gpl.txt
for more information.

a fortune quote ...
Brain fried -- Core dumped

 

[Eric J. Roode]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What would be better is the following (untested)

my $dbh = DBI->connect("dbi:ODBC:$dsn", $user, $pass,
{PrintError=>1,
RaiseError=>1}
or die "Can't connect: ", $DBI::errstr,"\n";

#create a statement with placeholders
my $stmt =
'INSERT INTO table field1,field2, ... fieldn VALUES(?,?,... ?)';

#prepare *once*
#you *should* die here if you can't prepare the statement
#especial if you're only printing errors
$rs = $dbh->prepare($stmt)
or die "Can't prepare: ", $dbh->errstr,"\n";

"or die"...? You set RaiseError above. You're not going to get the
chance to die; prepare() will die for you.

#now execute the statement foreach value
while(my(@row) = $sth->fetchrow_array){
$rs->execute(@row)
or warn "Insert failed for the following:\n", join("|",@row),"\n",

Ditto here -- you won't get the chance to warn.

- --
Eric
$_ = reverse sort $ /. r , qw p ekca lre uJ reh
ts p , map $ _. $ " , qw e p h tona e and print

-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 7.0.3 for non-commercial use <http://www.pgp.com>

iQA/AwUBP8fi7WPeouIeTNHoEQKeZACgl/IqFwZ8uquDcFgHjpHeKi17YowAoPzg
rzyGg2Mf4SdDVgLxsI4Q0jaG
=tG40
-----END PGP SIGNATURE-----

 

[Tore Aursand]

I've a perl script which inserts data to a database. Sometime I get an
error on the execute() statement. Now I want the script not to die but
to exit the loop and to try again with the next record.

[...]
See 'perldoc DBI' for more information.

And perldoc -f eval

Why?

 

[Malcolm Dew-Jones]

Tore Aursand ([email protected]) wrote:
: On Thu, 27 Nov 2003 08:39:09 -0700, Ron Reidy wrote:
: >>> I've a perl script which inserts data to a database. Sometime I get an
: >>> error on the execute() statement. Now I want the script not to die but
: >>> to exit the loop and to try again with the next record.

: >> [...]
: >> See 'perldoc DBI' for more information.

: > And perldoc -f eval

: Why?

eval is used to trap `die'.

 

[Tore Aursand]

[...]
See 'perldoc DBI' for more information.
And perldoc -f eval

Why?

eval is used to trap `die'.

Why would you want to do that? There is no need to trap 'die' in this
case.

And - I really don't think 'eval' should be used primarily to trap 'die',
but that's my opinion of course.

 

[James Willmore]

"or die"...? You set RaiseError above. You're not going to get the
chance to die; prepare() will die for you.

True. I meant to set that to '0'. :-(

Ditto here -- you won't get the chance to warn.

True again.

--
Jim

Copyright notice: all code written by the author in this post is
released under the GPL. http://www.gnu.org/licenses/gpl.txt
for more information.

a fortune quote ...
Yes, but which self do you want to be?

 

[Tassilo v. Parseval]

Also sprach Tore Aursand:

[...]
See 'perldoc DBI' for more information.
And perldoc -f eval
Why?

eval is used to trap `die'.

Why would you want to do that? There is no need to trap 'die' in this
case.

And - I really don't think 'eval' should be used primarily to trap 'die',
but that's my opinion of course.

'eval BLOCK' can only be used for trapping abnormal termination. It's
sort of Perl's equivalent to Java's 'try'. Are you maybe confusing 'eval
BLOCK' with 'eval STRING'?

Tassilo

 

[Ben Morrow]

And - I really don't think 'eval' should be used primarily to trap 'die',
but that's my opinion of course.

How else would you do it?

Ben

 

[Ron Reidy]

I've a perl script which inserts data to a database. Sometime I get an
error on the execute() statement. Now I want the script not to die but
to exit the loop and to try again with the next record.

[...]
See 'perldoc DBI' for more information.

And perldoc -f eval

Why?

To trap errors and continue processing.

 

[Tore Aursand]

How else would you do it?

Do what? Trap 'die'? I don't know - I've never been forced to trap
'die', and either way it's off-topic.

The OP asked how to trap DBI errors, and you don't need to trap 'die' to
do that. The DBI module only dies upon errors when the RaiseError flag is
set to a true value (a false value is default).

IMO, it's a bad habit to try to find out if your application, or a module
it's using, dies if there are ways to handle the errors in a better way.
With the DBI module - and most other modules - that's not a problem (as
explained in a previous post from me).

 

[Tore Aursand]

[...]
See 'perldoc DBI' for more information.
And perldoc -f eval

Why?

To trap errors and continue processing.

No need for that in this case. Avoid using 'eval', if possible, is my
suggestion.

 

[Randal L. Schwartz]

Tore> IMO, it's a bad habit to try to find out if your application, or a module
Tore> it's using, dies if there are ways to handle the errors in a better way.
Tore> With the DBI module - and most other modules - that's not a problem (as
Tore> explained in a previous post from me).

You're not "thinking Perl" then. I find the RaiseError mechanism
to create much cleaner code. When enabled, I can do things like:

eval {
...;
...;
...;
...;
};
if ($@) {
# something went wrong with something in this section
}

whereas without RaiseError, I'm stuck setting status variables
and nesting if statements to get the same effect.

 

[Eric J. Roode]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

IMO, it's a bad habit to try to find out if your application, or a
module it's using, dies if there are ways to handle the errors in a
better way. With the DBI module - and most other modules - that's not
a problem (as explained in a previous post from me).

I humbly disagree. In other languages, "die" is called "throwing an
exception", and eval{} is called a "try block". It's a useful technique
and can result in much cleaner code.

- --
Eric
$_ = reverse sort $ /. r , qw p ekca lre uJ reh
ts p , map $ _. $ " , qw e p h tona e and print

-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 7.0.3 for non-commercial use <http://www.pgp.com>

iQA/AwUBP8j0OWPeouIeTNHoEQJnyQCfal8tGLSqZR+KLb+r/3+Nv8GDN24AoO+3
RGCmf1dpJ00uDyEM7xyO2WHJ
=alvr
-----END PGP SIGNATURE-----

 

[Ron Reidy]

Well, my suggestion is to use the RaiseError flag and to trap errors
using eval blocks. The original question was how to best handle errors
in CGI. CGI or non-CGI doesn't matter. In my 20+ years of RDBMS
development and admin, the eval block method is the most straight
forward to understand and implement.

[...]
See 'perldoc DBI' for more information.

And perldoc -f eval

Why?

To trap errors and continue processing.

No need for that in this case. Avoid using 'eval', if possible, is my
suggestion.