Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot

DBI query where table name is a variable

by ai56 (Initiate)
on Oct 22, 2007 at 15:34 UTC ( #646467=perlquestion: print w/replies, xml ) Need Help??
ai56 has asked for the wisdom of the Perl Monks concerning the following question:

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:
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

Replies are listed 'Best First'.
Re: DBI query where table name is a variable
by duff (Vicar) 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.

      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.

Re: DBI query where table name is a variable
by ikegami (Pope) 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`.)

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=?};
    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=?};

      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.

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

      just another cpan module author
        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' +)

        enjoying Mark Jason Dominus' Higher-Order Perl
        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):
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

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; }
    or something like it.


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.

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (7)
As of 2016-10-25 15:36 GMT
Find Nodes?
    Voting Booth?
    How many different varieties (color, size, etc) of socks do you have in your sock drawer?

    Results (323 votes). Check out past polls.