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

I understand how to use placeholders. But how do you use the column name as a variable? Example:

my $sth = $dbh->prepare('SELECT DISTINCT email FROM contacts WHERE gue
+st = ? ORDER BY email'); 
     $sth->execute($value) or die $sth->errstr;

[download]
This works fine.

But how can you pass a variable into the first part of the WHERE clause? Example:

my $sth = $dbh->prepare('SELECT DISTINCT email FROM contacts WHERE ? =
+ ? ORDER BY email'); 
     $sth->execute($value1,$value2) or die $sth->errstr;

[download]

This doesn't work. I want to pass a variable into the statement that chooses one of the many column names I have in the DB. (MySQL).

I've tried every variation I can and searched through all the docs I have and FAQ's and can't find any reference to this. Is it possible?

peppiv

Look at me! I'm flinging!

Replies are listed 'Best First'.
Re: Passing a value into a WHERE clause
by VSarkiss (Monsignor) on Jan 26, 2002 at 01:55 UTC

    You can't do that, nor would there be any benefit to it. If the SQL optimizer doesn't know what column you'll be qualifying in your where clause, it can't plan your query. You can use placeholders for the values you bind to columns, not the column names themselves.

    If you could do that, by extension you'd end up with extreme examples like: 

    $sth = $dbh->prepare(qq(
    select ? from ? where ? = ? group by ? order by ?));

    [download]
    There's no point in trying to prepare a query like that: the optimizer doesn't know anything about what you're trying to do.

    HTH

[reply]
[d/l]
[select]
Re: Passing a value into a WHERE clause
by Fletch (Bishop) on Jan 26, 2002 at 01:59 UTC

    For many databases placeholders aren't just a textual substitution. It needs a real column name there in order to compile it to whatever internal representation the handle has. You really need to consult the specific DBD::Foo and database documentation to tell what's alowable as a placeholder and what's not. What you might could do is to prepare a handle for each column you plan on using: 

    my %sth;
foreach( qw( guest name shoe_size ) ) {
  $sth{$_} = $dbh->prepare( qq{
SELECT distinct email from contacts where $_ = ? order by email
}) or warn "Error preparing for $_: " . $dbh->errstr . "\n";
}

    [download]

    You could even build the handles dynamically on demand as they're needed. 

    $sth{$column} ||= $dbh->prepare( make_select( $column ) );

    [download]
[reply]
[d/l]
[select]
Re: Passing a value into a WHERE clause
by dws (Chancellor) on Jan 26, 2002 at 03:21 UTC
    But how can you pass a variable into the first part of the WHERE clause?

    Your example very nearly gets you there. The trick is to supply the field name before the DBMS sees the query. Easy enough to do. All you need is Perl's standard variable interpolation. Try something like: 

      my $field = 'answer';
  my $value = 42;
  my $str = dbh->prepare("SELECT * FROM t WHERE $field = ?");
  $sth->execute($value);
  ...

    [download]
[reply]
[d/l]
      Unfortunately I have tried this approach to no avail. I have tried it with almost every variation of syntax I can think of. It seems that no matter what I do, if I put a string ($field) in the first part of the WHERE clause it returns 'unknown column' even though it's defined before the DBMS sees the query.
[reply]
Re: Passing a value into a WHERE clause
by nandeya (Monk) on Jan 26, 2002 at 04:53 UTC
    Folks above in thread already mentioned how to go about doing do what you are looking to do - by throwing a variable into the WHERE clause for the dynamic changing of the sql statement to the left of the '=' sign, and then your '?' to the right of the '=' sign would stay the same.

    Would add on to this discussion by commenting that the reason that you can use the '?' to the right of the '=' is that you are using the '?' as a placeholder to bind the value if you are going to use the statement multiple times in your script for example. Most newer database products support this and by doing it you don't have to incur the overhead of executing the execution plan each time the statement is run.

    Which brings me to a question which interests me which your point gets me wondering about...

    In a way it seems as though using a variable in the creation a sql-DBI prepare statement such as in the example in this thread is an oxymoron. Can you get away with not incuring the overhead of re-executing the execution plan each time even though you are changing your actual sql statement via a variable which alters the statement itself?

    I looked into the Perldoc DBI module documentation documentation and around a bit and couldn't find the answer to the question.

    Does anyone know the answer to this? peppiv's initial question got me wondering (nothing like posting a question back to a question)...

    nandeya
[reply]
      In a way it seems as though using a variable in the creation a sql-DBI prepare statement such as in the example in this thread is an oxymoron. Can you get away with not incuring the overhead of re-executing the execution plan each time even though you are changing your actual sql statement via a variable which alters the statement itself?

      No. When you change the statement, you need to re-prepare, and hence replace.

      What you can do is cache statement handles based on what field name you've decided to plug in to the query. In theory, you then bite off the overhead of preparing (and query planning) only once per statement.

[reply]

Back to Seekers of Perl Wisdom