[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

perl dbi fetchall array, but with column headers

Posted on 2012-08-28
4
Medium Priority
?
3,364 Views
Last Modified: 2012-08-29
Using Perl DBI, can anyone show me how to fetch all rows from a result set, but have the first row contain the column names?

Instead of [ [ row1data1,row1data2,...],[row2data1,row2data2,...] ] (which is what 'fetchall_arrayref()' returns)

I would like to have:
[ [column_name1,column_name2,...], [ row1data1,row1data2,...], [row2data1,row2data2,...] ]

Any scheme ideas would be welcome.
0
Comment
Question by:grump-
4 Comments
 
LVL 10

Expert Comment

by:tdlewis
ID: 38343383
Here's a routine that will accomplish the task, however, if you're going to call the routine often, you should cache the field names:
sub fetchResults
{
  my ($dbh,$table,$query) = @_;
  return complain("No database has been opened.") unless defined($dbh);

  my @fields = ();

  # Retrieve the field names for the table
  my $sth = $dbh->prepare("DESCRIBE `$table`");
  $sth->execute();
  while (my $item = $sth->fetchrow_hashref())
  {
    push @fields, $item->{Field} if defined($item);
  }
  $sth->finish();
  my @result = (\@fields);

  # Execute the query
  $sth = $dbh->prepare($query);
  $sth->execute();

  # Process and return the results
  while (my $item = $sth->fetchrow_hashref())
  {
    next unless defined($item);
    my @row = map{$item->{$_}} @fields;
    push @result, \@row;
  }
  $sth->finish();
  return @result;
}

Open in new window


I fetch all the table names and the field descriptions for each table when I open a database. When I need the field names, I just use @{$TABLE{$table}};
0
 
LVL 28

Assisted Solution

by:FishMonger
FishMonger earned 400 total points
ID: 38343700
By inference, I assume that you're sql statement is "SELECT * from table".  You really shouldn't do it that way.  Instead, you should specify the fields and in the order in which you want then retrieved.  If you do that, then you'll already know/have the field names.

If you don't want to specify the fields in the select statement, then you can retrieve them after the execute statement.  Instead of doing a separate "DESCRIBE" statement as tdlewis suggests, it would be cleaner and more efficient to use the statement handle's "NAME" attribute.

Here's an example.
#!/usr/bin/perl

use strict;
use warnings;
use DBI;
use Data::Dumper;

my $dbh = DBI->connect("DBI:mysql:mysql:localhost", 'user', 'password',
                       { RaiseError => 1 }) or die $DBI::errstr;

my $sth = $dbh->prepare("SELECT * from user");
$sth->execute;

my $fields = $sth->{NAME};
print Dumper $fields;

Open in new window

Outputs:
$VAR1 = [
          'Host',
          'User',
          'Password',
          'Select_priv',
          'Insert_priv',
          'Update_priv',
          'Delete_priv',
          'Create_priv',
          'Drop_priv',
          'Reload_priv',
          'Shutdown_priv',
          'Process_priv',
          'File_priv',
          'Grant_priv',
          'References_priv',
          'Index_priv',
          'Alter_priv',
          'Show_db_priv',
          'Super_priv',
          'Create_tmp_table_priv',
          'Lock_tables_priv',
          'Execute_priv',
          'Repl_slave_priv',
          'Repl_client_priv',
          'Create_view_priv',
          'Show_view_priv',
          'Create_routine_priv',
          'Alter_routine_priv',
          'Create_user_priv',
          'Event_priv',
          'Trigger_priv',
          'Create_tablespace_priv',
          'ssl_type',
          'ssl_cipher',
          'x509_issuer',
          'x509_subject',
          'max_questions',
          'max_updates',
          'max_connections',
          'max_user_connections',
          'plugin',
          'authentication_string'
        ];

Open in new window

0
 
LVL 25

Accepted Solution

by:
lwadwell earned 1600 total points
ID: 38343719
Try

my $sth = $dbh->prepare($sql) or die("\n\nPREPARE ERROR:\n\n$DBI::errstr");
$sth->execute or die("\n\nQUERY ERROR:\n\n$DBI::errstr");

my @cols = $sth->{NAME};   ## Save the column names
my @data = $sth->fetchall_arrayref();
unshift(@data, @cols);   ## Add the column names onto the start or array

Open in new window

0
 

Author Closing Comment

by:grump-
ID: 38345717
I was bouncing around the 'NAME' key from
http://search.cpan.org/~timb/DBI/DBI.pm#fetchall_arrayref 
and
http://docstore.mik.ua/orelly/linux/dbi/ch05_06.htm
I even bought the book:
http://www.amazon.com/Programming-Perl-DBI-Tim-Bunce/dp/1565926994
I assumed that since fetch.....hash option knows about the column names, somehow the handle must know about the column names for any fetch. It would seem that the database would return column names always. I just could not find an example to make it work. Now I have two. Thanks so much!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
A year or so back I was asked to have a play with MongoDB; within half an hour I had downloaded (http://www.mongodb.org/downloads),  installed and started the daemon, and had a console window open. After an hour or two of playing at the command …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Six Sigma Control Plans
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question