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

Re^4: Variable interpolation in a file to be read in

by runrig (Abbot)
on Oct 06, 2011 at 18:33 UTC ( #930043=note: print w/replies, xml ) Need Help??

in reply to Re^3: Variable interpolation in a file to be read in
in thread Variable interpolation in a file to be read in

Always use placeholders...
Beware of absolutes. Placeholders are not always the way to go.
  • Comment on Re^4: Variable interpolation in a file to be read in

Replies are listed 'Best First'.
Re^5: Variable interpolation in a file to be read in
by afoken (Monsignor) on Oct 06, 2011 at 20:21 UTC
    Re^4: DBI, place holders and CGI forms (quote++)
    Problem 1: "limit ?" with DBD::mysql. Seems to be a bug introduced in some version of DBD::mysql. Maybe the placeholder support for limit was an accidental feature removed by a "bugfix", but as this "bugfix" breaks existing code, it is a bug, not a bugfix. Maybe it is related to changed placeholder handling code in 4.014 / 4.015, and maybe it could be fixed using the mysql_bind_comment_placeholders attribute. Or it was broken in 3.0008_1, when the LIMIT parsing was changed. I don't know, I don't like MySQL, and so I don't use it.
    Problem 2: Datetime literals with DBD::ODBC. Seems to be caused by using an incompatible datetime format in the application, according to the reply by mje, the maintainer of DBD::ODBC.
    Where the advice to use DBI bind parameters can go wrong (long)
    Problem 3: "col like ?" instead of "col like 'prefix%'" forces Oracle's query optimizer into a full table scan, because it can no longer see the special case that only col values starting with prefix are relevant and thus an index on col can be used. When I learned to use Oracle, I was told to avoid like when substr can be used. I have no Oracle instance available, but I think the query optimizer should be able to use the index with "substr(col,1,length(:p1))=:p1".
    Can a DBI Placeholder accept multiple values?
    Problem 4: "col in (list)". Yeah. SQL sucks here. This is not a DBI problem. You have to construct a different SQL statement for each different list length. And in parallel, you can/have to construct a list of parameters to be passed to $sth->bind_param() or $sth->execute(). Or, you use a wrapper to do so: igelkott's reply proposes to use the special "??" placeholder of DBIx::Simple.

    I still see no reason to use quote() in all but the first case. And even this "limit" problem would better be fixed by reconfiguring or reparing DBD::mysql, or by downgrading it to the latest "known good" version.


    Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
      I've run into ODBC drivers that did not cope very well with placeholders and executing statements multiple times, substr() doesn't fix everything when you do sometimes want wildcards at the beginning of a string, and if I might have wildly varying numbers of "?" in "IN (?,?,?)" type clauses, I usually just go with quote().

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://930043]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (4)
As of 2017-03-01 20:49 GMT
Find Nodes?
    Voting Booth?
    Before electricity was invented, what was the Electric Eel called?

    Results (429 votes). Check out past polls.