in reply to Re^5: Can I get the actual error for DBI->execute() ?
in thread Can I get the actual error for DBI->execute() ?

Marshall wrote: So a simple check of $result to detect an error won't work like it does for $dbHandle.

I'm pretty sure $sth->execute(...) returns undef if and only if there is an error. From perldoc DBI:

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

  • Comment on Re^6: Can I get the actual error for DBI->execute() ?

Replies are listed 'Best First'.
Re^7: Can I get the actual error for DBI->execute() ?
by Marshall (Canon) on Jun 24, 2024 at 13:16 UTC
    I had to do some review of this because it can get confusing.

    By design you can't get number of rows out of a SELECT statement. You can only get a numerical result out of DELETE or UPDATE statements. The question is how to get that info from Perl DBI? There are 2 ways:

    First with a do statement:
     my $rows = $dbh->do("DELETE FROM table_name WHERE col2='X'");
    $rows can be a) Integer>0 b) 0E0 c) undef
    0E0 is the DB's "true but numeric zero result".
    undef would mean the statement failed.

    Second with prepare/execute:

    my $sth= $dbh->prepare("DELETE FROM table_name WHERE col2=?");
$sth->execute("X");
my $rows = $sth->rows();

    [download]
    $rows can be (like first example): a) Integer>0 b) 0E0 c) undef

    I almost always run with RaiseError=1 so checking for undef doesn't come up.

    I don't know if DBI returns 1 or 0E0 for a successful SELECT operation.

[reply]
[d/l]
[select]
      Marshall write: I don't know if DBI returns 1 or 0E0 for a successful SELECT operation.

      Assuming there wasn't an error, from perldoc DBI: "If the number of rows affected is not known, then "execute" returns -1."

[reply]
        Cute. So,
        undef: sql command didn't work
        0E0: sql command worked and returned zero rows
        >0: sql command worked and returned indicated number of rows
        -1: sql command worked but number of rows is unknown (e.g. select)
[reply]

In Section Seekers of Perl Wisdom