I frequently use placeholders and bind values with Perl DBI and during debugging phases I frequently want to see what values are being bound. The bound values are available from the statement handle’s ParamValues.

%bound_values = %{ $sth->{ParamValues} }; 

Here’s a simple script to illustrate.

#!/usr/bin/perl

use DBI;
use Data::Dumper;

my $dbh = DBI->connect(
    'dbi:Oracle:cd_sid', 
    'cdaily',
    'pa55wd');

my $sql = "select 'something' from dual where 1 = ? and 2 = ?";

my $sth = $dbh->prepare($sql);
$sth->execute(1, 2);


print Dumper $sth->{ParamValues};

Will print a result like:

$VAR1 = {
          ':p1' => 1,
          ':p2' => 2
        };

This is handy but awkward to work with when there are a lot of placeholders. Is there a simple way to print the param values in the context of the sql statement? Like so,

select 'something' from dual where 1 = 1 and 2 = 2

That would be a swell thing to be able to do.

Related:

What are placeholders in DBI, and why would I want to use them?
DBI perldoc
Tim Bunce’s Advanced Perl DBI

Advertisements