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" ; }
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

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
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");
    "We all agree on the necessity of compromise. We just can't agree on when it's necessary to compromise." - Larry Wall.
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.