Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

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).


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

Comment on Seeking a non-quoted DBI placeholder
Download Code
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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://55119]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (9)
As of 2015-07-02 22:34 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (46 votes), past polls