Getting Column Names from DBI?

vivekvp has asked for the wisdom of the Perl Monks concerning the following question:

Hello again,

1) Is there a method to get the field name and corresponding field data from a database - or do you have to manually insert the field names.

i.e.
43  $sth=$dbh->prepare("select * from people where name=?");
44  $sth->execute($name);                                      
45  my @ar = $sth->fetchrow_array();                            
46          foreach $ar (@ar) {                                 
47                  print "$ar \n";                             
48          }                                                   
49  $sth->finish;
[download]

When I do a simple select from a large table it just gives the field data only

name doug
last mitchell
phone 1234567
address house
colour blue
[download]

But if I do a select like this in perl - I just get:

doug
mitchell
1234567
house
blue
[download]

Anyway to get the field name with the field data. I would like to grab this info from many table reusing the code - and some table have 100 field names....

Thanks!

V

He who laughs last, doesn't get the joke.

Comment on Getting Column Names from DBI? Select or Download Code

Replies are listed 'Best First'.
Re: Getting Column Names from DBI? by dws (Chancellor) on Oct 09, 2002 at 20:33 UTC
Try doing the following immediately after `execute()`: `my $names = $sth->{'NAME'}; my $numFields = $sth->{'NUM_OF_FIELDS'}; for (my $i = 0; $i < $numFields; $i++) { printf("%s%s", $$names[$i], $i ? "," : ""); }` [download] `NAME` and `NUM_OF_FIELDS` are magic attributes of a statement handle that are supported by many DBDs.	[reply] [d/l] [select]
Re: Getting Column Names from DBI? by Zaxo (Archbishop) on Oct 09, 2002 at 20:28 UTC
The `fetchrow_hashref()` method will return the column names as keys. For newer DBI there is a FetchHashKey attribute which gives some control over the form of the keys. See the DBI pod. After Compline, Zaxo	[reply]
Re: Re: Getting Column Names from DBI? by diotalevi (Canon) on Oct 09, 2002 at 21:03 UTC
Perhaps I'm daft but I've always preferred DBI's fancy form of binding directly to hash variables. `(direct quoting from DBI) Here's a more fancy example that binds columns to the values inside a hash (thanks to H.Merijn Brand): $sth->execute; my %row; $sth->bind_columns( \( @row{ @{$sth->{NAME_lc} } } )); while ($sth->fetch) { print "$row{region}: $row{sales}\n"; }` [download] `__SIG__ printf "You are here %08x\n", unpack "L!", unpack "P4", pack "L!", B:: +svref_2object(sub{})->OUTSIDE` [download]	[reply] [d/l] [select]