my ($sid, $age) = $dbh->selectrow_array(<<'SQL', undef, $uid);
SELECT SessionID,
       datediff(minute, TimeStamp, getdate())
  FROM Sessions
 WHERE UserID = ?
    AT ISOLATION READ UNCOMMITTED
SQL


my %data =
   ( Column1 => $value1,
     Column2 => $value2,
     # ...
     ColumnN => $valueN,
   );

my $rv = $dbh->do(<<"SQL", undef, values %data);
INSERT
  INTO TableName (@{[ join ', '  keys %data ]})
VALUES ( @{[join ', ', map {'?'} keys %data ]})
SQL
[download]

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]

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.

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.

And if your code is query-heavy you could even Memoize the cache!
--
seek $her, $from, $everywhere if exists $true{love};

SQL::Catalog, SQL::Library, or especially Data::Phrasebook can help readability. Sometimes extracting SQL from the code is an improvement.

Class::DBI had a neat way of modularizing SQL... does DBIx::Class have something similar?

here is how Class::DBI did it... I dont see anything similar for DBIx::Class

I might try it like this, which will look nice in Emacs cperl-mode:

my $clause1 = '';
if ($some_condition) {
    $clause1 = <<'END_SQL';
    AND a.somedata5 <= ?
    AND [...]
END_SQL

my $clause2 = '';
if ($some_condition) {
    $clause2 = <<'END_SQL';
    AND b.somedata6 >= ?
    [...] 
END_SQL

my $order_by = '';
if ($some_condition) {
    $order_by = <<'END_SQL';
    ORDER BY somedata7
END_SQL

my $sql = <<END_SQL;
    SELECT a.somedata1,
           a.somedata2,
           a.somedata3,
           [...]
    FROM table1 a,
         table2 b,
         table3 c
    WHERE a.somedata4 > ?
$clause1
$clause2
$order_by
END_SQL
[download]

There are obvious constraints on what you can do with embedded SQL to make it readable. I personally would format the lines above as

my $sql = qq{ SELECT a.somedata1,       }
        . qq{        a.somedata2,       }
        . qq{        a.somedata3,       }
        . qq{        [...]              }
        . qq{ FROM table1 a,            }
        . qq{      table2 b,            }
        . qq{      table3 c             }
        . qq{ WHERE a.somedata4 > ?     }
        ;

$sql .=   qq{   AND a.somedata5 <= ?    }
        . qq{   AND [...]               }
        if some_perl_code_condition1;

$sql .=   qq{   AND b.somedata6 >= ?    }
        . qq{   [...]                   }
        if some_perl_code_condition2;

$sql .=   qq{ ORDER BY somedata7        };
[download]

It removes a lot of the white space and all the new lines, but I'm pretty sure that my floating ; is not PBP. I'll play with field widths depending on case, but always put starting period and the closing brace at tab stops. One nice thing about this format is that mixing and matching is generally easy and safe, and changing width tends to be very fast.

Sorry, but I think that formatting is horrible. Just horrible. Why? Because who said you have to remove white space and new lines? The idea is to not make the output pretty for the SQL engine, but to make changes to your code painless, simple and efficient. And having to concatenate like that is none of the above for me. No, I would not change the original formatting at all. I cannot possible see how leading periods and closing brace at the tab stops beats ... nothing!

my $sql = qq/
    SELECT a.somedata1,
        a.somedata2,
        a.somedata3,
        [...]
    FROM table1 a,
        table2 b,
        table3 c
    WHERE a.somedata4 > ?
/;
[download]

See? No periods. No braces. No nothing. Just SQL.

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)

The OP specifically requested removing new lines and white space for testing and logging. The format is busy, but it also sets the SQL clearly apart from the surrounding Perl.