Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic

Seeking a non-quoted DBI placeholder

by lindex (Friar)
on Jan 30, 2001 at 03:03 UTC ( #55119=perlquestion: print w/replies, xml ) Need Help??
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).

/****************************/, /*****************************/

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!


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://55119]
Approved by root
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (7)
As of 2017-01-18 17:12 GMT
Find Nodes?
    Voting Booth?
    Do you watch meteor showers?

    Results (161 votes). Check out past polls.