I was annoyed by the verbosity (logorrhoea) of my above "solution" and I wanted to figure out a better way to trace only one or a few statement. (i.e. switch tracing on and off)
I was going to try for other DB's than postgres as well but as I don't find the time to dig those details up (surely it must be possible) here is at least a better way for DBD::Pg (postgresql).
For postgres:
$dbh->{trace} = 'SQL'; turns SQL tracing on
$dbh->{trace} = 0; turns tracing off.
I'd like to see how such switching is done in other DBD's.
#!/opt/perl-5.20/bin/perl
use strict;
use warnings;
use DBI;
main();
exit;
sub main {
my $dbh = DBI->connect or die "oops - $!\n";
$dbh->{RaiseError} = 1;
# if table public.t does not exist, create it:
if ($dbh->selectrow_arrayref( "
select count(*)
from information_schema.tables
where table_schema = 'public'
and table_name = 't' " )->[0] == 0)
{
$dbh->do("create table public.t as select id from generate_series(
+1,20) as f(id)");
}
my $sql = "select id from t where id = ?";
my $sth = $dbh->prepare($sql) or die "oops - $!\n";
trace_test($sth, 'SQL' , 10);
trace_test($sth, undef , 11);
trace_test($sth, 0 , 12);
trace_test($sth, 'SQL' , 1000);
}
sub trace_test {
my ($sth, $trace, $id) = @_;
if (defined $trace) {
$sth->trace($trace);
}
print "--> trace_test() start. trace = ";
print $sth->trace;
print ", passed '", (defined $trace ? $trace : 'undef'), "'\n";
my $rc = $sth->execute($id);
while (my $rrow = $sth->fetch) { print "", $rrow->[0], "\n"; }
print "\n";
$sth->trace(0); # always switch off
}
Now we only get ouptut where we asked for it:
--> trace_test() start. trace = 256, passed 'SQL'
EXECUTE select id from t where id = $1 (
$1: 10
);
10
--> trace_test() start. trace = 0, passed 'undef'
11
--> trace_test() start. trace = 0, passed '0'
12
--> trace_test() start. trace = 256, passed 'SQL'
EXECUTE dbdpg_p15160_1 (
$1: 1000
);
|