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

DBI Format Question

by aphexcoil (Novice)
on Aug 03, 2010 at 17:29 UTC ( #852703=perlquestion: print w/replies, xml ) Need Help??
aphexcoil has asked for the wisdom of the Perl Monks concerning the following question:

I have the following code:
my $terms = $dbh->prepare(" SELECT date, term, appId, sum(impressi +ons), sum(clicks) FROM ? WHERE appId = ? GROUP BY HOUR(date), term, appId; + "); my $result = $terms->execute("`2010-07-22`", 3);
Question: When using a MySQL reserved word, you use grave's around the reserved word. In my case, I have tables that are named in a standard MySQL timestamp. How do I use the prepare statement properly to include the graves? Everything I have tried results in an error. Thank you.

Replies are listed 'Best First'.
Re: DBI Format Question
by jeffa (Bishop) on Aug 03, 2010 at 17:33 UTC

    You cannot use placeholders for table names like that. You'll have to instead interpolate inside the string. It's ok, though, you are not dealing with user input here.

    $dbh->prepare( "select stuff from $table");

    Having to use table names like that is a challenge too. I would look into renaming those tables and removing the dashes if that is an option and a viable one at that. Good luck! :)

    jeffa

    L-LL-L--L-LL-L--L-LL-L--
    -R--R-RR-R--R-RR-R--R-RR
    B--B--B--B--B--B--B--B--
    H---H---H---H---H---H---
    (the triplet paradiddle with high-hat)
    
      You can't use placeholders because table names must be known when the query is prepared. That leaves using a quoting function.
      my $q_table = $dbh->quote_identifier($table); $dbh->prepare("select stuff from $q_table");
Re: DBI Format Question
by Generoso (Parson) on Aug 03, 2010 at 21:07 UTC

    you can do something like this:

    my $t1 = "`animals`"; my $sql1 = "select * from ".$t1." where id = ?"; my $sth = $dbh->prepare($sql1) or die $DBI::errstr; $sth->execute(3) or die $DBI::errstr; my @ary = $sth->fetchrow_array(); $sth->finish; print @ary,"\n";

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (2)
As of 2016-09-26 23:51 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Extraterrestrials haven't visited the Earth yet because:







    Results (493 votes). Check out past polls.