Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

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

Comment on dbi & placeholders: getting the actual sql
Download Code
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?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (7)
As of 2014-08-29 00:14 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (275 votes), past polls