Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
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

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?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://111811]
Approved by root
help
Chatterbox?
[Yary]: ah thanks for those links
[hippo]: Just bear in mind that all such ideas are subjective.
[choroba]: There might be a different standard at the shop you write the code for.
[choroba]: The order of package - (strict+warnings | warnings+strict) - modules makes sense, POD and strict-warnings order are not so important

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (9)
As of 2018-01-23 17:07 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    How did you see in the new year?










    Results (250 votes). Check out past polls.

    Notices?