- 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". ;-)
Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
Read Where should I post X? if you're not absolutely sure you're posting in the right place.
Please read these before you post! —
Posts may use any of the Perl Monks Approved HTML tags:
You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
- a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
Link using PerlMonks shortcuts! What shortcuts can I use for linking?
See Writeup Formatting Tips and other pages linked from there for more info.
| & || & |
| < || < |
| > || > |
| [ || [ |
| ] || ] ||