I don't think he meant that you can't know the names anywhere. He appears to be referring to hardcoding the table structure in queries.

Yes, that's closer to what I meant. In context I was talking about the fact that you're going to add fields later, even if you currently don't think so.

I consider it okay for any given bit of code to know the names of the fields it is actively working with. As disciple points out, this is nearly unavoidable. The trouble comes when you write a piece of code that relies on knowing all of the fields. Don't do that, because you'll have to change every single query whenever you add fields.

If I'd known about Class::DBI when I first ran into this problem, that's what I'd have used. I didn't, so I rolled my own set of routines that do essentially the same thing but with a function interface rather than an object interface. I call it db.pl, and I have a number of CGI database thingies at work that use it. The interface looks like this:

$result  = db::addrecord(tablename, $record_as_hashref);
$id      = db::added_record_id;
@change  = @{db::updaterecord(tablename, $record_as_hashref)};
%record  = %{db::getrecord(tablename, id)};
@record  = db::getrecord(tablename); # Not for huge tables.
@record  = db::findrecord(tablename, fieldname, exact_value);
@record  = @{db::searchrecord(tablename, fieldname, value_substring)};
[download]

This is *MUCH* better than my previous method of embedding SQL all over the place. (I can still use a db handle directly for custom SQL in the few rare cases where the above functions aren't enough, such as when the code needs to create tables on the fly.) The functions are general, and in all cases the records being passed around are hashrefs, which works out rather nicely. If I had to do over again, though, I would use Class::DBI instead, mainly to avoid re-inventing the wheel but also because it's often easier to install a module from the CPAN than it is to pull a copy of my roll-my-own stuff from another system where it's installed. I'm pretty sure Class::DBI does everything my self-rolled stuff does (though not in quite the same way).

The real key, though, is that it doesn't hardcode the list of (or even the number of) fields. Here's a sample excerpt from addrecord:

  my @clauses = map { "$_=?" } sort keys %r;
  my @values  = map { $r{$_} } sort keys %r;
  my $q = $db->prepare("INSERT INTO $table SET ". (join ", ", @clauses
+));
  my $result = $q->execute(@values);
[download]

This way, when new fields are added (as long as they're not set NOT NULL without a default value), I don't have to hunt through the existing code for places that have to be updated; the only parts that have to be updated are the parts that actually need to use the new fields. I believe Class::DBI also provides this benefit.

---

---

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.