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.

Solution 1

DBI supports the following: There is the DBI->trace($tracefile_handle) method (traces all DBI interactions), or $dbh->trace($tracefile_handle) which would trace just the interactions on a specific handle. Output defaults to STDERR, but by supplying $tracefile_handle, you can explicitly send output to a different file (or just use shell redirection).

DBD::pg also supports $h->trace('SQL'); This must be supported by your DBD driver to work, but fortunately DBD::Pg does support the feature.

The documentation for DBI, at CPAN - DBI, and for DBD::Pg at CPAN - DBD::Pg really gives you all you need to know on tracing.

Solution 2

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;

Solution 3

Beyond the tracing others have mentioned you should look at https://metacpan.org/pod/DBI#Statement which gives you the SQL last executed and https://metacpan.org/pod/DBI#ParamValues and https://metacpan.org/pod/DBI#ParamTypes which tell you about your parameters.

There is also DBIx::Log4perl which can log what you want without all the DBI tracing.

Comments

[NovumCoder]

Ok but doesnt this only work if there was an error? So traces will go to STDERR? I would like to get all queries no matter if there was an error or not. I know i cann provide a file name to trace(), but in docs i cannot ready anything about getting them written always not only in error cases.