Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask

syntax error when using bind_param

by davidj (Priest)
on Apr 14, 2005 at 20:10 UTC ( #447949=perlquestion: print w/replies, xml ) Need Help??

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

Fellow monks,
I am totally stumped on this one. When I try to use bind_param in the following code, I get a syntax error.

my @tables = $dbh->tables(); $qry = qq{SELECT * FROM ?}; $sth = $dbh->prepare( $qry ); foreach $table ( @tables ) { $sth->bind_param( 1, $table ); $sth->execute(); $row = $sth->fetchrow_hashref(); ......
They syntax error I get is:
DBD::mysql::st execute failed: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near ''user_info'' at line 1....

Now, if I change
$qry = qq{SELECT * FROM ?}
$qry = "SELECT * FROM user_info";

and remove the bind_param statement, everything works fine.

I even tried changing the bind_param statement from
$sth->bind_param( 1, $table );
$sth->bind_param( 1, "user_info" );

and I get the same syntax error as above.

The query is valid. It works when bind_param is not used and fails only when bind_param is used.

What am I overlooking?

as always, your input is greatly appreciated

Replies are listed 'Best First'.
Re: syntax error when using bind_param
by cowboy (Friar) on Apr 14, 2005 at 20:14 UTC
    I don't believe you can use a placeholder for the table name, only for values.
      You may be correct on that. However, my second attempt at using bind_param does use a literal and it still fails.

        In the second example, you're using a literal as the value, but it's still attempting to use a placeholder (the ?) for the table name, which is what can't be done. Unfortunately, you have to construct or query with the table name:
        $qry = "SELECT * FROM $table";
        May or may not help in your case, but a very handy module is SQL::Abstract.
Re: syntax error when using bind_param
by davidj (Priest) on Apr 14, 2005 at 20:45 UTC
    Thanks guys,
    I just found the same answer in the Cheetah book. I guess I should have read it a little more closely :)


Log In?

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

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (6)
As of 2020-09-30 10:36 GMT
Find Nodes?
    Voting Booth?
    If at first I donít succeed, I Ö

    Results (160 votes). Check out past polls.