BACKGROUND

DBI has a wonderful call called bind_columns, which takes a list of references to variables and assigns those variables to columns. This is A) the most efficient data retrieval method, and B) kinda neat and very simple to create output.

#!/usr/bin/perl

use DBI;
use warnings 'all';
use strict;

my $dbh = DBI->connect(...)
    || die("could not create handler: ", $DBI::errstr); 

my $sth = $dbh->prepare(q{
    SELECT Name, Price, Description
    FROM Products
    ORDER BY Name, Price
})  || die("could not prepare product listing: ", $dbh->errstr);

$sth->execute 
    || die("could not execute product listing: ", $sth->errstr);

my($name, $price, $description);
$sth->bind_columns(\($name, $price, $description));

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

(DISCLAIMER: Yes, I use CGI.pm normally, Yes, I know about RaiseError, and Yes, I know that $DBI::errstr, $dbh->errstr, and $sth->errstr are all the same thing :-)

At this point, all is well in the garden of good and evil.

MIKE STEPS UP TO BAT

Somewhere along the lines, I became good 'n lazy. I decided that I wanted a sleeker, more elegant method than declaring my variables one at a time, and then binding my columns (more or less) one at a time. Why not use a loop, or map, or some such? Thus the following code was born unto my script:

# ... assume up to $sth->execute

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

# ... assume same while-loop and output as before
[download]

This performed wonderfully and did exactly what I asked it to. I could query as many columns as I darn well pleased and bind them to variables, with a minimal amount of typing and no variable spelling errors. There was just one teeny tiny little problem... it would not run under `strict', unless I declared all my variables with `my' beforehand.

So, naturally, I made sure the rest of my code worked, then turned strict off. >:)

A CHANGE OF HEART

Several weeks (months? years?) later, I came to my senses and decided that this code really needed to run with strict on after all. Not willing to do a `no strict qw(vars)', because in my eyes that's as sinful as `no strict', I began wresting with scoping variables who's names were contained in other variables. I've gotten it to work (mostly) with symbolic references, but `no strict qw(refs)' is, again, as sinful as `no strict'.

It is at this point that I appeal to my fellow monks. At this point, having already gotten it to compile using symbolic references, I am interested primarily in a learning experience.

Is there any way to do this with hard references?

Below is my current code for you to check out. Any help is greatly appreciated.

Alakaboo

---

#!/usr/bin/perl

use DBI;
use warnings 'all';
use strict;
no strict 'refs'; # Ni!

my $dbh = DBI->connect(...)
    || die("could not create handler: ", $DBI::errstr); 

my $sth = $dbh->prepare(q{
    SELECT Name, Price, Description
    FROM Products
    ORDER BY Name, Price
})  || die("could not prepare product listing: ", $dbh->errstr);

$sth->execute 
    || die("could not execute product listing: ", $sth->errstr);

$sth->bind_columns(
    map { \${"main::$_"} }   # `my' isn't going to cut it
       @{ $sth->{NAME_lc} }  # who knows where this is going to run?
)  || die("unable to bind columns: ", $sth->errstr);

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

---

---

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.