DBI binding field names as parameters

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

Hi. I hope the title of this post isn't misleading to any of you. I'm trying to collect some data for (a) variable (number of) columns, so I figured I'd first prepare the query (only once) and then pass on the relevant column inside the loop. However, this returns the actual column name, whereas I obviously want the value for this column. I can get it to work and do what I want if I prepare the query inside the loop, but this is exactly what I was trying to avoid. Could anyone explain me why the following doesn't return the value, but the column name?

 
  my $sClassScoresQuery = qq/SELECT ? FROM $exportDb->{pagesTable} WHE
+RE id=?/;
  my $stPageClassScores = $exportDb->{dbh}->prepare($sClassScoresQuery
+);
  my $newClassScore = 0;
  for my $className (keys $hrClassName2Id){
    my $classColumnName = $className . "_score";
    my $classId = $hrClassName2Id->{$className};
    my @aTemp;
    for my $id (@pageTableAutoIncrementIds){
      #prepare here... for debugging purposes only!
      #my $sQuery = qq/SELECT $classColumnName FROM $exportDb->{pagesT
+able} WHERE id = $id/;
      #my $stDebug = $exportDb->{dbh}->prepare($sQuery);
      $stPageClassScores->execute($exportDb->{dbh}->quote_identifier($
+classColumnName), $id);
      #$stDebug->execute();
      while (my @res = $stPageClassScores->fetchrow_array()){
    #while (my @res = $stDebug->fetchrow_array()){
    push @aTemp, $res[0];
      }
    }
  #some other stuff happens here...
}
[download]

So the above code returns the columnname in @res (instead of the value for this column). It works alright if I use the lines currently commented out instead. But this means preparing the statement all the time. I have tried it with a

bind_param(1, $classColumnName);
bind_param(2, $id)
[download]

, and even bind_param(1, $dbh->quote_identifier($classColumnName)) just before running execute, but that didn't help either. Like I said; could someone maybe explain me what's going on here and why this approach doesn't work? Thanks!

Comment on DBI binding field names as parameters Select or Download Code

Replies are listed 'Best First'.
Re: DBI binding field names as parameters by Anonymous Monk on Jun 03, 2014 at 13:08 UTC
Well, as is you are selecting the (column) name. I suppose return of actual value of a parametric column name would depend on the capabilities of the database (& its driver) being used. Else, you may need to evaluate the SQL much like one generates code (or a value) from a string in Perl via `eval` function.	[reply] [d/l]
Re^2: DBI binding field names as parameters by sinterklaas (Initiate) on Jun 03, 2014 at 13:23 UTC
Thanks for your quick response! Not sure if I fully understand what you mean with "you are selecting the (column)name." Good point about me forgetting to mention DB; I'm using MySQL (engine=MyISAM, not sure if that's relevant too?). I'll play around with `eval` a bit then and see if I can get it to work. And if the answer simply is that my DB(&driver) does not support this, then I guess that's the answer to my question. At least I can get it to work, be it with a less efficient solution. Thanks again for your reply!	[reply] [d/l]
Re^3: DBI binding field names as parameters by Anonymous Monk on Jun 03, 2014 at 13:33 UTC
You are essentially doing ... `select 2` [download] ... where you supply your own return value. If you are going to investigate "eval" like SQL function, just prepare the query in the loop & be done with it.	[reply] [d/l]
Re^4: DBI binding field names as parameters by sinterklaas (Initiate) on Jun 03, 2014 at 13:41 UTC
Re^5: DBI binding field names as parameters by Anonymous Monk on Jun 03, 2014 at 13:52 UTC
Re: DBI binding field names as parameters (quote_identifier) by tye (Sage) on Jun 03, 2014 at 17:32 UTC
One of the primary purposes of place-holders is to safely quote values so that it doesn't matter what data a user provides for a search or to be inserted. (The classic example is a search for "`'; drop table users`" with a DBD version that supports multiple queries separated by semicolons, which most don't.) Quoting matters. "select foo from bar" is very different from "select 'foo' from bar", quite intentionally. A secondary purpose of place-holders is to allow (for some DBD versions) one to pay only once the cost of parsing an SQL statement (and perhaps of figuring out a performant query plan) while executing similar statements but with different data. Most databases only allow constant values to be "left out" of the SQL being parsed. So, even if your DBD version had some support for a different kind of place-holder that took column names, it would likely boil down to being the same as if you had just done: `$db->prepare( "select $column from ..." );` [download] With the likely important distinction that your DBD would likely either validate that the value of $column only contained a valid identifier or would quote the identifier. How to quote an identifier varies between databases. At least DBD::Pg supports the quote_identifier() call so you'd want something more like: `$db->prepare( join( ' ', "select", $db->quote_identifier($column), + "from ..." ) );` [download] Which might be similar to (but will be safer than and more portable than) "`select [$column] from ...`" or `qq(select "$column" from ...)`. This, of course, prevents a single prepared statement from being used for more than one column. But is likely required with most databases. [And the performance benefit is very often not worth it in practice anyway. Every place I've ended up using DBD in Production we've had to disable the computing of query plans before the values have been provided because performance can be so terribly worsened by that "optimization".] - tye	[reply] [d/l] [select]