#!/usr/bin/perl --
use strict; use warnings;
use DBI;
use DBD::SQLite;
Main( @ARGV );
exit( 0 );
sub Main {
    my $tmpfile = 'sqlite-deleteme.tmp';
    my $dbh = DBI->connect(
        "DBI:SQLite:database=$tmpfile",
        undef, undef,
        {qw/RaiseError 1/}
    );
    $dbh->do('CREATE TABLE test (id integer PRIMARY KEY, foo varchar(1
+00))');
    roshambo( $dbh, 1, 1 => "ro" );
    roshambo( $dbh, 2, 2 => "sham" );
    roshambo( $dbh, 3, 3 => "bo" );
    roshambo( $dbh, 999, 999 => "999" );
    undef $dbh;
    unlink $tmpfile;
}
sub roshambo {
    warn "\n### @_\n";
    my( $dbh, $trace, $id, $foo ) = @_;
    $dbh->trace( $trace );
    my $sth_ins  = $dbh->prepare('INSERT INTO test (id, foo) values (?
+, ?)');
    $sth_ins->execute( $id, $foo );
    $sth_ins->finish;
    undef $sth_ins;
    $dbh->trace(0);
    warn "### @_\n\n";
}
[download]

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
}
[download]

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
);
[download]

I believe few DBDs support many of the DBI trace flags. I say this as I added TXN, ENC, DBD and CON and perhaps one of the others (SQL?). DBD::ODBC does support all trace flags listed in the DBI docs. DBD was added so you can get DBD trace messages without any DBI ones.

I have a long standing TO_DO for DBD::Oracle to remove the old ora_verbose and replace it with DBD flag but although it sounds a trivial change it is not and it is a big change.

I'd like to see more DBDs support the trace flags. There are of course other ways to capture all SQL e.g., DBIx::Log4perl, DBIx::LogAny or by using callbacks (the first 2 of those can show your parameters with the SQL).