Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic

Pretty printing the exact query (with bind variables) executed by DBI?

by Withigo (Friar)
on Dec 18, 2007 at 01:50 UTC ( #657584=perlquestion: print w/replies, xml ) Need Help??
Withigo has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,
I often find myself troubleshooting why a complex DBI select query return zero rows. Often, these queries are over a dozen tables in a complex join, and also contain dynamically created SQL 'where' conditions based on combinations of certain arguments being present.

Being the lazy monk that I am, I just want to see a pretty version of the query that DBI executes, with the bind variables interpolated in the query, so that I can manually pick apart the query and determined what data is missing from which tables that made the query return zero rows.

I know about DBI->trace, but the output is not very readable. There's a lot of debugging info that I don't need. Can anyone recommend an existing module or share a snippet of code you may have which parses the trace output, and pretty prints the query like I described?
  • Comment on Pretty printing the exact query (with bind variables) executed by DBI?

Replies are listed 'Best First'.
Re: Pretty printing the exact query (with bind variables) executed by DBI?
by jZed (Prior) on Dec 18, 2007 at 02:07 UTC
    Part of the problem is that DBI deos not ever interpolate the binds into the SQL and most (all?) DBDs likewise pass along the SQL and bind values separately to the RDBMS. The best you can do is simulate it as I show in Interpolate binds into SQL on error - DBI subclassing. That should show you how to "assemble" the statement and where you can format it as you desire.
Re: Pretty printing the exact query (with bind variables) executed by DBI?
by erix (Parson) on Dec 18, 2007 at 02:45 UTC

    I find it handy (as a debug facility) to add the actual values out-commented behind the placeholders. This doesn't give you the exact statement but it makes it easy to reproduce them, or just to check the actual values that the statement was executed with:

    select name, address, phone where name = ? -- 'Doe'

    Another approach would be to let DBI trace produce its mess, and make your statements easy to parse out of the 'wealth of information' by a markup scheme, again SQL-outcommented, of course:

    -----8<--------------- select name, address, phone where name = ? -----8<---------------
Re: Pretty printing the exact query (with bind variables) executed by DBI?
by pfaut (Priest) on Dec 18, 2007 at 02:11 UTC

    That information may not be available. Oracle's OCI supports parameterized queries. The prepare step sends the statement with placeholders to the server for parsing and generation of an execution plan. The data is plugged in on the server at the time of execution. The full statement with data plugged into placeholders is never available to DBI or DBD::Oracle.

    There may be other ways to find out, though. I know PostgreSQL can be told to keep a log of the statements it executes. If we knew what database you were working with, maybe someone who knows that database could tell you how to trace at the database.

    90% of every Perl application is already written.
Re: Pretty printing the exact query (with bind variables) executed by DBI?
by andye (Curate) on Dec 18, 2007 at 20:48 UTC

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://657584]
Approved by dsheroh
[Corion]: marioroy: Oh, that's always cool, having API-compatible modules. This makes testing and comparing things much easier
[marioroy]: IPC in MCE::Shared can handle 400k (sends) per second. That's seems a lot for being a pure-Perl module. After making the release, will come back and post a solution for a node by a fellow wanting faster logging.
[Corion]: While working on WWW::Mechanize:: Chrome, I had the suspicion that AnyEvent was doing something wrong, but I was able to swap it out for Mojolicious and the error persisted.
[Corion]: Of course, the error was in my own code ;)
[marioroy]: Corion, start and start_child in MCE::Hobo::Manager return a MCE::Hobo object, whereas P::FM returns the PID. I can have it return the PID though. I tried Hobo::Manager with several P::FM modules, just changed P::FM to MCE::Hobo::Manager and it works.

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (6)
As of 2017-05-26 08:36 GMT
Find Nodes?
    Voting Booth?