How can I print the SQL query executed after Perl's DBI fills in the placeholders?

Question

I'm using Perl's DBI module. I prepare a statement using placeholders, then execute the query.

Is it possible to print out the final query that was executed without manually escaping the parameters and dropping them into the placeholders?

Thanks

Answer 1

See Tracing in DBI. The following works using DBD::SQLite but produces a lot of output:

$dbh->trace($dbh->parse_trace_flags('SQL|1|test'));

Output:

<- prepare('SELECT ... FROM ... WHERE ... = ?')= DBI::st=HASH(0x21ee924) at booklet-excel.pl line 213

<- execute('Inhaler')= '0E0' at booklet-excel.pl line 215

etc etc.

You could plug your own filter in to the trace stream to only keep prepares.

Answer 2

You can do a debug print of a prepared statement using the Statement attribute. This can be accessed either with a "statement handle" or a "database handle".

print $sth->{Statement} # with a statement handle

print $dbh->{Statement} # with a database handle

Answer 3

Not in general, because DBI doesn't necessarily produce such a query. If your database supports prepared statements and placeholders in its API, DBI will pass them through and let the database do the work, which is one of the reasons to use prepared statements.

Answer 4

This works for DBD::mysql with server-side prepare disabled (the default):

$ DBI_TRACE=2 perl your-script-here

It will print each statement twice, once before binding parameters and once after. The latter will be well-formed SQL that you can run yourself.

There is also a module, DBI::Log, which only prints SQL statements (no other debug noise), and optional timing information and caller stacktraces. It's really useful.

Answer 5

As masto says in general the placeholders in the SQL are not directly replaced with your parameters. The whole point of parameterized SQL is the SQL with placeholders is passed to the database engine to parse once and then it just receives the parameters.

As idssl notes you can obtain the SQL back from the statement or connection handle and you can also retrieve the parameters from ParamValues. If you don't want to do this yourself you can use something like DBIx::Log4perl to log just the SQL and parameters. See DBIX_L4P_LOG_DELAYBINDPARAM which outputs something like this:

DEBUG - prepare(0.1): 'insert into mje values(?,?)'
DEBUG - $execute(0.1) = [{':p1' => 1,':p2' => 'fred'},undef];

Of course as it uses Log::Log4perl you can omit the "DEBUG - " if you want. There is a small tutorial for using DBIx::Log4perl here.

You should be able to use DBIx::Log4perl with any DBD and if you cannot for some reason RT it and I will look at it.

If you don't want to go with DBIx::Log4perl and the DBI trace options don't suit your needs you can write callbacks for DBI's prepare/select*/execute methods and gather whatever you like in them.

Answer 6

If you don't want to create your own tracer module (as suggested by Sinan), you are better off just trying to print the argument hash before it is passed to $sth->execute(). This is especially true, since the "Trace" functionality is DBMS dependent and $sth->{Statement} only returns the SQL placeholder statement. Here's what I did.

...
while (my $row = $csv->getline_hr($fh)) {
    my $cval = "";
    my $tquery = $query;
    foreach my $j (@cols) { 
            $cval = $row->{$j};
            $tquery =~ s/\?/\'$cval\'/;
    }
    print "$tquery\n\n";
    $rc = $sth->execute(@{$row}{@cols});
}

Where I have used Text::CSV... NOTE: This is not exact, due to DBMS implementation dependent handling of {'}s.

Answer 7

For the majority of queries, the simplest debugging is to use the following...

• If you prepare and execute a single statement using do method, use:

   use feature 'say';
   say $dbh->{Statement};
  

• If you use prepare and execute methods separately, use:

   use feature 'say';
   use Data::Dumper;
   $Data::Dumper::Sortkeys = 1;
   say $sth->{Statement};
   say Dumper($sth->{ParamValues});
  

Answer 8

For perl neophytes, my solution, copied from not2qubit and simplified/hopefully made a bit more generic/reuseable:

sub dump_query {
  my $tquery = shift;
  my @args = shift;
  my $j;
    foreach my $j (@args) { $tquery =~ s/\?/\'$j\'/; }
    print STDERR "$tquery\n\n";
}