Re: DBI query where table name is a variable

Yes, you can use a variable for a table name like that. But you need to make sure that variable is a) previously defined and b) a valid SQL tablename e.g. isn't a SQL reserved word and doesn't contain invalid characters unless it's delimited with quotes or brackets or whatever your RDBMS uses.

For example this won't work:

my $table = "Robert dropped a table on his foot";
my sql = qq{SELECT * FROM $table WHERE x=?};
[download]

Whereas this might, depending on how your db delimits:

my $table = "Robert dropped a table on his foot";
my sql = qq{SELECT * FROM "$table" WHERE x=?};
[download]

Comment on Re: DBI query where table name is a variable Select or Download Code

Replies are listed 'Best First'.
Re^2: DBI query where table name is a variable by jZed (Prior) on Oct 23, 2007 at 16:02 UTC
Most DBDs only allow placeholders for values, not for structural elements like table and column names. This is especially true for any DBD that does anything useful during prepare. Think, for example about how an RDBMS could develop a query-plan during prepare if the table names were placeholders - you can't really make a query plan for a query without knowing what table it will work on. oops, this was meant as a reply to spatterson, oh well.	[reply]
Re^2: DBI query where table name is a variable by spatterson (Pilgrim) on Oct 23, 2007 at 12:07 UTC
Though you probably can also use placeholders `my $sth = $dbh->prepare("select column from ? where column = ?"); my $table = 'foo'; my $value = 'bar'; $sth->execute($table, $value);` [download] just another cpan module author	[reply] [d/l]
Re^3: DBI query where table name is a variable by Krambambuli (Curate) on Oct 23, 2007 at 12:49 UTC
Hmmmm... Take care when using placeholders as tablenames; you'll better check if that works with your DB and should avoid it even if it works if portability matters. The DBI documentation mentions that --- `With most drivers, placeholders can't be used for any element of a sta +tement that would prevent the database server from validating the sta +tement and creating a query execution plan for it. For example: "SELECT name, age FROM ?" # wrong (will probably fail +) "SELECT name, ? FROM people" # wrong (but may not 'fail' +)` [download] Krambambuli --- enjoying Mark Jason Dominus' Higher-Order Perl	[reply] [d/l]
Re^3: DBI query where table name is a variable by andreas1234567 (Vicar) on Oct 23, 2007 at 12:48 UTC
I strongly suspect one cannot use placeholders for table names due to quoting. The resulting SQL with placeholders will be syntactically incorrect: `select col1 from 'foo' where col1 = '3'` [download] A complete sample (trace output filtered for readability): Read more... (3 kB) -- Andreas	[reply] [d/l] [select]