Re: debugging and stepping into ->do

[Jeff Macdonald]

Thanks again Brian. Also, no need to apologize.

On Wed, Apr 25, 2018 at 3:29 PM Fennell, Brian  wrote:

> P.S. Two other ways to do UPSERT in mysql - INSERT IGNORE and REPLACE
>
>
> https://chartio.com/resources/tutorials/how-to-insert-if-row-does-not-exist-upsert-in-mysql/
>
> --
Jeff Macdonald
Ayer, MA

RE: debugging and stepping into ->do

[Fennell, Brian]

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.

Re: debugging and stepping into ->do

[Jeff Macdonald]

Thanks Brian!

I'm familiar with C. My goal with digging into DBI was to programmatically
cause a SQL error in a test case. I needed somehow to create a wrapper
around do (or execute) in which I'd examine the Statement and "die"
accordingly. This would allow me to validate database logic that rolled
back a transaction. The code I am testing is existing code and isn't
modulure. My thinking was to create an extension of an existing DBD module
(DBD::mysql) by having my extension module load first and insert "hooks" to
wrap the needed function: The below code works when $dbh->prepare and
$sth->execute are called in Perl code, but $dbh->do calls don't seem to
follow the same code path (XS stuff makes it act different). The code I'm
writing the test against has lots of INSERTs into tables using do, and it
is there that I'd like to generate the exception.

File DBD/mysql.pm (in a local directory to the test):

BEGIN {
   # fiddle with INC so correct module is loaded
my $path = shift(@INC);
push(@INC, $path);

# lie to Perl so it will load the real DBD::mysql
delete $INC{"DBD/mysql.pm"};
}

# put INC back to normal
INIT {
my $path = pop(@INC);
unshift(@INC, $path);
}

use DBD::mysql; # pulls in module from system dirs

use strict;

package DBD::mysql::st;

# hook into method by manipulating symbol table
my $orig_execute = \
*execute = \_execute;

sub my_execute {
my($sth, @bind_values) = @_;

print "execute: $sth->{'Statement'}\n";

return $orig_execute->(@_);
}

I'm resorting to having the production code crash when an environment
variable is set and having the test set that variable and validating the
results. :(




On Wed, Apr 25, 2018 at 2:06 PM Fennell, Brian  wrote:

> For quick and dirty debugging in C I usually put together something like
> this : open file in append mode, write to file, close file.  (this way is
> the code crashes you still have something useful in your debug log).
>
> Using C’s getenv allows the debug logging to be turned on/off with an
> environment variable at run time (no fancy config file parsing needed).
>
>
>
> Here are some examples if you are new to C
>
>
>
> https://www.tutorialspoint.com/c_standard_library/c_function_fprintf.htm
>
> https://www.tutorialspoint.com/c_standard_library/c_function_getenv.htm
>
> http://rosettacode.org/wiki/Category:C
>
>
>
> And in the “more than you wanted to know” department (including xs
> tutorial, perl internals, and how to use gdb on perl)
>
>
>
> http://perldoc.perl.org/perlhacktips.html
>
> http://perldoc.perl.org/index-internals.html
>
-- 
Jeff Macdonald
Ayer, MA