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

Hello Monks,

My code is simple enough. Straight out of the perl books. And my problem is basic. I want to see the column names in my output. Is there something that I am missing? This code retrieves the data, but not the column names. Is this possible? 
my $sth = $dbh->prepare($sql);
$sth->execute() || die $sth->errstr;

my @row;

while (@row = $sth->fetchrow_array) {
        print IN join("\t", @row), "\n";
        }

[download]

Thanks, Louis

Replies are listed 'Best First'.
Re: perl dbi - no column headings
by lachoy (Parson) on Jul 09, 2003 at 16:34 UTC

    You need to grab the column names manually:

    my $sth = $dbh->prepare($sql);
$sth->execute() || die $sth->errstr;

my $columns = $sth->{NAME};
print IN join( "\t", @{ $columns } ), "\n";

while ( my @row = $sth->fetchrow_array ) {
    print IN join("\t", @row), "\n";
}

    [download]

    Chris
    M-x auto-bs-mode

[reply]
[d/l]
      Chris,

      Your solution worked perfectly.

      Thanks,
      Louis
[reply]
Re: perl dbi - no column headings
by hardburn (Abbot) on Jul 09, 2003 at 16:40 UTC

    TMTOWTDI. This strikes me as an OK time to use SELECT *:

    # Use LIMIT 1 to keep the database from working 
# too hard 
my $sql = q(SELECT * FROM table LIMIT 1); 
my $sth = $dbh->prepare($sql) or die . . . ;
$sth->execute();

my @col_names = keys %{ $sth->fetchrow_hashref };

$sth->finish();

    [download]

    There are most certainly better ways, though.

    ----
    I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
    -- Schemer

    Note: All code is untested, unless otherwise stated

[reply]
[d/l]
[select]
      I strongly disagree - you shouldn't run a select * just to get the column names of another query.

      Use the functionality provided by DBI - namely the $sth->{NAME} attribute (or the related NAME_lc and NAME_uc for the lower case and upper case versions) - this will provide column names in the same order as the current query and is completely generic so that it should handle all situations.

      Michael

[reply]
[d/l]
[select]

      This is not the same thing. Using fetchrow_hashref does not handle double column names in the query.

      Please see Re: Re: Field names from DBI? for more details.

[reply]

        The SQL above isn't doing any JOINs, so I don't see how double column names would be a problem. Now, fetchrow_hashref() has other problems, but collisions aren't one of them in this (simple) case.

        ----
        I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
        -- Schemer

        Note: All code is untested, unless otherwise stated

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom