for my $cd (@cds) {
    print  $cd->name, $/;
    printf " - %d: %s\n", $_->num, $_->song->name
        for sort { $a->num <=> $b->num } $cd->tracks;
}
[download]

OK, now assuming you really do want to abstract the SQL-generation process yourself, to do the whole generate-SQL/prepare/execute/fetch process, then here are some good starting points for common queries. Personally, I prefer using sprintf for SQL generation:

my $select = sprintf "select %s from $table where %s",
    join(","     => @cols),
    join(" and " => map { "$_=?" } keys %constraints);

my $sth = $dbh->prepare($select);
   $sth->execute( values %constraints );

#########

my $insert = sprintf "insert into $table (%s) values (%s)",
    join("," => keys %data),
    join("," => ('?') x scalar keys %data);

my $sth = $dbh->prepare($insert);
   $sth->execute( values %data );

##########

my $update = sprintf "update $table set %s where %s",
    join(","     => map { "$_=?" } keys %data),
    join(" and " => map { "$_=?" } keys %constraints);

my $sth = $dbh->prepare($update);
   $sth->execute( values %data, values %constraints );
[download]

*: similar modules = Class::Tables. I'm quite biased, but I can't pass up any opportunity for a plug ;)

i'm not a huge fan of sprintf, mostly because i always forget the formatting codes.

and so far most of the code has been tied to a CGI object ( not the best solution, but very few projects have needed to be decoupled from it ), so i can get away with:

my $cnt = 1;
foreach my $field ( @fields ) {
   $sth->bind_param( $cnt, $q->param( $field );
   $cnt++;
}
[download]

i'll probably end up poking around more, but since i'm the only developer there, i don't want to blow too much time looking for solutions that don't really result in a gain.

Class::DBI takes care of all the basics for you while at the same time helping to keep all your sql stuff in dedicated modules. Once you have your module set up for a table inserts & updates are easy as pie and custom queries aren't much harder.

package My::Table;
use base 'My::Class::DBI';

__PACKAGE__->table('blah');
__PACKAGE__->columns( All => qw/ foo bar baz quid quux / );
__PACKAGE__->columns( Essential => qw/ foo bar / );
__PACKAGE__->columns( Primary => 'foo' );

# assuming we have another table called 'other'..
# this creates a method called sql_method_name
__PACKAGE__->set_sql( method_name => qq( select b.foo, b.bar, o.summat
+, o.summat_else from blah b left join other o using(foo) where b.quid
+=?));

sub public_method_name {
    my ($class,$quid) = @_;
    my $sth = $class->sql_method_name();
    $sth->execute($quid);
    return @{$sth->fetchall_arrayref({})};
}
[download]

and using the module...

# fetch a My::Table object
my $record = My::Table->create({ foo => 1,
                                 bar => 'bar val',
                                 baz => 'baz val',
                                 quid => 'quid val',
                                 quux => 'quux val' });
# update a value
$record->quid('new quid val');
$record->update;

# fetch a list of records via our join method
my @list = My::Table->public_method_name('new quid val');
[download]

I've also recently started using TEMP columns to store values from other tables in the retrieved object via custom join methods so I can still use the oo interface to read the values (can't update in this case) but it's beer o'clock and I don't have an example of that handy.. 8)

Class::DBI takes care of all the basics for you while at the same time helping to keep all your sql stuff in dedicated modules. Once you have your module set up for a table inserts & updates are easy as pie and custom queries aren't much harder.

i already do what i can to pull all of my SQL out of main 'functional' modules and put them into SQL.pm-type classes/packages ( i think the current one is named ArticleQureies.pm and all it does is fetch/store and return results ).

i'll have to spend some time playing with it, but it doesn't seem like much of a win to write the query, install it as a method, and then call the method. ( well, OK, it does, but it doesn't require the 'overhead' of importing another module. )

i don't expect there to be a good way of generating everything should of a lot of concatenation ... :-(

and i'm not particularly fond of table --> object mapping ( if it's a one-to-one relationship ). i've already had to deal with a 'home-grown' solution with a previous job that just became unmaintainable, unweildy, slow, and excessively complicated.

Class::DBI is better than most homegrown versions of this sort of application, which is why people use it.

perldoc Class::DBI

...
INTRODUCTION
    Class::DBI provides a convenient abstraction layer to a database.

    It not only provides a simple database to object mapping layer, but can
    be used to implement several higher order database functions (triggers,
    referential integrity, cascading delete etc.), at the application level,
    rather than at the database.