Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things

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
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! :)


    (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?

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

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (8)
As of 2015-11-29 16:27 GMT
Find Nodes?
    Voting Booth?

    What would be the most significant thing to happen if a rope (or wire) tied the Earth and the Moon together?

    Results (751 votes), past polls