Mask has asked for the wisdom of the Perl Monks concerning the following question:

I have next sequence of code: 
$sql="Select * from table where column=?";
$rh=$dbh->prepare($sql);
$rh->execute($str);

[download]
Is there any possibility to see what is the executed SQL string? I can see what is in $str and what is in $sql, but when the column is 'char' then it should be automatically put _'_ at the begining and end of string, what happening in case if I have this character in $str?

Replies are listed 'Best First'.
Re: How to see executed SQL string?
by hmerrill (Friar) on Jun 04, 2003 at 12:25 UTC
    Assuming you are using the DBI module, you can use "trace" to see the query that is actually being sent to the database. Do 
       perldoc DBI

    [download]
    at a command prompt to read the excellent documentation that is included with the DBI module. Search(using the forward slash "/") for the word "trace".

    The basic idea is to turn trace on before the statements you are interested in, and turn it off after - something like this: 
       $sql="Select * from table where column=?";
   DBI->trace(2); # turn trace on
   $rh=$dbh->prepare($sql);
   $rh->execute($str);
   DBI->trace(0); # turn trace off

    [download]
    HTH.
[reply]
[d/l]
[select]
      You might want to direct trace to a file: I find that easier usually. Also be aware that trace has 9 levels, and you can get more detail going up a level. The specific bind variables would come out with a trace(2) as hmerrill noted.
      DBI->trace(2, "tracefile.txt");
[reply]
[d/l]
Re: How to see executed SQL string?
by naChoZ (Curate) on Jun 04, 2003 at 12:33 UTC
    Also in the DBI perldoc, search for the Statement Handle Attributes section.

    Statement (string, read-only)
    Returns the statement string passed to the the prepare entry elsewhere in this documentmethod.

    ~~
    naChoZ

[reply]
Re: How to see executed SQL string?
by Jenda (Abbot) on Jun 04, 2003 at 19:40 UTC

    Apart from the DBI's tracing you might want to see if your database allows some tracing. Eg. if you were using MS SQL Server you might what to try the "SQL Profiler" that gets installed with it. It may give you more information than you need.

    Jenda
    Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.
       -- Rick Osborne

    Edit by castaway: Closed small tag in signature

[reply]
      Thank you guys, it was a real help!
[reply]

Back to Seekers of Perl Wisdom