Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Perl Programming Language

Help With Placeholders


This question pertains to using DBI with a MySQL database.....

I use placeholders for all my queries (mainly for security). The
problem I have is that I need to be able to print the actual queries
my code runs into a log.... with the placeholders replaced.

For example, consider the following:

my $id = "example";
my $sql = "UPDATE Customers SET ID=?";
my $query = $db->prepare($sql);
$query->execute($id);

How do I print the ACTUAL query that was run into a log file.... with
the placeholders replaced???

For example, when I do the following:

print $sql;

It prints:

UPDATE Customers SET ID=?

When I need it to print:

UPDATE Customers SET ID='example'

I know that I could replace the placeholders myself... but then
again... I need to log the ACTUAL query that was just executed... or
attempted to be executed.

Any help is appreciated... and thank you in advance.

On Jun 4, 8:14 pm, PerlGoon <mburg@gmail.com> wrote:

How about this:

    my $log_sql = $sql;
    $log_sql =~ s{\?}{'$_'} for @values;
    print $log_sql;

the array @values keeps all the binding values to the placeholders,
for your case, it's

    my @values = qw($id);

you can pack this into a subroutine, i.e.

sub printSQL {
    my $sql = shift;
    print $sql and return if not @_;
    $sql =~ s{\?}{'$_'} for @_;
    print index($sql, '?') > 0 ? "need more binding values\n" : $sql;

}

(untested)

Regards,
Xicheng

PerlGoon wrote:
> The
>problem I have is that I need to be able to print the actual queries
>my code runs into a log.... with the placeholders replaced.

>For example, consider the following:

>my $id = "example";
>my $sql = "UPDATE Customers SET ID=?";
>my $query = $db->prepare($sql);
>$query->execute($id);

>How do I print the ACTUAL query that was run into a log file.... with
>the placeholders replaced???

You can replace each placeholder in the SQL statement with a value
calculated as $dbh->quote($_) for the next bind value.

You might be able to do that by overriding execute and putting the
logging in that method, though I bet it will not be easy: DBI is a very
hard module to subclass.

--
        Bart.

Thank you both for your help.

I was hoping there might be a method that I was overlooking so that I
didn't have to "redo" my queries by replacing the placeholders with
"quote($_)".

Instead I was looking for something already built into DBI that would
maybe return the last executed query... or something built into MySQL
that would log specific queries.

Again thank you for you help.

PerlGoon wrote:
>Instead I was looking for something already built into DBI that would
>maybe return the last executed query... or something built into MySQL
>that would log specific queries.

Set DBI trace?

        http://search.cpan.org/perldoc?DBI#TRACING

--
        Bart.

* Bart Lateur <bart.lat@pandora.be> wrote:

> PerlGoon wrote:

>>Instead I was looking for something already built into DBI that would
>>maybe return the last executed query... or something built into MySQL
>>that would log specific queries.

> Set DBI trace?

>    http://search.cpan.org/perldoc?DBI#TRACING

Unfortunately not.

Quote from the "Trace Flags" subsection:
"SQL - trace SQL statements executed (not yet implemented)"

--
Lars Haugseth

"If anyone disagrees with anything I say, I am quite prepared not only to
 retract it, but also to deny under oath that I ever said it." -Tom Lehrer

Lars Haugseth schreef:

> Bart Lateur:
>> PerlGoon:
>>> Instead I was looking for something already built into DBI that
>>> would maybe return the last executed query... or something built
>>> into MySQL that would log specific queries.

>> Set DBI trace?
>> http://search.cpan.org/perldoc?DBI#TRACING

> Unfortunately not.

> Quote from the "Trace Flags" subsection:
> "SQL - trace SQL statements executed (not yet implemented)"

That is about "Trace Flags".

For me, this just works: $dbh->trace(1).
See also "RaiseError".

--
Affijn, Ruud

"Gewoon is een tijger."

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc