<select name=city_id>
 <TMPL_LOOP cities>
   <option value="<TMPL_VAR id>" <TMPL_IF selected>selected</TMPL_IF>>
    <TMPL_VAR city_name>
 </TMPL_LOOP>
</select>
[download]

<select name=city_id>
 <TMPL_LOOP cities>
   <option value="<TMPL_VAR id>"><TMPL_VAR city_name>
 </TMPL_LOOP>
</select>
[download]

sub _getBuilderList {
  my $self = shift;

  my $sth = $self->param('DBH')->prepare("SELECT id, city_name FROM ci
+ty
ORDER BY city_name" );
  $sth->execute();

  # returns a reference to an array of hash refs
  my $cities= $sth->fetchall_arrayref( {} );
  $sth->finish;

  return $cities;
}
[download]

my $city_list= $self->_getBuilderList;

# ...

my $template= $self->load_tmpl( ... );
$template->param(
  cities => $city_list,
  # ...
);

require HTML::FillInForm;
return HTML::FillInForm->new->fill(
  scalarref  => \$template->output,
  fobject    => $self->query,
);
[download]

$self->query->param( city_id => $some_value );

# HTML::FillInForm code as before
[download]

sub _getBuilderList {
  my $self = shift;

  my $sql = q{
    SELECT id, city_name
    FROM city
    ORDER BY city_name
  };

  return selectall_sql( $self->param('DBH'), $sql );
}

# (needs proper error handling)
sub selectall_sql {
  my( $dbh, $sql, $values )= @_;

  $values ||= [];
                                                                      
+          
  my $results= $dbh->selectall_arrayref(
    $sql,
    { Slice => {} },
    @$values
  );
                                                                      
+          
  return $results if $results;
                                                                      
+          
  warn $dbh->err if $dbh->err;
                                                                      
+          
  return;
}
[download]

sub do_select
{
    my $self = shift;
    my %params = @_;
    $params{params} ||= [];
                                                                      
+          
    my $results;
    eval {
        my $sth = $self->dbh->prepare_cached(
            $params{sql},
        );
                                                                      
+          
        $sth->execute(
            @{$params{params}},
        );
                                                                      
+          
        $results = $sth->fetchall_arrayref( {} );
    }; if ($@) {
        warn $@, "\n$params{sql}\n@{$params{params}}\n";
        return;
    }
                                                                      
+          
    if (UNIVERSAL::isa($params{inflater}, 'CODE'))
    {
        my @results = $params{inflater}->($results);
                                                                      
+          
        return (
            wantarray
                ? @results
                : $results[0]
        );
    }
    else
    {
        return $results;
    }
}
[download]

The function takes three parameters, two of which are optional. The sql parameter is the SQL statement you want executed. params is an optional array reference of the parameters to that statement. (You are using placeholders, right?) If you pass an inflater parameter, it will call that function, passing in the returned value from the fetchrow_arrayref({}) call.

Assumptions:

• $self->dbh will return a database handle.
• That $dbh was created with the following call:

  $dbh = DBI->connect( $connect_string, $user, $password, { PrintError =
  +> 0, RaiseError => 1 } );
  [download]

Now, the inflater is passed an arrayref of hashes. Do with it what you will. if you don't pass in an inflater, that AoH is passed back to the caller of do_select().

There is a base class which supports all the basic get / set functions, a meta-SQL language ( I know that this is not liked by many ), and one very important function "unroll()".

I have a class for each table, or join of tables, in the db. Each of these classes inherits from the base class, and thus "can" unroll itself. Unroll knows how to crawl the attributes and arrays within the class and produce an HTML::Template friendly representation of the data within the instance.

Within the driver code, which is usually procedural, I build an array of the data that is supposed to be made available to the template processor. I pass this array and the template name to a Display function. It unrolls the objects in the array, then processes the template using the new HTML::Template friendly struct.

The naming of the template values is standardized so that you know how to call them; for instance a class FOO with an attribute "bar" would be in the hash as "FOO.bar" variable.

I have lately been wondering whether Class::DBI supports this kind of transformation because I would be interested in replacing my home-grown code, which attempts the same thing as Class::DBI, with Class::DBI.

Does anyone know?

Well one step to simplifiy that would be to use selectall_array($sql, {Slice => {}}) and then one of your subs becomes:

sub _getBuilderList {
        my $self = shift;
        my $selection = shift;
 
        my @cities = $self->param('DBH')->selectall_array("SELECT id, 
+buildername FROM builder ORDER BY buildername", {Slice => {}} );
        @cities = grep { $_->{SELECTED}++ if $_->{id} == $selection; $
+_ } @cities;
        return \@cities;
}
[download]

hmmm ... i was thinking more about not having to build each little accessor sub over and over.

But why would you have to? Just pass in the SQL and the selected id:

sub make_select {
   my ($dbh, $sql, $args, $id) = @_;
   my $items = $dbh->selectall_arrayref($sql, {Slice => {}}, $args);

   for ($items) {
      $_->{selected} = 1 if $_->{id} == $id;
   }

   return $items;
}
[download]

jeffa

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)