Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

DBI and placeholders, see resulting query?

by DreamT (Pilgrim)
on May 21, 2014 at 12:26 UTC ( [id://1086953]=perlquestion: print w/replies, xml ) Need Help??

DreamT has asked for the wisdom of the Perl Monks concerning the following question:

Dear monks,
I'm using Perl DBI for SQL queries, and I'm using placeholders a'la
my $Customer_ID = 10; my $query = "SELECT * FROM Customers where Customer_ID = ?"; my $result = $sth->execute($Customer_ID);
So far so good. But, when developing I sometimes want to see the resulting query in a warn (i.e. "SELECT * FROM Customers where Customer_ID = 10").
Is it possible to see the resulting query somehow?

Replies are listed 'Best First'.
Re: DBI and placeholders, see resulting query?
by marto (Cardinal) on May 21, 2014 at 13:04 UTC

    DBI, see the section on tracing.

Re: DBI and placeholders, see resulting query?
by erix (Prior) on May 21, 2014 at 13:36 UTC

    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
      $! 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"; }

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

Re: DBI and placeholders, see resulting query?
by mje (Curate) on May 27, 2014 at 09:35 UTC

    Neither DBI, the DBD or even the driver actually produce some SQL with your parameters magically inserted. Mostly the SQL is passed to the database with parameter markers and parsed then the parameters are sent seperately.

    You can find your parameter values from ParamValues. You might want to take a look at DBIx::Log4perl or DBIx::LogAny as both of those can output this info and even if you don't use them you can see how they do it.

Re: DBI and placeholders, see resulting query?
by sundialsvc4 (Abbot) on May 21, 2014 at 12:51 UTC

    What I typically do is to output the SQL with its placeholders, followed by a dump of the list of parameters.   It’s easy enough to interpret, and so that’s what I would suggest doing here.

      Clearly they are asking how it's done, you tell them that you do it, not how to do it.

        Actually, no. Printing the query with the placeholders and then another line with the parameters supplied is not the same as outputting the final query as if it had not been parameterized. The point is that it's simpler to output those two pieces of information together and gets the author within a very short distance of the stated goal.

      Sigh ...   Reckon it’s fairly easy to see how to do that, say with the join() function, or maybe with Data::Dumper.   There are lots of ways to implement my suggestion, if one cares to do so, and I don’t think that every single suggestion has to be accompanied by extemporaneously-written Perl source code.   I’m just not as good at that, as other people are!

      Often, my own logging-messages are more app-specific anyhow.   I don’t necessarily need to see the actual SQL-string; what I really need to know are the parameters (and these in application terms) that were sent in to it.   I already know, from the validation test-suite, that the query is correctly written and that it produces the intended results.   (Of course, of course ... Mmmmm...??)   So what I really want to know most is what are the values that are being given at that particular point.   A dump – even literally a Data::Dumper dump – of the input parameters to a function might actually be the most useful, because I already know from validation-tests that the function itself works.

      One more thought:   be certain that these types of results appear only in log-files, never in anything that might be sent back to a web-browser.   Yes, you can exploit a web-site by causing it to fail and then “stuffing” the debugging-messages that were meant only to be seen by the developer.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1086953]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others perusing the Monastery: (4)
As of 2024-03-19 10:57 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found