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

Anyone know if theres a way to get DBI to not quote a specific placeholder on a statement, such as for whatever reason (maybe not a good one) someone wanted to process the sql statment "update foo set ?=? where foo_id=?" and pass the column name as a placeholder with the DBI::do method, and not have it quote the first '?' (as far as I know most databases dont like column names on update statments quoted).


lindex 
/****************************/
  jason@gost.net, wh@ckz.org
    http://jason.gost.net
/*****************************/

[download]

Replies are listed 'Best First'.
Re: Seeking a non-quoted DBI placeholder
by runrig (Abbot) on Jan 30, 2001 at 03:12 UTC
    No, you cannot, in general (nor anywhere that I know of) use placeholders for column names in a sql statement. It is a good idea to use placeholders everywhere that you can, though. Here's one idea for a variable number of columns:
    
my %columns = (field1=>"value1", field2=>"value2");

my $sql_stmt = "update my_table ".column_list(\%columns).
 " where id_field = ?";

my $sth = $dbh->prepare($sql_stmt);
$sth->execute(values %columns, $id_field);

sub column_list {
 join(" ", map {"$_=?"} keys %{$_[0]});
}

    [download]
    Of course, arturo's answer below is perfectly good also for substituting one or any constant number of column names :-)
[reply]
[d/l]
Re: Seeking a non-quoted DBI placeholder
by arturo (Vicar) on Jan 30, 2001 at 03:30 UTC

    No, but you can always achieve the effect if you build the SQL query up as a string, e.g. 

    my $foo = 'bar';

my $sql = qq{SELECT * FROM table_name where $foo = ?};
my $sth = $dbh->prepare($sql);
#...
$sth->execute($foo);

    [download]

    Nice side effect: you have the SQL statement handy for debugging.

    Philosophy can be made out of anything. Or less -- Jerry A. Fodor

[reply]
[d/l]
Re: Seeking a non-quoted DBI placeholder
by salvadors (Pilgrim) on Jan 30, 2001 at 21:08 UTC

    Anyone know if theres a way to get DBI to not quote a specific placeholder on a statement

    Ignoring the flaws that others have already pointed out with your example, it's also worth remembering that DBI will not quote numbers.

    This can cause problems when you have a CHAR column to which (sometimes) wish insert numbers. e.g if you had an ISBN column in a book table, the query: 

      my $find_book = $dbh->prepare(qq{
     SELECT title FROM book WHERE isbn = ?
  });

    [download]
    would be fine when passed 
     my $isbn = "156884915X";
 my $title = $dbh->selectrow_array($find_book, undef, $isbn);

    [download]
    But when passed 
     my $isbn = 1565922433;
 my $title = $dbh->selectrow_array($find_book, undef, $isbn);

    [download]
    it won't quote the ISBN. On some databases (definitely on MySQL) this will have the effect of not using the index on this column, massively slowing down this query - particularly if you have the 1.2 million books in print in this table!

    Tony

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom