Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

dbi & placeholders: getting the actual sql

by nop (Hermit)
on Sep 12, 2001 at 01:39 UTC ( [id://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
Domain Nodelet?
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?Last hourOther CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (4)
As of 2024-03-29 16:01 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found