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.

Anyhow, you should make a set of constant "accessors" (as if this were an object) which return the field and table names, column numbers, etc. That way, when tables change or spawn, you have only one place to change the data.

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.

---

;$;=sub{$/};@;=map{my($a,$b)=($_,$;);$;=sub{$a.$b->()}} split//,".rekcah lreP rehtona tsuJ";$\=$;[-1]->();print

Something related to your question was debated in the Monastery a few years ago, and it ended up with a couple of articles:

• DBIx::Recordset VS DBI
  (update I am NOT recommending this article as a good resource. It is useful to read it, though, to better understand chromatic's reply.)
• DBI is OK by chromatic

More recently, DBI Recipes addressed the same issue, offering some good advice.

update Thanks to Tilly for clarifying. I had the sequence of facts upside down.

The actual history a few years ago is that princepawn had an article published, it drew some criticism, and then chromatic has his reply published.

For some of the discussion that appeared between those two events, see DBI vs DBIx::Recordset vs princepawn vs chromatic. For reasons that are clear in my contribution to that discussion, I wouldn't recommend the original article as a useful learning resource.

Ideally your data abstraction classes should be isolating you from your database implementation. Meaning, you should not have to change any code that uses them if you change the database structure, assuming of course your change isnt so major as to require a rethinking/recoding of said data abstraction classes. Of course this is idealistic and in reality it may not be this black and white.

Personally the way I avoid database structural changes rippling throughout my code, is by using a set of Object Relational Mapping classes. Usually comprised of an Entity class and matching Data Access class, although sometimes they are combined into a single class. I then make sure from that point on all my other code only uses the Entity classes interface. As long as the metaphor my entity is based on is solid, then I find any underlying database changes have minimal effect. More often than not, additions are the only noticable changes.

I would really hate to be the one maintaining code like that.

What happens if someone adds a column, or rearranges the columns? I know some folks that will change their column order whenever they add a column to a table so that their auditing columns are always the last columns in the table. Guaranteed breakage.

I would think that:

   my $q=$dbh->query("INSERT INTO foo (col1, col2, col3, col4, col5) V
+ALUES (?,?,?,?,?);
   $q->execute($bar, $baz, $qx, $qux, $quux);
[download]

would be more robust as it doesn't make any assumptions about either the column order or the number of columns (assuming any columns not listed are nullable). Also, wouldn't naming the columns make the code more readable and maintainable (by virtue of being able search on the column names)?

Another thing I like to avoid is the dreaded and error-prone counting of placeholders. To accomplish this goal I build an array of fields I want, and use it multiple times. Example:

    my @keys = qw(time_value hour_24 minute second hour_12 am_pm);

    my %time = (
      time_value => "2:30:12pm",
      hour_24    => 14,
      hour_12    =>  2,
      minute     => 30,
      second     => 12,
      am_pm      => "pm"
    );

    my $sql  = "INSERT INTO"
             . "   time (" . join(",", @keys)         . ")"
             . " VALUES (" . join(",", ('?') x @keys) . ")";

    my $sth = $dbh->prepare($sql);
    $sth->execute(@time{@keys});
[download]

Note I keep the values I'm executing in a hash with keys matching the field names. This allows me to use a hash slice so even the execute() call is guaranteed to match.

1. It doesn't need to get worse because of stored procedures. It may get better. Depending on the nature of the change you may end up having to change just the procedures without having to leak the change to the upper levels. That is if your stored procedures are more than just SELECT All, Columns FROM TableName WHERE ID = @ID.

2. Data Abstraction layer is one of the best places to use code generation. The functions/methods look very similar so there is no need to maintain them by hand.

Edit by castaway: Closed small tag in signature

For me, it's generally not a matter of hard coding the field names, but limiting the amount i do. When retrieving data for display, i strive to only "hard code" my database field names in 2 places ... the SQL query and the template (usually HTML). I discuss more of that in a moment, but for now, know that DBI allows you to deal with the query results in a variety of useful ways. You really should become familar with the documentation and then have a look into Class::DBI. For example, try this code on one of your SELECT queries:

use DBI;
use Data::Dumper;

my $dbh = DBI->connect( ... );

# Example 1: List of Lists
print Dumper $dbh->selectall_arrayref('select id,name from status');

# Example 2: List of Hashes
print Dumper $dbh->selectall_arrayref(
 'select id,name from status',
 { Slice=>{} },
);
[download]

$VAR1 = [
          [
            '0',
            'enrolled'
          ],
          [
            '1',
            'dropped'
          ]
        ];

my $rslt = $dbh->selectall_arrayref('select name,id from status');

my %hash = map @$_, @$rslt;

print Dumper \%hash;
[download]

But what about the second example, the one with the strange and cryptic { Slice => {} }? For a web developer who has to build reports and such, that is one of the best things since sliced bread. With this, i need only specify which fields i want in the SQL query, and then once more in the template. Here is an example that uses an easy to install CPAN module, HTML::Template. Just run the code first, you can ask more questions later. :)

use DBI;
use HTML::Template;

my $dbh = DBI->connect(
   qw(DBI:vendor:database:host user pass),
   {RaiseError=>1},
);

my $things = $dbh->selectall_arrayref('
   SELECT id,name
   FROM status
',{Slice=>{}});

# optionally do more things to some of the fields
# yes, we do have to say their names again
$_->{id} = sprintf("%03d",$_->{id}) for @$things;

my $tmpl = HTML::Template->new(filehandle => \*DATA);
$tmpl->param(things => $things);

print $tmpl->output;

__DATA__
<tmpl_loop things>
   <tmpl_var id>: <tmpl_var name>
</tmpl_loop>
[download]

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)