Mysql - prepare

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

Dear Monks,

Well, lets start with a snippet of code

$sth = $dbl->prepare("SELECT val FROM ? where col=?") ;
if (! $sth->execute( "my_table_name", "bla") ) {
    print "FAILED\n" ;
}
[download]

This fails, Mysql tells me that the execution failed due to some errors inside the mysql statement.
Is it not possible to use a ? for a table name, or is it something else ?

Thanks a lot
Luca

Comment on Mysql - prepare Download Code

Replies are listed 'Best First'.
Re: Mysql - prepare by marto (Cardinal) on Mar 15, 2006 at 11:34 UTC
jeanluca, If you read the DBI documentation under Placeholders and Bind Values you will find: With most drivers, placeholders can't be used for any element of a statement that would prevent the database server from validating the statement 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') If in doubt check out the DBI documentation, and the documentation for the driver you are using. Hope this helps Martin	[reply]
Re: Mysql - prepare by jbrugger (Parson) on Mar 15, 2006 at 12:26 UTC
If you really want to do that, why not use: `my $table = "my_table_name"; my $sth = $dbl->prepare("SELECT val FROM ". $table ." where col=?") ; $sth->execute( "bla");` [download] "We all agree on the necessity of compromise. We just can't agree on when it's necessary to compromise." - Larry Wall.	[reply] [d/l]
Re: Mysql - prepare by kwaping (Priest) on Mar 15, 2006 at 15:45 UTC
Is it not possible to use a ? for a table name Bingo. The reason is the bound value, being a character string, will be inserted into the sql as `'table_name'`, enclosed in single quotes. As you can see, `select * from 'table' ...` isn't going to do what you want. --- It's all fine and dandy until someone has to look at the code.	[reply] [d/l] [select]