Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
PerlMonks  

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

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


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

And if not placeholders and bind values, then at least quote


Comment on Re^2: Variable interpolation in a file to be read in
Re^3: Variable interpolation in a file to be read in
by afoken (Parson) on Oct 06, 2011 at 18:12 UTC

    quote() is evil. Well, not exactly, but using quote() manually causes more trouble than it is worth. Always use placeholders and you never again have to think about quoting values for SQL statements. Plus, DBI, DBD or database can cache prepared statements for better application performance.

    Alexander

    --
    Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
      Always use placeholders...
      Beware of absolutes. Placeholders are not always the way to go.
        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.

        Alexander

        --
        Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (4)
As of 2014-09-22 01:37 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (176 votes), past polls