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

So the code below gave me the most detail. I tried checking for $dbHandle->err after the prepare statement but it returned nothing.

And I just wanted to just be clear: setting RaiseError => 1 does cause the script to exit immediately, yes?

Thanks for everyone's help. 
sub insertItem {
    (my $insertCom) = @_;
    
    $dbHandle=$db->prepare($insertCom);


    my $result = $dbHandle->execute();
    if ($dbHandle->err || $dbHandle->errstr) {
        $logger->debug("err | $DBI::err | $DBI::errstr");
    }
    


    return $result;
}

[download]

Replies are listed 'Best First'.
Re^5: Can I get the actual error for DBI->execute() ?
by soonix (Chancellor) on Jun 22, 2024 at 13:57 UTC
[reply]
Re^5: Can I get the actual error for DBI->execute() ?
by Danny (Chaplain) on Jun 21, 2024 at 20:34 UTC
    SergioQ wrote: And I just wanted to just be clear: setting RaiseError => 1 does cause the script to exit immediately, yes?

    Yes. You can also log any errors associated with the database handle without exiting by passing PrintError => 1 and warnings with PrintWarn => 1 to the DBI->connect() call. I generally don't want to make any changes to the database if any errors occur so I use AutoCommit => 0, and only $dbh->commit if everything passes within whatever constraints I set.

[reply]
Re^5: Can I get the actual error for DBI->execute() ?
by Marshall (Canon) on Jun 23, 2024 at 02:21 UTC
    Yes. setting RaiseError => 1 does cause the script to exit immediately, yes?

    I prefer to do it that way instead of checking for errors after each DB operation. In your code above, prepare can also throw an error. $dbHandle will be undef if there is an error and you should check for that if you don't set RaiseError.

    Note that execute sometimes returns a numerical value that has meaning, like perhaps the number of rows updated. execute behaves differently say for a SELECT statement where no information is provided. So a simple check of $result to detect an error won't work like it does for $dbHandle.

[reply]
      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).

[reply]
        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]

In Section Seekers of Perl Wisdom