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

Question

This is a standard insert example from a 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 changed it a bit, so I can see on which error the request ended ( die ($query . " " . $dbh->errstr) ). However, I liked the query with the associated values (how it was executed). How to get it?

---

Edit

Btw, I found an awkward way to see a query with related values: you have to make a syntax error in the query. For example, if I modify the request above:

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

I got it back as I wanted:

DBD :: mysql :: st execute failed: you encountered an error in the SQL syntax; check the manual that matches the version of your MySQL server for the correct syntax to use the sample 'xINSERT INTO (product_code, qty, price) VALUES (' 1 ',' 2 ',' 3 ')' on line 1

Sometimes it really helps. At least it's with me.

Answer 1

You can use DBI ParamValues to get parameter values, but you are unlikely to find any method in DBD to get parameters actually in SQL, because they are mostly sent to the database after SQL analysis. You can look at DBIx :: Log4perl to find out how ParamValues ​​is used in the error handler. You may also find some parts of DBIx :: Log4perl useful.

Answer 2

There is no standard way to do this. The closest approximation is to replace each placeholder with a (probably indicated) value. In general, it is rather difficult to reliably parse the SQL statement to find question marks that are placeholders, and not parts of identifiers or lines with delimiters or comments. In this example, you can simply search for question marks; this will not always work:

 INSERT /* ? */ INTO "??".Sales VALUES('?', ?, ?, ?); 

Please note that in most, but not all, DBMSs (and, therefore, most DBD drivers), the statement is sent to the DBMS when it is ready; when the instruction is executed, only values ​​are sent. An operator is never created that is created with all the values ​​substituted in the VALUES list, so neither DBI nor DBMS should create it.

Answer 3

In my opinion, DBI does not support any common DBI method, but individual database drivers may allow this. What database are you using?