Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options

DBI: seeing what's actually executed

by thor (Priest)
on Sep 29, 2005 at 15:21 UTC ( #496140=perlquestion: print w/replies, xml ) Need Help??

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

Greetings all,

A co-worker of mine recently came to me with a problem regarding the DBI. Her queries were returning 0 rows where she expected data back. So, I showed her how to turn tracing and we worked with it from there. However, it seems to me that the output could be a little more useful if the entire SQL were assembled as it were. Allow me to explain. At present, the output in the trace log shows the statement prepared and the individual bind values (i.e. what was passed to execute), but it doesn't put the two together for easy copy/paste action. Is there any way to get this? Even if it's not a DBI-type solution (as I understand that the substitution is done server side and not by perl itself), but rather a perlish solution.

Thanks in advance,


Feel the white light, the light within
Be your own disciple, fan the sparks of will
For all of us waiting, your kingdom will come

Replies are listed 'Best First'.
Re: DBI: seeing what's actually executed
by davidrw (Prior) on Sep 29, 2005 at 15:32 UTC
    It does show the whole "pieced together" statement upon ->execute ... what trace level are you using? what version of DBI?

    Update: I see the complete statement using DBD::Pg and trace(2) .. but i don't see it w/this test snippet:
    use strict; use warnings; use DBI; my $dbh = DBI->connect("DBI:CSV:f_dir=/tmp"); $dbh->trace(2); $dbh->do("CREATE TABLE a (id INTEGER, name CHAR(10))"); $dbh->do("INSERT INTO a (id, name) values (?,?)", {}, 1, 'one'); $dbh->selectall_arrayref("select * from a where id < ?",{},2); my $sth = $dbh->prepare("select * from a where id < ?"); $sth->execute(2); use Data::Dumper; warn Dumper $sth->fetchall_arrayref();
      I tried every trace level up to 5. I'm using DBI v1.43 with ODBC v1.10. I'm trying to execute a SELECT statement. When I view the trace log, I see the word SELECT in there either once or twice (depending on the trace level). If the statements were assembled, I'd see it a couple hundred times.


      Feel the white light, the light within
      Be your own disciple, fan the sparks of will
      For all of us waiting, your kingdom will come

        Try setting odbc_exec_direct.
Re: DBI: seeing what's actually executed
by graff (Chancellor) on Sep 29, 2005 at 22:13 UTC
    Perhaps you could show us a sample of the trace output you are getting? I think I have an idea about the sort of output you might be looking at, based on experience with failed/badly formed queries passed to DBI/DBD::Oracle, and I thought about writing something to parse that into something coherent enough for a human to interpret and use. (But I just haven't got around to that yet.)

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://496140]
Approved by g0n
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (3)
As of 2020-10-25 23:35 GMT
Find Nodes?
    Voting Booth?
    My favourite web site is:

    Results (249 votes). Check out past polls.