Class::DBI isn't really for abstracting the SQL-generation process. Of course, it must generate SQL, but everything's under the hood -- you don't use Class::DBI to get back SQL that you execute yourself. Use it (or similar modules*) when you want to have an abstract, object-oriented view of your data and not have to think about SQL at all. The catch for this abstraction is that it's harder to do complex queries all at once. But don't think you can't get the same results, for instance:

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

This code simulates a join among the "cds", "tracks", and "songs" tables using an object-persistence interface. You get the same results. Of course, it uses many queries on the database instead of just one join query. You need to weigh the gains of this abstraction against the performance of your complex queries when you simulate them like this. But also keep in mind that Class::DBI and friends execute most select queries using the primary key as a constraint, making all those small individual queries very fast.

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]

But if you do need to do complex stuff like joins, you'd probably be better off with a CPAN solution. You're own your own there, I've never tried any of them myself. Try searching for SQL or in the DBIx namespace.

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

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.