in reply to DBI, place holders and CGI forms

Whether or not you can insert placeholders for column names is database-specific (see Placeholders and Bind Values) but will at the least compromise the speed of your queries if they are used more than once. Rather than using placeholders for column names, the easy solution would be to just bind nulls to the unused parameters and use 1 SQL statement.

If you are doing updates, so you cannot have unused fields present, I personally usually use branching in the Perl and concatenate my SQl based on local requirements. Ugly, I know, but it gets the job done.

Replies are listed 'Best First'.
Re^2: DBI, place holders and CGI forms (column names)
by tye (Sage) on Jun 02, 2011 at 17:11 UTC
    Whether or not you can insert placeholders for column names is database-specific

    Perhaps I misunderstand you. I would be quite surprised if any DBD allowed ordinary placeholders to be used to insert column names.

    For maximum clarity, assume I get a dynamic column name and desired value like so: 

    my $colname= 'city';
my $value=   'Toledo';

    [download]

    such that I want to use that to dynamically create the query: 

    select * from mytable where city = 'Toledo';

    [download]

    and I try to use a placeholder for the column name like so: 

    my $sth= $dbh->prepare("select * from mytable where ? = ?");
... $sth->execute( $colname, $value );

    [download]

    Then the query that gets run should surely be: 

    select * from mytable where 'city' = 'Toledo';

    [download]

    which should always return 0 rows since the string 'city' is never equal to the string 'Toledo'.

    But the link you provide is indeed an excellent resource for this type of question. Thanks!

    - tye        

[reply]
[d/l]
[select]
Re^2: DBI, place holders and CGI forms
by Anonymous Monk on Jun 02, 2011 at 15:53 UTC
    My db is 10G oracle. I don't understand your bind to null answer. Do you mean bind the variable for example $foo = $cgi->param('foo') || undef; and call execute with $foo, or something else?
[reply]
      Something functionally equivalent, yes. DBD::Oracle will map undef to an Oracle null. I would probably build a hash with expected field/value pairs (protection against an untrusted client), and pass that into my query routine. Any unspecified parameter would therefore be automatically mapped to undef.
[reply]
[d/l]
[select]

        but beware you cannot do "where mycol = ?" and then pass a value of undef (mapping to NULL) and expect it to work.

[reply]

In Section Seekers of Perl Wisdom