Don't ask to ask, just ask | |
PerlMonks |
Re: dbi style questions (code, discussion)by edebill (Scribe) |
on Dec 29, 2001 at 22:39 UTC ( [id://135141]=note: print w/replies, xml ) | Need Help?? |
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:
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:
Now, before you reach for that barf bag, take another look. This third way does some things the first 2 don't.
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.
In Section
Seekers of Perl Wisdom
|
|