<yours>
my %db;
$sth->bind_columns( \( @db{ @{ $sth->{NAME} } } ));# magic
while ($sth->fetch)
{
#... and no worries about which order the columns get returned in
#... since you access them via the $db{ColumnName} method :)
</yours>
Right, no worries, but a good point to make is that the hash keys are in an
array - so the order in which they are returned while doing that is always
in the order they returned by the query, so it is perfectly safe to do
something like this when the two tables have the same columns:
my $select = qq{SELECT * FROM Sometable};
my $selecth = $dbh1->prepare($select) || die "Can't
prepare\n$select\n$DBI::errstr\n"
$selecth->execute() || die "Can't execute\n$select\n$DBI::errstr\n";
$selecth->bind_columns(undef, \(@col{ @{$selecth->{NAME}}}));
my $insert = qq{INSERT INTO SomeOtherTable ($columnlist) VALUES (}
. '?' . '?' x $#col{ @{$selecth->{NAME}}} . ')';
my $inserth = $dbh2->prepare($insert) || die "Can't
prepare\n$insert\n$DBI::errstr";
while ($selecth->fetch) {
#do some manipulation if necessary
$inserth->execute(@col{ @{$selecth->{NAME}}}) || die "Can't execute
$insert: $DBI::errstr\n";
}
# or if you are making a pipe delimited file instead of inserting elsewhere:
while ($selecth->fetch) {
#do some manipulation if necessary
print outfile join('|', @{$selecth->{NAME}}}) . "\n";
}
Syntax untested in that example, but I use the principle sometimes. It gives
the advantage of the speed of bind_columns instead of fetchrow_hashref, and
the ability to access the columns by name, and it keeps all the columns in
order for the use in execute or print or whatever else might be useful. I
find it very slick when I need column names.
Steve H.
-----Original Message-----
From: Scott R. Godin [mailto:[EMAIL PROTECTED]]
Sent: Friday, November 02, 2001 6:28 AM
To: [EMAIL PROTECTED]
Subject: Re: Column Names
In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] (Bart Lateur) wrote:
> On Thu, 01 Nov 2001 18:56:18 -0800, Venkataramana Mokkapati wrote:
>
> >How do I get column names and order of column names
> >for a "select * from ..." query.
>
> If you have
>
> $sth = $dbh->prepare("select * from ...");
>
> then try
>
> @column names = @{$sth->{NAME}};
>
> You may have to do an "execute" first, for this to return anything of
> value.
>
> It's in the DBI docs under the heading "Statement Handle Attributes", in
> the DBI POD formatted as text around line 2284.
the absolute neatest trick I've seen with this, that is so totally
perlish it defies description.. you stare at it for a bit and suddenly
all becomes clear.
$sth->execute
or die("Cannot Execute SQL Statement: ", $sth->errstr(), "\n");
my $rows = $sth->rows;
# only expecting one row for a unique ID . this should NEVER happen.
safe_error("invalid number of rows returned from database ($rows) for
ID $id")
if $rows > 1;
# although this might...
safe_error("no match in database for ID $id")
if $rows < 1;
my %db;
$sth->bind_columns( \( @db{ @{ $sth->{NAME} } } ));# magic
while ($sth->fetch)
{
#... and no worries about which order the columns get returned in
#... since you access them via the $db{ColumnName} method :)
--
Scott R. Godin | e-mail : [EMAIL PROTECTED]
Laughing Dragon Services | web : http://www.webdragon.net/
