Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

Re: DBI and placeholders, see resulting query?

by erix (Vicar)
on May 21, 2014 at 13:36 UTC ( #1086968=note: print w/ replies, xml ) Need Help??


in reply to DBI and placeholders, see resulting query?

Assuming customer_dbi.pl:

#!/opt/perl-5.20/bin/perl use strict; use warnings; use DBI; main(); exit; sub main { my $dbh = DBI->connect or die "oops - $!\n"; my $Customer_ID = 10; my $sql = "select customer_id from t where customer_id = ?"; my $sth = $dbh->prepare($sql) or die "oops - $!\n"; my $rc = $sth->execute($Customer_ID); while (my $rrow = $sth->fetch) { print $rrow->[0], "\n"; } }

You can call it with DBI_TRACE either on or off:

$ DBI_TRACE=0 ./customer_dbi.pl 10 $ DBI_TRACE=1 ./customer_dbi.pl DBI 1.628-nothread default trace level set to 0x0/1 (pid 5340 pi 0 +) at DBI.pm line 287 via customer_dbi.pl line 4 -> DBI->connect(, , ****) -> DBI->install_driver(Pg) for linux perl=5.020000 pid=5340 ruid=5 +00 euid=500 install_driver: DBD::Pg version 2.19.3 loaded from /home/aardva +rk/perl-5.20/lib/site_perl/5.20.0/x86_64-linux/DBD/Pg.pm <- install_driver= DBI::dr=HASH(0xd1ad40) !! warn: 0 CLEARED by call to default_user method <- default_user(undef, undef, ...)= ( undef undef ) [2 items] at D +BI.pm line 657 <- connect('', undef, ...)= ( DBI::db=HASH(0xfd4258) ) [1 items] a +t DBI.pm line 669 <- STORE('PrintError', 1)= ( 1 ) [1 items] at DBI.pm line 721 <- STORE('AutoCommit', 1)= ( 1 ) [1 items] at DBI.pm line 721 <- STORE('Username', undef)= ( 1 ) [1 items] at DBI.pm line 724 <- connected= ( undef ) [1 items] at DBI.pm line 731 <- connect= DBI::db=HASH(0xfd4258) <- STORE('dbi_connect_closure', CODE(0xfd3c58))= ( 1 ) [1 items] a +t DBI.pm line 740 <- prepare('select * from t where customer_id = ?')= ( DBI::st=HAS +H(0xfd4588) ) [1 items] at customer_dbi.pl line 12 <- execute(10)= ( 1 ) [1 items] at customer_dbi.pl line 13 <- fetch= ( [ 10 ] ) [1 items] row1 at customer_dbi.pl line 14 10 <- fetch= ( undef ) [1 items] row1 at customer_dbi.pl line 14 <- DESTROY(DBI::st=HASH(0xfd4348))= ( undef ) [1 items] at custome +r_dbi.pl line 5 <- DESTROY(DBI::db=HASH(0xfd4198))= ( undef ) [1 items] at custome +r_dbi.pl line 5 <- disconnect_all= ( '' ) [1 items] at DBI.pm line 748 ! <- DESTROY(DBI::dr=HASH(0xd1ad40))= ( undef ) [1 items] during glo +bal destruction


Comment on Re: DBI and placeholders, see resulting query?
Select or Download Code
Re^2: DBI and placeholders, see resulting query?
by Anonymous Monk on May 22, 2014 at 00:41 UTC
    $! in error checking for DBI? my version
    #!/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"; }
Re^2: DBI and placeholders, see resulting query?
by erix (Vicar) on Jun 06, 2014 at 08:58 UTC

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

      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://1086968]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (18)
As of 2015-07-06 17:43 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (79 votes), past polls