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

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

by afoken (Chancellor)
on Oct 06, 2011 at 20:21 UTC ( #930061=note: print w/replies, xml ) Need Help??

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

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

Replies are listed 'Best First'.
Re^6: Variable interpolation in a file to be read in
by runrig (Abbot) on Oct 06, 2011 at 22:02 UTC
    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
Domain Nodelet?
Node Status?
node history
Node Type: note [id://930061]
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others exploiting the Monastery: (3)
As of 2023-12-10 10:30 GMT
Find Nodes?
    Voting Booth?
    What's your preferred 'use VERSION' for new CPAN modules in 2023?

    Results (39 votes). Check out past polls.