Take a look at the NAME statement variable. From the DBI docs.

"NAME"  (array-ref, read-only)
           Returns a reference to an array of field names for
           each column. The names may contain spaces but should
           not be truncated or have any trailing space. Note that
           the names have the letter case (upper, lower or mixed)
           as returned by the driver being used. Portable appli-
           cations should use "NAME_lc" or "NAME_uc".

             print "First column name: $sth->{NAME}->[0]\n";
[download]

   my $sth = $dbh->prepare("SELECT * FROM $tablename WHERE 1=0");
   $sth->execute();
   my $fields = $sth->{NAME};
[download]

MJD says you can't just make shit up and expect the computer to know what you mean, retardo! I run a Win32 PPM repository for perl 5.6x+5.8x. I take requests. ** The Third rule of perl club is a statement of fact: pod is sexy.

   $sth = $dbh->prepare($query) or die "Prepare exceptioin: $DBI::errs
+tr!";
   $rv = $sth->execute() or die "Execute exception: $DBI::errstr";
   $res = $sth->fetchall_arrayref();                    
   
   # Array reference with cols captions, which were retrived.
   $names = $sth->{NAME};          
   
   # Array reference with types of cols, which  were retrived         
+             
   $types = $sth->{TYPE};                               
   $sth->finish();
[download]

      
--------------------------------
SV* sv_bless(SV* sv, HV* stash);

adrianh's suggestion is fine. I'll just add that you also have the option to use fetchrow_hashref instead of fetchrow_array: this method returns a refernece to an hash which keys are the field names and values the field values for that row.

Michele.

They are not the same thing, though.

Not only fetchrow_hashref will be slower, because the names are assigned for each row, while $sth->{NAME} is only evaluated once, after the call to "execute," but in addition, if your query contains two columns with the same name, $sth->{NAME} will handle it, but fetchrow_hashref will not.

select * from dept; select * from emp;
+--------+----------+
| deptID | name     |
+--------+----------+
|      1 | pers     |
|      2 | sales    |
|      3 | research |
+--------+----------+

+-------+-------+--------+
| empID | name  | deptID |
+-------+-------+--------+
|     1 | John  |      1 |
|     2 | Fred  |      2 |
|     3 | Susan |      2 |
+-------+-------+--------+

select distinct dept.deptID, emp.deptID 
    from dept left join emp using(deptID);
+--------+--------+
| deptID | deptID |
+--------+--------+
|      1 |      1 |
|      2 |      2 |
|      3 |   NULL |
+--------+--------+
[download]

Given these tables, the Perl code to handle them could be:

my $query = qq{select distinct dept.deptID, emp.deptID 
    from dept left join emp using(deptID)};

my $sth = $dbh->prepare($query);

$sth->execute();
print "--- names\n";
print join ", ", @{$sth->{NAME}};

print "\n--- fetchrow_hashref\n";
my $row = $sth->fetchrow_hashref();
print "$_ \t" for keys %$row;
print "\n";
$sth->finish();

__OUTPUT__

--- names
deptID, deptID
--- fetchrow_hashref
deptID
[download]

Notice that $sth->{NAME}, despite the double names, returns the correct result. fetchrow_hashref, instead, misses one column altogether.

Thanks cchampion,

Cool, didn't know that... hmmm, /me "thinks have to go redo some code now". I've been working on a bit of code with a friend to 'clone' an SQL table row, you've just given me a couple of very valuable ideas for improvement... thanks again!

barrd

arthas is correct, and here is a bit of (untested) code to do exactly that - though there is undoubtly other (better?) ways to do it.

my $sql = "SELECT * FROM mytable WHERE foo = bar";
my $sth = $dbh->prepare($sql);
$sth->execute() || die $DBI::errstr;

my $row = $sth->fetchrow_hashref;
my %cols = %$row;

# Print the field names
print join(', ', sort keys %cols);

# Print corresponding values
print join(', ', map { defined $cols{$_} ? $dbh->quote($cols{$_}) : 'N
+ULL' } sort keys %cols);
[download]

Only posted as a guideline...
barrd