I am strongly against runtime SQL generation. It is my opinion that the mapping between a collection of program conditions and the resultant SQL queries represents a finite and describable space.

one form of dynamic sql generation: if-then-if-then-if-then ad infinitum

In other words, I want to remove things like this

use CGI; use DBI; my $cgi = ...; my $dbh = ...;

my $sql = "SELECT ";

push @field, 'age'    if (my $age = $cgi->param('age'));
push @field, 'gender' if (my $age = $cgi->param('gender));

if ($cgi->param('weight_constraint')) {
   push @where, sprintf 'weight %s', $cgi->param('weight_constaint');
}

my $sql = sprintf "SELECT %s FROM user WHERE %s;",
   join ',' @field, join ' and ', @where;

$dbh->selectall_arrayref($sql);
[download]

and also...

Modules supporting dynamic sql generation

I also don't think that a more structured approach to dynamic SQL generation such as is represented in DBIx::Recordset, DBIx::Abstract, DBIx::SQLEngine, DBIx::SearchBuilder, et al makes things any better because there are...

Issues with runtime SQL generation

1. time. the time consumed breaks down into these types
   1. to execute the conditionals. This is the same for both.
   2. to build data structures which then get joined, sprintf'ed and otherwise folded into a final string
   3. if the module is sophisticated, it will also spending time caching the resulting query to avoid creating it again. It will also flexibly cache it either to disk or memory using something like Cache::Cache. The advantage of the static approach (exemplified by SQL::Catalog) is that the caching happens to either place before the application starts
2. tuning it is possibly advisable to have a database expert create well-tuned static (static meaning, the only variability is in placeholders) queries as opposed to more general on-the-fly general queries.
3. task divisionagain, allowing the database expert to code up the queries satisfying a specification on his own while the Perl programmer accesses then purely by label, as exemplified in SQL::Catalog provides the independance that large-scale applications need.

The only thing lacking from the static approach

Is some sort of structure which maps a collection of cases to a collection of SQL labels. I am thinking about Switch but also about decision trees.

Can anyone provide any pointers to a good means of such a mapping? Thanks.

---

---

• Are you posting in the right place? Check out Where do I post X? to know for sure.
• Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:

  <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>

• Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
• Want more info? How to link or How to display code and escape characters are good places to start.