http://qs1969.pair.com?node_id=55119

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 /*****************************/

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]}); }
    Of course, arturo's answer below is perfectly good also for substituting one or any constant number of column names :-)
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);

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

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

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 = ? });
    would be fine when passed
    my $isbn = "156884915X"; my $title = $dbh->selectrow_array($find_book, undef, $isbn);
    But when passed
    my $isbn = 1565922433; my $title = $dbh->selectrow_array($find_book, undef, $isbn);
    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