Your code is a marvelous example of the danger of such approaches. Your code could easily break if someone were to add to the database a column that has the same name as an existing variable in your program!

I agree with the first replier: If spelling out your variable names is too tedious (and it probably would be too tedious for me), use hashes. That's what they're for. And they can have nice short names.

    -- Chip Salzenberg, Free-Floating Agent of Chaos

The problem that I have with hashes is that you go through all of the work in strict to make sure you cannot mistype your variable names and then you throw it all away because there is no check for misspelled hash entries.

I have used 2 solutions to get around that in the past. One is Class::Struct, the other is pseudo-hashes. Each has their gotchas. Now I know you had some bad luck with pseudo-hashes in the Topaz effort, which leads me to a couple of questions.

First of all what do you suggest to this problem? (Want to use a hash or reference to one, but with only validated keys allowed.)

And secondly is it likely that stuff I did with pseudo-hashes is going to be low priority (ie in the 5% acceptable breakage) for Perl 5.6? (I know it is early for this question, I just would like an educated guess.)

Thanks,
Ben

Plan A: Put a unique prefix on the database column names, turning off strict temporarily:

    { no strict 'refs';
      $sth->bind_columns( map { \${"c_$_"} @{ $sth->{NAMES_lc} } ) }
[download]

Plan B: If you want the advantages of variables and the advantages of hashes, there is a way, but it will cost you a little in speed. There is one language construct that can see variables and can be built with strings, even with strict enabled: eval.

    local $, = ',$'; # saves on the joins
    eval "\$sth->bind_columns(\\( \$@{$sth->{NAMES_lc}} ))"
[download]

However, now you're actually letting column names into the Perl parser! A saying about frying pans and fires comes to mind.... I'd much rather disable strict than make wide use of eval STRING.

As for pseudohashes: I think they're great. The only thing about their implementation in Perl 5 that ate me alive was the fact that they weren't a distinct data type, but rather a mere access method, usable with any array at any time. If they had to be declared somehow at their creation, hey, I'd have been praising them in my Topaz talk.

    -- Chip Salzenberg, Free-Floating Agent of Chaos

As a runtime solution, create a tied hash that supported only a limited set of keys, throwing an exception if a distinct key was used.

-- Randal L. Schwartz, Perl hacker

> I agree with the first replier: If spelling out your variable names is too tedious (and it probably would be too tedious for me), use hashes. That's what they're for. And they can have nice short names.

Too slow. :-)

> Your code is a marvelous example of the danger of such approaches. Your code could easily break if someone were to add to the database a column that has the same name as an existing variable in your program!

I appreciate the wake-up call, but consider this:

my($field1, $field2, $field3);
$sth->prepare("SELECT Field1, Field2, Field3 ...");
$sth->bind_columns(\($field1, $field2, $field3));
while($sth->fetch) {
    print "$field1\t$field2\t$field3\n";
}
[download]

Versus:

my($field1, $field2, $field3);
$sth->prepare("SELECT Field1, Field2, Field3 ...");
$sth->bind_columns(map { \$$_ } @{ $sth->{NAME_lc} });
while($sth->fetch) {
    print "$field1\t$field2\t$field3\n";
}
[download]

In both situations, our would-be saboteur has to add a field variable to the my() list, a field name to the SELECT list, and then add the output command. If that doesn't hammer the point home that the variable name is reserved, I doubt one more step (adding the same field to the list of binded variables) is going to make much of a difference.

I guess the point I'm trying to make here is that there's more than one way to do it... and there's more than one way to break it. In-line comments and self-documenting code (ie, I did not use a SELECT *, the field list was explicitly defined) are really our best tools in fighting "ignorance". Any script can be easily broken in any number of ways.

Besides the fact, my proposal to use a separate package name gets around this problem. :-) Other than, of course, someone using the same package name. But I think it's safe to assume that a programmer experienced and confident enough to be creating packages like that will at least take a look around to make sure the package is being used. This is where documentation and commenting comes in handy!

Thanks for your insight.

Alakaboo

my %columns;

# stuff here

$sth->bind_columns(
    map { \${ $columns{$_} } @{ $sth->{NAME_lc} }
)  || die("unable to bind columns: ", $sth->errstr);

# dereference somehow, may not be correct

while($sth->fetch) {
    print "<tr><td>$name</td><td>$columns{price}</td></tr>\n";
    print "<tr><td colspan=\"2\">$columns{description}</td></tr>\n";
}
[download]

If I were going to use that approach, I would almost certainly drop the bind_columns altogether and do:

while(my $columns = $sth->fetchrow_hashref) {
    print "<tr><td>$columns->{name}</td><td>$columns->{price}</td></tr
+>\n";
    print "<tr><td colspan=\"2\">$columns->{description}</td></tr>\n";
}
[download]

But I'm trying to avoid that. Primarily, I asked this question in hopes of learning a bit more about hard references.

Thanks for your help. :-)

Alakaboo

If the typing is the problem, consider ( in vi ) yanking to end of line ( y$<ENTER> in command mode ) and pasting ( p ). I don't use emacs, but I will bet lots of money it can do something very similar. That is even faster than typing in the loop and retains clarity.

Probably over simplistic and not very monkish to shrink from the weird loop. But I like simplicity at times, and I think this is one of them.

mikfire

> This funky loop buys what? A severe headache when I have to fix it, by my estimates.

• Use of the speedy binded columns feature in DBI
• A quick & dirty solution for a poor speller/typist
• A method that will hopefully never break to be fixed. Hah!

> Probably over simplistic and not very monkish to shrink from the weird loop. But I like simplicity at times, and I think this is one of them.

On the contrary, I think it is very "monkish" to go the simple route. I'm inclined to agree 100% in that understand-at-a-glance is best. But I became lazy, and you know what that means.

> ...consider ( in vi ) yanking to end of line ( y$ in command mode ) and pasting ( p )...

Hmm a little sarcasm there? Duly noted for future flamebait... mua! ;-)

Thanks for your comments, mik.

Alakaboo

You're welcome. :-)

Just please, please pay heed to all the things mentioned here:

• Garbaging the main package with variables, that may 'accidentally' be overwritten sometime
• Lack of clarity for someone unfamiliar with your code
• Why we have 'strict' to begin with: to avoid this kind of problem
• Any others I can think of between now and later... *grin*

And keep bringing those issues to us if you're ever in doubt about something!

Alakaboo

sub select {
    my $s       = shift;
    my $sql     = shift;
    my $sth = $s->dbh->prepare( $sql ) ||    
        $s->write_log("Can't prepare select ($sql): ".$s->dbh->errstr)
+;
    $sth->execute() ||
        $s->write_log("Can't execute select ($sql): ".$sth->errstr);
    $s->fields  = $sth->{NAME};    
    my $x = 0;
    my @ar = ();
    while ( my $hr = $sth->fetchrow_hashref() ) {
        $ar[$x++] = $hr;
    }
    $s->rows = $x;     
    $s->ar   = \@ar;   
    $sth->finish();
}
[download]

You can simplify most of this code:

      my $x = 0;
      my @ar = ();
      while ( my $hr = $sth->fetchrow_hashref() ) {
          $ar[$x++] = $hr;
      }
      $s->rows = $x;     
      $s->ar   = \@ar;
[download]

to this:

my @ar = ();
while (my $hr = $sth->fetchrow_hashref() ) {
  push @ar, $hr;
}
$s->ar = \@ar;
$s->rows = @ar;
[download]

But I think there's a "fetchall_hashref" or something like that that could do this all in one fell swoop. Durn. Not enough window space on this speaker-lounge-area dumb PC to go looking up "man DBI". Sorry.

-- Randal L. Schwartz, Perl hacker

But I think there's a "fetchall_hashref" or something like that that could do this all in one fell swoop. Durn. Not enough window space on this speaker-lounge-area dumb PC to go looking up "man DBI". Sorry.

Indeed there is:

my @ar = ();
while (my $hr = $sth->fetchrow_hashref() ) {
  push @ar, $hr;
}
$s->ar = \@ar;
$s->rows = @ar;
[download]

... can be written ...

$s->ar   = $sth->fetchall_arrayref({});
$s->rows = @{$s->ar};
[download]

On a side note, fetchall_arrayref() is one of my favorite methods because HTML::Template templates take an arrayref of hashrefs as a parameter for loops. You can output a table really easily like so:

# Assume $sth is a valid statement handle
my $params   = $sth->fetchall_arrayref({});
my $template = new HTML::Template (filename => 'file.name');

$template->param(TABLENAME => $params);
print $template->output();
[download]

The template file looks like this:

<TABLE>
<TMPL_LOOP NAME=TABLENAME>
  <TR><TD><TMPL_VAR NAME=COLUMN1></TD><TD><TMPL_VAR NAME=COLUMN2></TD>
+</TR>
</TMPL_LOOP>
</TABLE>
[download]

The stuff between the <TMPL_LOOP> tags outputs for each row returned by the query, and it only takes two lines of perl to get the rows from the db to the template.

-Matt