Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

perl dbi - no column headings

by cosmicsoup (Beadle)
on Jul 09, 2003 at 16:31 UTC ( #272726=perlquestion: print w/replies, xml ) Need Help??

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"; }

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"; }

    Chris
    M-x auto-bs-mode

      Chris,

      Your solution worked perfectly.

      Thanks,
      Louis
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();

    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

      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

      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.

        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

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://272726]
Approved by gmax
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (3)
As of 2022-07-01 07:22 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My most frequent journeys are powered by:









    Results (98 votes). Check out past polls.

    Notices?