Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses

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
[marioroy]: Thank you Lady_Aleena. I've bookmarked the page.
[1nickt]: folks, would you say that 1.0 is an integer ?
[Lady_Aleena]: 1nickt, I wouldn't, but I'm not normal.
LanX aggrees, LA isn't normal ;-P
[Lady_Aleena]: LanX, thanks bunches.
[Lady_Aleena]: Rigth now I'm ranting in my head about an old subject.
[1nickt]: Hm, that is, how to prevent Perl from turning 1.0 into 1? I feel sure this must be an faq, but am reading perlnum and not finding the answer ...
[Lady_Aleena]: 1nickt, why does it matter in this case?
[1nickt]: Hm, perhaps this? "Operators which expect an integer force the argument into the integer format."

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (11)
As of 2017-05-24 18:31 GMT
Find Nodes?
    Voting Booth?