You're right that it gets ugly fast. Like you, I've been doing a lot of DBI work, and I've found that the bigger the SQL statements, the uglier it gets. Here's what I came up with

Starting with the "everything in a line" technique, but showing more columns/values

 my $bsql = "insert into messages(messageid, quoteid, subject, body, viewed, toid, fromid, timestamp_c, timestamp_m) values($next_messageid, $quoteid, $q_subject, $q_body, 0, $toid, $fromid, CURRENT TIMESTAMP, CURRENT TIMESTAMP)";

How long does it take to make sure all your columns names and values line up? What happens the next time you need to add a column? I get lost pretty quick looking at that style.

Now, let's try it in a vertical layout:

   my $sql = "insert into messages (
     messageid,
     quoteid,
     toid,
     fromid,
     subject,
     body,
     viewed,
     timestamp_c,
     timestamp_m
   ) values (
     $next_messageid,
     $quoteid,
     $toid,
     $fromid,
     $q_subject,
     $q_body,
     0,
     CURRENT TIMESTAMP,
     CURRENT TIMESTAMP)";
[download]

OK, so you have a little better hope of adding/removing columns without breakage. And people are generally better at counting lines than counting words (for figuring out exactly which column a value goes with). C-k C-k C-y in emacs (or the vi equivalent) can move columns,values around very efficiently. I definitely like this one better (even though it grows off screen pretty quick).

Now, for a third way. How about something like:

my $msgdata = { messageid => $next_messageid,
                quoteid => $quoteid,
                toid => $toid,
                fromid => $fromid,
                subject => $q_subject,
                body => $q_body,
                viewed => 0,
                timestamp_c => "CURRENT TIMESTAMP",
                timestamp_m => "CURRENT TIMESTAMP" };

my $sql = "insert into msg (";
my $values = ") values (";

my $started = 0;
foreach my $datum (keys(%$msgdata)) {
    if (length($msgdata->{$datum}) > 0) {
        if ( $started ){
            $sql .= ",\n   $datum";
            $values .= ",\n  $msgdata->{$datum}";
        } else {
            $started = 1;
            $sql .= " $datum";
            $values .= " $msgdata->{$datum}";
        }
    }
}

$sql .= $values;
[download]

Now, before you reach for that barf bag, take another look. This third way does some things the first 2 don't.

• It checks to make sure there's something there. If one of the values is undefined, it will still generate valid SQL.
• You always know exactly which column a given value goes with.
• In fact, everything after creating the hash can be put into a nice little sub so you don't have to look at the foreach.

It takes some getting used to, but I find the third option a lot more maintainable.

Also notice I'm always creating a scalar to hold the SQL, never putting it right into the prepare(). That's to make it easier to print(). I find that I have to do that so often I might as well just plan on it, no matter which style I'm making the SQL with.

SQL is an ugly language (why does update have nice name=value syntax, while insert doesn't?) and it just gets uglier as you add code to $dbh->quote() all your values and wrap things in the eval{} blocks that make your code robust enough to handle a failed transaction. Automatically generating the SQL at least cuts out some typo problems.

---

---

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.