Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

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

Comment on DBI query where table name is a variable
Select or Download Code
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 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 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=?};

      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
        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):
        --
        Andreas
        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' +)

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

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.

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.

    rdfield

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (9)
As of 2014-12-21 11:46 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (104 votes), past polls