in reply to Perl style/best practice question: how to [better] embed SQL in code ?

How I do it is: 

if (some_perl_code_condition)
{
  push @where, 'a.somedata5 <= ?';
  push @bind, $somedata5_max
}
# ...
$sql = $select .
  join(' AND ', @where) .
  $order;
# and use @bind for the execute statement

[download]
How I'd like to do it is abstract it away into comparison objects where stringifying it will mix in the AND and ORs, and maybe a list context for binding. But I've not stopped to think about it long enough to write any code for it :-)

Of course, I'll end up with a unique statement nearly each time through, so preparing becomes nearly useless (from a performance perspective anyway - almost may as well use do...), which may be a different concern.

Update: Added italicised pieces in last paragraph in response to graff.

  • Comment on Re: Perl style/best practice question: how to [better] embed SQL in code ?
  • Download Code

Replies are listed 'Best First'.
Re^2: Perl style/best practice question: how to [better] embed SQL in code ?
by graff (Chancellor) on Dec 17, 2008 at 06:41 UTC
    That's more or less how I usually do it too.

    Of course, I'll end up with a unique statement nearly each time through, so preparing becomes useless...

    I don't get what you mean by "useless" -- preparing is never useless. It's kind of mandatory, esp. when using placeholders, which should be "whenever possible". Even if you think you won't create the same SQL statement more than once in a given run, you can still use: 

      my $sth = $dbh->prepare_cached( $sql_statement );

    [download]
    That's quite painless, and if you happen to come up with the same statement more than once, you'll just get the same handle you got the last time you prepared that statement.
[reply]
[d/l]
      And if your code is query-heavy you could even Memoize the cache!
      --
      seek $her, $from, $everywhere if exists $true{love};
[reply]
[d/l]

In Section Seekers of Perl Wisdom