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]

In reply to Re^2: DBI and placeholders, see resulting query? by erix
in thread DBI and placeholders, see resulting query? by DreamT

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.