Jeff,
Sorry if I was telling you something you already knew. I do that sometimes – I
am a computer geek and sometimes my social skills are lacking.
All the best in your XS hacking.
It looks to me that this is actually documented behavior (if you know that the
documentation is on the DBI module page, and not the DBD::mysql module page):
- if you need an exception you can test for “undef” (or false) and do your own
die
- If you need the actual error number / error string you can use $h->errstr
and/or $h->err
https://metacpan.org/pod/DBI
[ . . . ]
err
$rv = $h->err;
Returns the native database engine error code from the last driver method
called. The code is typically an integer but you should not assume that.
The DBI resets $h->err to undef before almost all DBI method calls, so the
value only has a short lifespan. Also, for most drivers, the statement handles
share the same error variable as the parent database handle, so calling a
method on one handle may reset the error on the related handles.
(Methods which don't reset err before being called include err() and errstr(),
obviously, state(), rows(), func(), trace(), trace_msg(), ping(), and the tied
hash attribute FETCH() and STORE() methods.)
If you need to test for specific error conditions and have your program be
portable to different database engines, then you'll need to determine what the
corresponding error codes are for all those engines and test for all of them.
The DBI uses the value of $DBI::stderr as the err value for internal errors.
Drivers should also do likewise. The default value for $DBI::stderr is
20.
A driver may return 0 from err() to indicate a warning condition after a method
call. Similarly, a driver may return an empty string to indicate a 'success
with information' condition. In both these cases the value is false but not
undef. The errstr() and state() methods may be used to retrieve extra
information in these cases.
See "set_err" for more information.
errstr
$str = $h->errstr;
Returns the native database engine error message from the last DBI method
called. This has the same lifespan issues as the "err" method described above.
The returned string may contain multiple messages separated by newline
characters.
The errstr() method should not be used to test for errors, use err() for that,
because drivers may return 'success with information' or warning messages via
errstr() for methods that have not 'failed'.
See "set_err" for more information.
[ . . . ]
RaiseError
Type: boolean, inherited
The RaiseError attribute can be used to force errors to raise exceptions rather
than simply return error codes in the normal way. It is "off" by default. When
set "on", any method which results in an error will cause the DBI to
effectively do a die("$class $method failed: $DBI::errstr"), where $class is
the driver class and $method is the name of the method that failed. E.g.,
DBD::Oracle::db prepare failed: ... error text here ...
If you turn RaiseError on then you'd normally turn PrintError off. If
PrintError is also on, then the PrintError is done first (naturally).
[ . . . ]
do
$rows = $dbh->do($statement) or die $dbh->errstr;
$rows = $dbh->do($statement, \%attr) or die $dbh->errstr;
$rows = $dbh->do($statement, \%attr, @bind_values) or die ...
Prepare and execute a single statement. Returns the number of rows affected or
undef on error. A return value of -1 means the number of rows is not known, not
applicable, or not available.
This method is typically most useful for non-SELECT statements that either
cannot be prepared in advance (due to a limitation of the driver) or do not
need to be executed repeatedly. It should not be used for SELECT statements
because it does not return a statement handle (so you can't fetch any data).
The default do method is logically similar to:
sub do {
my($dbh, $statement, $attr, @bind_values) = @_;
my $sth = $dbh->prepare($statement, $attr) or return undef;
$sth->execute(@bind_values) or return undef;
my $rows = $sth->rows;
($rows == 0) ? "0E0" : $rows; # always return true if no error
}
[ . . . ]
In other news –
You may also be interested in the mysql UPSERT syntax “ON DUPLICATE KEY”
https://stackoverflow.com/questions/6107752/how-to-perform-an-upsert-so-that-i-can-use-both-new-and-old-values-in-update-par
https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
That way you can have mysql handle the exceptional cases, which may be more
efficient.