Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Is there a way to display the statement generated by DBD on execute()

by Cody Fendant (Pilgrim)
on May 05, 2013 at 00:05 UTC ( #1032094=perlquestion: print w/ replies, xml ) Need Help??
Cody Fendant has asked for the wisdom of the Perl Monks concerning the following question:

I'm trying to figure out an SQL problem I'm having and I would like to see what's going on when I do

$sth->execute($foo,$bar);

Because what I think is in there works fine when I do it manually...

The full problem is, I need to find Related Content. Say I have a blog post with id 99. I find one Related story by one method: select from blog where id <> ?. So far so good. But then I need to find another by another method and I don't want to find the same one twice.

So I changed the select to select from blog where id not in(?), that way I can find the first one, then make sure I get a different post for the second one by joining the id of the original post and the first Related post.

So the first time it should do select from blog where id not in(99) then I find Related post 88 and the second time around it should do select from blog where id not in(99,88).

The second run isn't working as expected and I'm getting post 88 again.

Comment on Is there a way to display the statement generated by DBD on execute()
Select or Download Code
Re: Is there a way to display the statement generated by DBD on execute()
by NetWallah (Abbot) on May 05, 2013 at 04:21 UTC
    If you have a prepare with
    select from blog where id not in(?)
    then you can pass in only ONE parameter, not two. You are apparently trying to pass in (99,88).

    We can help you more if you show some code.

                 "I'm fairly sure if they took porn off the Internet, there'd only be one website left, and it'd be called 'Bring Back the Porn!'"
            -- Dr. Cox, Scrubs

Re: Is there a way to display the statement generated by DBD on execute()
by rpnoble419 (Pilgrim) on May 05, 2013 at 04:24 UTC

    I would take a hard look at your logic on this. Your method will not scale. For example what do you do once you get to 20 ids or 50 ids?

Re: Is there a way to display the statement generated by DBD on execute()
by moritz (Cardinal) on May 05, 2013 at 06:09 UTC

    $sth->execute (usually) doesn't create any SQL at all. Most DBD modules pass the string as-is to the database engine, which often directly support prepared statements.

    That said, DBI supports tracing, which allows you to find out what happens between DBI/DBD and the database. See the section TRACING in the DBI docs.

    Finally a possible way to create your SQL is this:

    my @exclude_ids = (88, 99); my $sql = 'SELECT * FROM blog WHERE ID NOT IN (' . join(', ', ('?') x +@exclude_ids) . ')'; my $sth = $dbh->prepare($sql); $sth->execute(@exclude_ids);
Re: Is there a way to display the statement generated by DBD on execute()
by FloydATC (Hermit) on May 05, 2013 at 08:59 UTC
    To answer your question: $sth->statement will show you the statement exactly as it was prepared (with placeholders). Unfortunately, this won't help you much in this case, as has already been pointed out.

    If you must use a prepared statement to pass your list, you could consider using the MySQL FIND_IN_SET() function, which accepts the list join()'ed into a single string.

    Another possible approach might be to use an SQL sub query to build the list, that way you never have to pass it in the first place:

    SELECT foo FROM bar WHERE id IN ( SELECT id FROM baz WHERE a = b )

    Finally, there are alternatives to using prepared statements, but you'll have to guard against injection attacks in some other way. In the case of a list of integer IDs this is ofcourse trivial.

    my @integers = map { int } @list; my $sth->prepare(" SELECT foo FROM bar WHERE id IN (".join(',',@integers).") ");

    -- Time flies when you don't know what you're doing

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (8)
As of 2014-08-02 00:47 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Who would be the most fun to work for?















    Results (53 votes), past polls