How can I get column names and row data in order w

Question

I'm using DBI to query a SQLite3 database. What I have works, but it doesn't return the columns in order. Example:

Query:  select col1, col2, col3, col4 from some_view;
Output:

    col3, col2, col1, col4
    3, 2, 1, 4
    3, 2, 1, 4
    3, 2, 1, 4
    3, 2, 1, 4
    ...

(values and columns are just for illustration)

I know this is happening because I'm using a hash, but how else do I get the column names back if I only use an array? All I want to do is get something like this for any arbitrary query:

    col1, col2, col3, col4
    1, 2, 3, 4
    1, 2, 3, 4
    1, 2, 3, 4
    1, 2, 3, 4
    ...

(That is, I need the output is in the right order and with the column names.)

I'm very much a Perl novice, but I really thought this would be a simple problem. (I've done this before in Ruby and PHP, but I'm having trouble tracking down what I'm looking for in the Perl documentation.)

Here's a pared down version of what I have at the moment:

use Data::Dumper;
use DBI;

my $database_path = '~/path/to/db.sqlite3';

$database = DBI->connect(
  "dbi:SQLite:dbname=$database_path",
  "",
  "",
  {
    RaiseError => 1,
    AutoCommit => 0,
  }
) or die "Couldn't connect to database: " . DBI->errstr;

my $result = $database->prepare('select col1, col2, col3, col4 from some_view;')
    or die "Couldn't prepare query: " . $database->errstr;

$result->execute
    or die "Couldn't execute query: " . $result->errstr;

########################################################################################### 
# What goes here to print the fields that I requested in the query?
# It can be totally arbitrary or '*' -- "col1, col2, col3, col4" is just for illustration.
# I would expect it to be called something like $result->fields
########################################################################################### 

while (my $row = $result->fetchrow_hashref) {
    my $csv = join(',', values %$row);
    print "$csv\n";
}

$result->finish;

$database->disconnect;

Answer 1

You're asking for the result as a hash. A hash is inherently unordered. Perhaps you want fetchrow_arrayref instead.

In fact, if you had looked at keys %$row, you would have seen the corresponding keys being out of order as well. That's the nature of a hash... each key is paired with its value, but the overall ordering of keys or values is optimized for access, not external ordering.

Answer 2

Define your column names in an ARRAY before your SELECT

Ideally you'd have a list of the columns you were SELECT'ing with DBI, and you'd use that array.

If you need to get the column names from the hash itself, this will work, and you can sort it, but there is no indication of the original SQL SELECT order (in the hash):

my %cols_hash = ("name" => "john", "age" => 2, "color" => "apalachian");
my $cols_hash_ref = \%cols;  

my @keys = (sort keys %$cols_hash_ref);  
my @vals;  
foreach (@keys){ push @vals, $$cols_hash_ref{$_} };  

Hope this helps.

When I searched I found a way to get the column names from the DBI:

$sth = $dbh->prepare($query) or die "Prepare exceptioin: $DBI::errstr!";  
$rv = $sth->execute() or die "Execute exception: $DBI::errstr";  
$res = $sth->fetchall_arrayref();  

# Array reference with cols captions, which were retrived.  
$col_names_array_ref = $sth->{NAME};          

That should give you the column names in the original order, but I haven't tested it.

Answer 3

Here's what I do:

    use Data::Dump qw(dump);
    # get column names in array
    my @column_names_array= $sth->{NAME};  
    # print out column names in pretty format
    print "Field names: \n";
    dump(@column_names_array);

Answer 4

If you want to preserve the order, but still use a hash to refer to fields by name use:

$dbh->selectall_arrayref($sql,{ Slice => {} } );

This will give you an ordered array of hashes

Answer 5

Replace the "what goes here" comment and the following loop with:

my $fields = join(',', @{ $result->{NAME_lc} });
print "$fields\n";

while (my $row = $result->fetchrow_arrayref) {
    my $csv = join(',', @$row);
    print "$csv\n";
}

NAME_lc gives the field names in lowercase. You can also use NAME_uc for uppercase, or NAME for whatever case the database decides to return them in.

You should also probably be using Text::CSV or Text::CSV_XS instead of trying to roll your own CSV file, but that's another question.