Perl DBI: how to see failed query with bound values?

Question

This is a standard inserting example from DBI manual:

     my $query = q{
       INSERT INTO sales (product_code, qty, price) VALUES (?, ?, ?)
     };
     my $sth = $dbh->prepare($query) or die $dbh->errstr;
     while (<>) {
         chomp;
         my ($product_code, $qty, $price) = split /,/;
         $sth->execute($product_code, $qty, $price) or die ($query . " " . $dbh->errstr);
     }
     $dbh->commit or die $dbh->errstr;

I modified it a bit, so I can see on die which query failed (die ($query . " " . $dbh->errstr)). Still I'd liked to see the query with bound values (as it was executed). How to get it?

---

Edit

Btw, i found a awkward way to see query with bound values too: you have to make syntax error in query. For example, if i change query above like that:

     my $query = q{
       xINSERT INTO sales (product_code, qty, price) VALUES (?, ?, ?)
     };

I got it back as i wanted:

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xINSERT INTO sample (product_code, qty, price) VALUES ('1', '2', '3')' at line 1

Sometimes it really helps. At least it did to me.

Answer 1

You can use DBI's ParamValues to obtain the parameter values but you are unlikely to find any method in a DBD to obtain the parameters actually in the SQL because they are mostly sent to the database after the SQL is parsed. You can look at DBIx::Log4perl to see how ParamValues is used in the error handler. You might also find some parts of DBIx::Log4perl useful.