in reply to Looking for neater solution with dynamic query

See SQL::Abstract for a perl-ish way to define things like this, and don't forget prepare_cached instead of prepare to try to reuse dynamically constructed SQL queries.

-- Randal L. Schwartz, Perl hacker
Be sure to read my standard disclaimer if this is a reply.

  • Comment on Re: Looking for neater solution with dynamic query

Replies are listed 'Best First'.
Re^2: Looking for neater solution with dynamic query
by pg (Canon) on Aug 24, 2005 at 22:12 UTC

    Cool module!

    Although the module does not resolve the binding issue, I think the database binding ability is lost any way with dynamic query. So that's fine. (Update: I was mistaken about this. See below davidrw's solution. His solution allows dynamic query to take full benefit of binding.)

    Side Note: although that module supports "binding", that's not the real binding. From database point of view, the queries prepared in this way are submited as literal, which will run slower than queries with real database binding that happens after prepare.

    With literal query, database sees it as a new query every time it is submited, so there is less optimization done. Where as non-literal queries, the optimization is done once, and used ever after, until the query is flahsed off the query pool, due to size limitation of the pool.

      A lot will depend on the way DBD for this type of database is implemented. Last time I looked at it, DBD:mysql did all the binding internally and presented a literal query string to the database-engine (the prepare-method wasn't even send to the database!), so there was no speed benefit in binding. It may have changed in more recent versions. You still had the security benefit and the cleaner code of course.

      CountZero

      "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law