mySQL non-string bind variable

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

I have a mySQL/perl CGI program, which at one point needs to loop through different table names and process data from each one in turn. The way I have it set up now is

$sth = $dbh->prepare("SELECT `name`, `number` FROM ?");
for ($i = 0; $i < 5; ++$i) {
    $sth->execute($tableNames[$i]);
    ($name, $number) = $sth->fetchrow_array();
    process_data();
    #etc.
}
[download]

But that interprets the bind variable as a string, which is of course incorrect. I could just prepare the SQL multiple times for each table, but I was wondering if there was a way to tell SQL what type of data you are passing.

Comment on mySQL non-string bind variable Download Code

Replies are listed 'Best First'.
Re: mySQL non-string bind variable by wfsp (Abbot) on Oct 12, 2006 at 07:55 UTC
From the docs: 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)` [download] So I don't think what you are trying to do will work. Perhaps just building the string using your array values?	[reply] [d/l]
Re: mySQL non-string bind variable by Koosemose (Pilgrim) on Oct 12, 2006 at 06:40 UTC
I think you're looking for bind_param, it goes something like this: `use DBI qw(:sql_types); $sth->bind_param( 1, $number, SQL_INTEGER );` [download] That would bind the first return column return from the statement to $number as an SQL_INTEGER. It should be noted that you need to make sure to import :sql_types when you use DBI, if you need further details check the DBI docs and look for bind_param (sorry, can't for the life of me remember how to link to the perldocs). Just Another Perl Alchemist	[reply] [d/l]
Re: mySQL non-string bind variable by graff (Chancellor) on Oct 12, 2006 at 12:20 UTC
I could just prepare the SQL multiple times for each table... I think what you mean is you could prepare a distinct SQL statement for each distinct table. As wfsp points out, that is the way you must do it, because DBI won't support using a placeholder where the table name is supposed to go.	[reply]
Re: mySQL non-string bind variable by laceytech (Pilgrim) on Oct 12, 2006 at 07:32 UTC
I would assign the results to an array. Then process the array. You will need to add a loop if you want to process more than one row of data. Read here for more info on fetchrow_array. Jim	[reply]