DBI query where table name is a variable

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

Replies are listed 'Best First'.
Re: DBI query where table name is a variable by duff (Parson) on Oct 22, 2007 at 15:41 UTC
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. duff	[reply] [d/l] [select]
Re^2: DBI query where table name is a variable by Fletch (Bishop) on Oct 22, 2007 at 16:56 UTC
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.	[reply] [d/l] [select]
Re: DBI query where table name is a variable by ikegami (Patriarch) on Oct 22, 2007 at 15:52 UTC
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`.)	[reply] [d/l] [select]
Re: DBI query where table name is a variable by jZed (Prior) on Oct 23, 2007 at 02:56 UTC
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=?};` [download] 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=?};` [download]	[reply] [d/l] [select]
Re^2: DBI query where table name is a variable by jZed (Prior) on Oct 23, 2007 at 16:02 UTC
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.	[reply]
Re^2: DBI query where table name is a variable by spatterson (Pilgrim) on Oct 23, 2007 at 12:07 UTC
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);` [download] just another cpan module author	[reply] [d/l]
Re^3: DBI query where table name is a variable by Krambambuli (Curate) on Oct 23, 2007 at 12:49 UTC
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' +)` [download] Krambambuli --- enjoying Mark Jason Dominus' Higher-Order Perl	[reply] [d/l]
Re^3: DBI query where table name is a variable by andreas1234567 (Vicar) on Oct 23, 2007 at 12:48 UTC
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'` [download] A complete sample (trace output filtered for readability): Read more... (3 kB) -- Andreas	[reply] [d/l] [select]
Re: DBI query where table name is a variable by andyford (Curate) on Oct 22, 2007 at 15:43 UTC
Do you need to set the value of $table before the prepare statement? non-Perl: Andy Ford	[reply]
Re: DBI query where table name is a variable by rdfield (Priest) on Oct 23, 2007 at 08:28 UTC
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; }` [download] or something like it. rdfield	[reply] [d/l]
Re: DBI query where table name is a variable by Thilosophy (Curate) on Oct 23, 2007 at 07:04 UTC
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`.	[reply] [d/l] [select]