Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

Re^2: DBI and placeholders, see resulting query?

by erix (Vicar)
on Jun 06, 2014 at 08:58 UTC ( #1089004=note: print w/ replies, xml ) Need Help??


in reply to Re: DBI and placeholders, see resulting query?
in thread DBI and placeholders, see resulting query?

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 );


Comment on Re^2: DBI and placeholders, see resulting query?
Select or Download Code
Re^3: DBI and placeholders, see resulting query?
by mje (Deacon) on Jun 06, 2014 at 09:35 UTC

    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).

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1089004]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (6)
As of 2014-09-23 23:57 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (242 votes), past polls