DBI passing tablename

RobertCraven has asked for the wisdom of the Perl Monks concerning the following question:

Dear Masters,

I am failing to truncate (or populate later on) a table in a MySQL database using DBI.

my $table = 'modeltargetsHet';
my $sth = $dbh->prepare(q{
        truncate ?; 
         }) or die $dbh->errstr;
$sth->execute($table) or die $dbh->errstr;
[download]

produces this error:

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''modeltargetsHet'' at line 1 at identifyModeltargets.pl line 204.

How do I have to pass the table name correctly?
Manual executing the truncate command works fine.

Many thanks,
Robert

Comment on DBI passing tablename Download Code

Replies are listed 'Best First'.
Re: DBI passing tablename by Anonymous Monk on Jun 11, 2009 at 01:05 UTC
My guess is you can't use placeholders for table names in truncate statement... turn up trace to maximum, maybe you'll get a better error message	[reply]
Re: DBI passing tablename by afoken (Chancellor) on Jun 11, 2009 at 11:12 UTC
The Anonymous Monk is right. You can use placeholders only for values, not for names. Use string interpolation here (and only here, NEVER interpolate values into SQL commands). Of course, you could write a stored procedure which interpolates its argument into a truncate command and executes that command ... ;-) Alexander -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply]
Re^2: DBI passing tablename by RobertCraven (Sexton) on Jun 11, 2009 at 14:59 UTC
Thanks for your help, viele Gruesse nach Deutschland! ;-)	[reply]