Mysql - prepare

[jeanluca]

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

[marto]

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

[jbrugger]

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

[kwaping]

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.