DBI query where table name is a variable

[ai56]

Hi Monks, I have a dbi statement handle:

my $confidence_query = $dbh->prepare("select confidence_str from $tabl
+e  where probeset_id = ?");

When I set the value of $table and execute the query:

$confidence_query->execute('SNP_1');

I get an error:

DBD::mysql::st execute failed: You have an error in your SQL syntax; c
+heck the manual that corresponds to your MySQL server version for the
+ right syntax to use near 'where probeset_id='SNP_1')

Are you allowed to have a table name as a variable? Is there a way of working around this? Thanks for your help. update: ah, yes. I needed to set table prior to the prepare statement.

20071026 Janitored by Corion: Changed PRE to code tags, as per Writeup Formatting Tips

[duff]

Did you set $table before you called $dbh->prepare? Are you sure you set it correctly? There's a way to get DBI to tell you exactly what it sent to the SQL processor but I forget what it is. Perhaps another kind monk can enlighten you so you can see what the SQL processor sees.

[Fletch]

Usually if you call $sth->trace( 2 ) (alternately call trace on your $dbh to enable tracing for all statement handles subsequently created) you should get a glimpse at the SQL that's being sent to the backend.

[ikegami]

Are you allowed to have a table name as a variable?

DBI and SQL never see the variable name.
"select confidence_str from $table where probeset_id = ?"
is exactly equivalent to
"select confidence_str from " . $table . " where probeset_id = ?"
Either table isn't set at that point, or it isn't set to a properly escaped table name.

What's the value of $table? Is there a space in it? If so, you'll have to properly bracket it according to your database's version of SQL. (MySQL would expect SELECT * FROM `Table Name`.)

[jZed]

Yes, you can use a variable for a table name like that. But you need to make sure that variable is a) previously defined and b) a valid SQL tablename e.g. isn't a SQL reserved word and doesn't contain invalid characters unless it's delimited with quotes or brackets or whatever your RDBMS uses.

For example this won't work:

my $table = "Robert dropped a table on his foot";
my sql = qq{SELECT * FROM $table WHERE x=?};

Whereas this might, depending on how your db delimits:

my $table = "Robert dropped a table on his foot";
my sql = qq{SELECT * FROM "$table" WHERE x=?};

[jZed]

Most DBDs only allow placeholders for values, not for structural elements like table and column names. This is especially true for any DBD that does anything useful during prepare. Think, for example about how an RDBMS could develop a query-plan during prepare if the table names were placeholders - you can't really make a query plan for a query without knowing what table it will work on.

oops, this was meant as a reply to spatterson, oh well.

[spatterson]

Though you probably can also use placeholders

my $sth = $dbh->prepare("select column from ? where column = ?");

my $table = 'foo';
my $value = 'bar';
$sth->execute($table, $value);

[Krambambuli]

Hmmmm... Take care when using placeholders as tablenames; you'll better check if that works with your DB and should avoid it even if it works if portability matters.

The DBI documentation mentions that
---


With most drivers, placeholders can't be used for any element of a sta
+tement that would prevent the database server from validating the sta
+tement 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'
+)

[andreas1234567]

I strongly suspect one cannot use placeholders for table names due to quoting. The resulting SQL with placeholders will be syntactically incorrect:

select col1 from 'foo' where col1 = '3'

A complete sample (trace output filtered for readability):
Read more... (3 kB)

[andyford]

Do you need to set the value of $table before the prepare statement?

[rdfield]

I would set the query up in a scalar first, then pass it to DBI:

my $sql = "select confidence_str from $table where probeset_id = ?";
if (!$dbh->prepare($sql)) {
   warn "error parsing SQL: $sql\n". $dbh->errstr;
   return;
}
if (!$dbh->execute($param)) {
   warn "error executing SQL: $sql\nparam $param\n". $dbh->errstr;
   return;
}

or something like it.

[Thilosophy]

You need to set $table before you call $dbh->prepare.

At that point, the table name gets expanded into the SQL string, and it will not change, even if you re-assign to $table.