debugging - Dump prepared sql query from DBI statement in PERL

Question

im using DBI in Perl to connect to my PostgreSQL Database. Everything is working fine but in my debugging (printing results etc.) iam not able to see if the query prepared by perls DBI module is really correct.

I have something like this:

$sth->prepare( qq{SELECT * FROM company WHERE companyname LIKE ? AND city = ?});
$sth->execute( $name.'%', $city);

Iam not able to see how the sql query looks after calling execute, as execute is the latest step where parameters are binded to the query.

I would like to have something like $sth->getLastExecutedQuery() or something to see how the query looked like.

In this case the function getLastExecutedQuery() would return:

SELECT * FROM company WHERE companyname LIKE 'Company Name%' AND city = 'City name';

Is there any way to get this? Its only for debugging purposes.

Answer 1

Use the DBI tracing facility. It works like this:

use strict;
use warnings;
use DBI;
my %opt = ( RaiseError => 1 );
my $dbh = DBI->connect( 'dbi:mysql:test', 'fred', 'secret', \%opt );
$dbh->trace(2); # level 2 shows statement with inserted parameters
my $sql_i = 'insert into t1 (a, b) values ( ?, ? )';
my $sth_i = $dbh->prepare( $sql_i );
for ( qw/ eins zwei drei / ) {
    $sth_i->execute( $_, $_ );
}
$dbh->disconnect;