DBI Format Question

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);
[download]

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");` [download] 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)	[reply] [d/l] [select]
Re^2: DBI Format Question by ikegami (Patriarch) on Aug 03, 2010 at 18:50 UTC
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");` [download]	[reply] [d/l]
Re: DBI Format Question by Generoso (Prior) 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"; [download]	[reply] [d/l]