Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number

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


    (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 romping around the Monastery: (8)
As of 2016-10-25 08:08 GMT
Find Nodes?
    Voting Booth?
    How many different varieties (color, size, etc) of socks do you have in your sock drawer?

    Results (315 votes). Check out past polls.