Perl::DBI - return column header

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

Hi Monks, Is it possible for Perl::DBI (Oracle) to return the name of the column header each time I run a "select ..." query? I ca'nt find any way to do that easily. Below are a sample of a code which I always use to run select statement. Thanks.


$dbh = DBI->connect ( "dbi:Oracle:host=$ip;sid=$sd;port=1521", $id, $p
+w, {RaiseError => 1, AutoCommit => 0} );

    my $sqlexe = $dbh->prepare( $sql );
    my @tempdata;
    my @data;

        $sqlexe->bind_columns ( undef, \$tempdata[0][0], \$tempdata[0]
+[1] );
        while ( $sqlexe->fetch() ) {
            push @{@data}, [ $tempdata[0][0], $tempdata[0][1] ];
        }
[download]

Comment on Perl::DBI - return column header Download Code

Replies are listed 'Best First'.

Re: Perl::DBI - return column header
by pfaut (Priest) on Dec 10, 2008 at 15:51 UTC

After you execute the statement, the statement handle should contain a NAME attribute. This is a reference to an array containing the names of the columns returned. $sth->{NAME}[$i] will give you the name of column $i. See perldoc DBI.

90% of every Perl application is already written. ⇒

dragonchild

[reply]
[d/l]
[select]

Re^2: Perl::DBI - return column header

by Anonymous Monk on Aug 12, 2013 at 11:43 UTC

100 rows

[reply]

Re: Perl::DBI - return column header
by Corion (Patriarch) on Dec 10, 2008 at 14:08 UTC

See DBI, about "Statement handle attributes". The usual approach to getting the column names for a statement is to ->execute it with an additional WHERE 1 = 0 clause.

[reply]
[d/l]
[select]

Re: Perl::DBI - return column header: Yes, fetchrow_hashref
by Narveson (Chaplain) on Dec 10, 2008 at 15:30 UTC

Use either fetchrow_hashref or selectrow_hashref and extract the keys.

my $hashref = $sqlexe->fetchrow_hashref();
my @column_names = keys %$hashref;
[download]

Update

My vote is for the NAME attribute of the statement handle (next reply). Thanks, pfaut.

If, like me, you've been indoctrinated into thinking that a class's interface should consist solely of methods, you'll miss a lot of good stuff that DBI makes available in the form of attributes.

[reply]
[d/l]
[select]


Welcome to the Monastery
	PerlMonks