DBI error handling

Discussion in 'Perl Misc' started by Lars Purschke, Nov 27, 2003.

  1. 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
     
    Lars Purschke, Nov 27, 2003
    #1
    1. Advertising

  2. Lars Purschke

    Tore Aursand Guest

    On Thu, 27 Nov 2003 13:44:32 +0100, Lars Purschke 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.


    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.


    --
    Tore Aursand <>
    "A teacher is never a giver of truth - he is a guide, a pointer to the
    truth that each student must find for himself. A good teacher is
    merely a catalyst." -- Bruce Lee
     
    Tore Aursand, Nov 27, 2003
    #2
    1. Advertising

  3. Lars Purschke

    Ron Reidy Guest

    Tore Aursand wrote:
    > On Thu, 27 Nov 2003 13:44:32 +0100, Lars Purschke 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.

    >
    >
    > 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

    >
    >



    --
    Ron Reidy
    Oracle DBA
     
    Ron Reidy, Nov 27, 2003
    #3
  4. Lars Purschke

    Guest

    Lars Purschke <> wrote:
    > 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

    --
    -------------------- http://NewsReader.Com/ --------------------
    Usenet Newsgroup Service New Rate! $9.95/Month 50GB
     
    , Nov 27, 2003
    #4
  5. Lars Purschke wrote:

    > 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
     
    Matthew Braid, Nov 28, 2003
    #5
  6. On Thu, 27 Nov 2003 13:44:32 +0100
    Lars Purschke <> 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. 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, Nov 28, 2003
    #6
  7. On Fri, 28 Nov 2003 17:50:46 GMT
    James Willmore <> wrote:
    > 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
     
    James Willmore, Nov 28, 2003
    #7
  8. -----BEGIN PGP SIGNED MESSAGE-----
    Hash: SHA1

    James Willmore <> wrote in
    news::

    > 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-----
     
    Eric J. Roode, Nov 29, 2003
    #8
  9. Lars Purschke

    Tore Aursand Guest

    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?


    --
    Tore Aursand <>
    "To cease smoking is the easiset thing I ever did. I ought to know,
    I've done it a thousand times." -- Mark Twain
     
    Tore Aursand, Nov 29, 2003
    #9
  10. Tore Aursand () 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'.
     
    Malcolm Dew-Jones, Nov 29, 2003
    #10
  11. Lars Purschke

    Tore Aursand Guest

    On Fri, 28 Nov 2003 20:33:23 -0800, Malcolm Dew-Jones wrote:
    >>>> [...]
    >>>> 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.


    --
    Tore Aursand <>
    "To cease smoking is the easiset thing I ever did. I ought to know,
    I've done it a thousand times." -- Mark Twain
     
    Tore Aursand, Nov 29, 2003
    #11
  12. On Fri, 28 Nov 2003 18:05:13 -0600
    "Eric J. Roode" <> wrote:
    > James Willmore <> wrote in
    > news::
    >
    > > 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";

    <snip>
    > > $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.


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

    > > #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.


    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?
     
    James Willmore, Nov 29, 2003
    #12
  13. Also sprach Tore Aursand:

    > On Fri, 28 Nov 2003 20:33:23 -0800, Malcolm Dew-Jones wrote:
    >>>>> [...]
    >>>>> 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
    --
    $_=q#",}])!JAPH!qq(tsuJ[{@"tnirp}3..0}_$;//::niam/s~=)]3[))_$-3(rellac(=_$({
    pam{rekcahbus})(rekcah{lrePbus})(lreP{rehtonabus})!JAPH!qq(rehtona{tsuJbus#;
    $_=reverse,s+(?<=sub).+q#q!'"qq.\t$&."'!#+sexisexiixesixeseg;y~\n~~dddd;eval
     
    Tassilo v. Parseval, Nov 29, 2003
    #13
  14. Lars Purschke

    Ben Morrow Guest

    Tore Aursand <> wrote:
    > 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

    --
    Like all men in Babylon I have been a proconsul; like all, a slave ... During
    one lunar year, I have been declared invisible; I shrieked and was not heard,
    I stole my bread and was not decapitated.
    ~ ~ Jorge Luis Borges, 'The Babylon Lottery'
     
    Ben Morrow, Nov 29, 2003
    #14
  15. Lars Purschke

    Ron Reidy Guest

    Tore Aursand 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?
    >

    To trap errors and continue processing.

    >



    --
    Ron Reidy
    Oracle DBA
     
    Ron Reidy, Nov 29, 2003
    #15
  16. Lars Purschke

    Tore Aursand Guest

    On Sat, 29 Nov 2003 11:39:16 +0000, Ben Morrow wrote:
    >> 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?


    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 <>
    "To cease smoking is the easiset thing I ever did. I ought to know,
    I've done it a thousand times." -- Mark Twain
     
    Tore Aursand, Nov 29, 2003
    #16
  17. Lars Purschke

    Tore Aursand Guest

    On Sat, 29 Nov 2003 07:41:03 -0700, Ron Reidy wrote:
    >>>> [...]
    >>>> 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.


    --
    Tore Aursand <>
    "To cease smoking is the easiset thing I ever did. I ought to know,
    I've done it a thousand times." -- Mark Twain
     
    Tore Aursand, Nov 29, 2003
    #17
  18. >>>>> "Tore" == Tore Aursand <> writes:

    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.

    --
    Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
    <> <URL:http://www.stonehenge.com/merlyn/>
    Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
    See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!
     
    Randal L. Schwartz, Nov 29, 2003
    #18
  19. -----BEGIN PGP SIGNED MESSAGE-----
    Hash: SHA1

    Tore Aursand <> wrote in
    news:p:

    > 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-----
     
    Eric J. Roode, Nov 29, 2003
    #19
  20. Lars Purschke

    Ron Reidy Guest

    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.

    Tore Aursand wrote:
    > On Sat, 29 Nov 2003 07:41:03 -0700, Ron Reidy wrote:
    >
    >>>>>[...]
    >>>>>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.
    >
    >



    --
    Ron Reidy
    Oracle DBA
     
    Ron Reidy, Nov 29, 2003
    #20
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ulloa
    Replies:
    1
    Views:
    546
    Juha Laiho
    Jul 22, 2004
  2. Mark Tarver
    Replies:
    22
    Views:
    1,376
    J Kenneth King
    Apr 26, 2009
  3. Jerome Hauss
    Replies:
    0
    Views:
    186
    Jerome Hauss
    Oct 13, 2004
  4. Asby

    Mason, DBI, and DBI::Pg

    Asby, Jul 24, 2003, in forum: Perl Misc
    Replies:
    0
    Views:
    193
  5. Tim Haynes
    Replies:
    3
    Views:
    157
    Ron Reidy
    Sep 13, 2003
Loading...

Share This Page