Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

Mysql - prepare

by jeanluca (Deacon)
on Mar 15, 2006 at 11:17 UTC ( #536807=perlquestion: print w/replies, xml ) Need Help??

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

Replies are listed 'Best First'.
Re: Mysql - prepare
by marto (Cardinal) on Mar 15, 2006 at 11:34 UTC

    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

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.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://536807]
Approved by Corion
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (3)
As of 2022-11-28 19:53 GMT
Find Nodes?
    Voting Booth?