Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
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 }

      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 rifling through the Monastery: (10)
As of 2014-08-21 16:26 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (136 votes), past polls