Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister

dbi & placeholders: getting the actual sql

by nop (Hermit)
on Sep 12, 2001 at 01:39 UTC ( #111811=perlquestion: print w/replies, xml ) Need Help??
nop has asked for the wisdom of the Perl Monks concerning the following question:

I have DBI code like this:
my $adsref = $dbh_g->selectcol_arrayref($sql, undef, @bindvals);
where the sql has a slew of placeholders.

I'd like get my hands on the actual sql that DBI plunks against the database... do I have to build the string with a regexp or can I get it from DBI?
  1. regexp What's an efficient way to replace all the "?" placeholders in my sql with the corresponding elements from the bindvals? I'd prefer the quoting to be right, too.
  2. DBI ...or is there just a way to ask DBI for the actual sql on the last statement handle? Laziness as a Virtue.
Thanks for any help.

Replies are listed 'Best First'.
Re: dbi & placeholders: getting the actual sql
by runrig (Abbot) on Sep 12, 2001 at 01:53 UTC
    Depending on the database, the sql may get passed along as is, with all '?'s intact. For the others, you'll have to dig through the DBD code to see what happens. As for your questions, these may not be exactly what you want, but:

    1. There's the quote method...

    2. There's $sth->{Statement}

(dkubb) Re: (2) dbi & placeholders: getting the actual sql
by Anonymous Monk on Sep 12, 2001 at 06:12 UTC

    Here is what I do sometimes during debugging to see what values will be put into the placeholders by DBI for me:

    my $statement = $sql; $statement =~ s/\?/$dbh->quote($_)/e for @bindvals; print "The SQL query will be: [$statement]";

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://111811]
Approved by root
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (4)
As of 2018-05-26 13:08 GMT
Find Nodes?
    Voting Booth?