Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

Comment on

( #3333=superdoc: print w/replies, xml ) Need Help??
I never use quote myself.

Actually, quote() is the more general solution. I've run into quite a few different problems trying to use placeholders. And I've never noticed any performance problem with my failure to re-use prepared queries. I've actually had more problems due to people being too aggressive about trying to re-use prepared queries such as using prepare_cached() way too much.

A significant fraction (perhaps a majority) of the environments where I've done significant DBI work were such that placeholders had no performance impact at all. For example, the default with DBD::mysql seems to still be to not do server-side prepares.

At more than one recent employer I've experienced upgrading DBD::Pg such that prepares started happening server-side and then having to disable server-side prepares due to more than one resulting problem (the query planner got significantly stupider in a few important cases, it was incompatible with PgBouncer or other middle-layer tools, and other problems).

We had some breakage at PerlMonks because "limit ?" worked fine on older versions of DBD::mysql but then stopped working after some ugprade.

And I've had significant problems with placeholders when using DBD::ODBC. The first batch of problems were around certain data type checking such that "where somedate = '2011-01-01'" is no problem but "where somedate = ?" plus passing '2011-01-01' to execute() gave some "type mismatch" error.

The scariest problem was using DBD::ODBC and having "where somedate between cast(? as datetime) and cast(? as datetime)" work without emitting any errors or warnings but then returning the wrong data. But, of course:

join ' ', ... "where somedate between", $dbh->quote($start), "and", $dbh->quote($end),

worked exactly as expected (whether "cast( ... as datetime)" was included or not).

So, I like being able to use placeholders. But way too often that actually requires being able to disable server-side prepares which then just boils down to $dbh->quote() except that the DBD module is doing that call for you (and thus removes your claimed advantage).

Actually, the majority of my experience using DBI is via some wrapper (quite a few different ones, some extremely thin, some only moderately thin) that takes my column names and column values and assembles at least some of the SQL for me.

- tye        

In reply to Re^4: DBI, place holders and CGI forms (quote++) by tye
in thread DBI, place holders and CGI forms by Anonymous Monk

Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":

  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • 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:
    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
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?

    What's my password?
    Create A New User
    and all is quiet...

    How do I use this? | Other CB clients
    Other Users?
    Others examining the Monastery: (4)
    As of 2018-05-24 13:38 GMT
    Find Nodes?
      Voting Booth?