All the Perl that's Practical to Extract and Report

Navigation

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
Without JavaScript enabled, you might want to
use the classic discussion system instead. If you login, you can remember this preference.

Please Log In to Continue

At least not against a good database. (eg Oracle, PostgreSQL, etc - but MySQL would be fine.)

The problem is that there is a trade-off between time spend preparing a query and query performance. It would not be inappropriate to think of preparing a query as a "compile and optimize this SQL" step. MySQL spends very little energy preparing, and therefore its ability to handle complex queries suffers. Most other databases put a lot of energy into preparing, and so it is very important to try to avoid recomp

Personally I prefer SQL::Interp [cpan.org] (via DBIx::Simple [cpan.org] actually), which lets me write code very nearly like the quasiquoting snippet, except it’s still plain Perl and it uses placeholders and does binding under the covers. It’s a fabulous duo.

If my situation was simpler then that would look good to me as well. Unfortunately I have big chunks of SQL that are included or not based on one condition, and if included require specific parameters to be bound or not as well. Templating syntax works well for this, but standard template tools only return a string, so I really, really want to work with a string by itself, not a string plus parameters.

Using:param_name isn't portable, but it is highly effective for my job.

sql_interp will combine all those SQL snippets into a single string, duly putting in with placeholders for all the variables, and shuffling all the values into a single list according to the positions of their corresponding placeholders. (It’ll also translate that hash into a list of ANDed equality comparisons.) So despite the fact that the database sees positional placeholders, you don’t need to repeat the conditional logic from building the SQL in building the bind list, and the query is written with the Perl variables inline – precluding any bugs in lining up placeholders with bind values, no matter how complex the query composition gets.

But the first example shows why I specifically pointed out that the list you pass is a normal Perl list and so is amenable to all of the facilities Perl provides. You can factor it just in the same way you would factor any other part of the code: