in reply to Database field names in code

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)?

Replies are listed 'Best First'.
Re: Re: Database field names in code
by revdiablo (Prior) on Apr 10, 2004 at 05:12 UTC

    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.

[reply]
[d/l]
[select]

In Section Meditations