Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
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.

Comment on DBI Format Question
Download Code
Re: DBI Format Question
by jeffa (Chancellor) 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 (Vicar) 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 drinking their drinks and smoking their pipes about the Monastery: (4)
As of 2014-08-23 20:26 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (178 votes), past polls