CODEWITHSUNDEEP
mysqlsqlperlprepared-statementdbi
aidan
aidan

Reputation: 9576

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

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

Upvotes: 21

Views: 28046

Answers (9)

Chris Writer
Chris Writer

Reputation: 1

Put:

$dbh->trace(2);

before the prepare and execute statements. This will output lots of debugging information, including the sql with the binding values.

Just be cautious when using this in a production environment, as query tracing can reveal sensitive information and should be used for debugging or development purposes only.

Upvotes: 0

Ωmega
Ωmega

Reputation: 43663

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});

Upvotes: 2

Dave Smylie
Dave Smylie

Reputation: 2693

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";
}

Upvotes: 0

Sinan Ünür
Sinan Ünür

Reputation: 118118

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.

Upvotes: 18

rjh
rjh

Reputation: 50274

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.

Upvotes: 7

Ishan De Silva
Ishan De Silva

Reputation: 1005

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

Upvotes: 12

bohica
bohica

Reputation: 5992

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.

Upvotes: 1

not2qubit
not2qubit

Reputation: 16912

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.

Upvotes: 1

masto
masto

Reputation: 1376

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.

Upvotes: 10

Related Questions