Perl DBI Oracle not preserving column order after SELECT

Question

I'm using Perl v5.12.3 built by ActiveState on Windows. DBD::Oracle version 1.27. DBI version 1.616. I'm selecting the data below in this particular order and wanting the resulting data to be retrieved in that same order. Below are the code samples and some examples.

SQL Snippet (contents of $report_sql below)

select student_number, lastfirst, counselor, 
   a.dateenrolled as "Date Enrolled 1", a.dateleft as "Date Left 1", a.termid as "Term ID 1", a.course_number as "Course Number 1", 
   a.expression as "Expression 1", b.dateenrolled as "Date Enrolled 2", b.dateleft as "Date Left 2", 
   b.termid as "Term ID 2", b.course_number as "Course Number 2", b.expression as "Expression 2"

Perl code snippet

## run the resulting query
my $report_result = $dbh->prepare( $report_sql );
$report_result->execute();   
while( my $report_row = $report_result->fetchrow_hashref())
        {
            print Dumper(\$report_row); ## contents of this posted below

Contents of print Dumper for $report_row

$VAR1 = \{
        'Expression 2' => 'x',
        'LASTFIRST' => 'xx',
        'Term ID 1' => 'xx',
        'Date Enrolled 2' => 'xx',
        'Course Number 1' => 'xx',
        'Term ID 2' => 'xx',
        'STUDENT_NUMBER' => 'xx',
        'Date Left 2' => 'xx',
        'Expression 1' => 'xx',
        'COUNSELOR' => 'xx',
        'Date Left 1' => 'xx',
        'Course Number 2' => 'xx',
        'Date Enrolled 1' => 'xx'
      };

Order I EXPECTED it to be printed

$VAR1 = \{
        'STUDENT_NUMBER' => 'xx',
        'LASTFIRST' => 'xx',
        'COUNSELOR' => 'xx',
        'Date Enrolled 1' => 'xx',
        'Date Left 1' => 'xx',
        'Term ID 1' => 'xx',
        'Course Number 1' => 'xx',
        'Expression 1' => 'xx',
        'Date Enrolled 2' => 'xx',
        'Date Left 2' => 'xx',
        'Term ID 2' => 'xx',
        'Course Number 2' => 'xx',
        'Expression 2' => 'x'
      };

One thing to note is that this query being ran is one of many that are being ran. This particular script runs through a series of queries and generates reports based on the returned results. The queries are stored in files on the hd alongside the perl script. The queries are read in and then ran. It's not always the same columns being selected.

ikegami · Accepted Answer · 2012-10-05T18:54:57.003

8

You used a hash. Hash elements have no controllable order*. The order of elememts in an arrays can be controlled. If you want to present the order in which the fields were received, use an array instead of hash.

If you actually need the names, you can get the ordered names of the fields using @{ $sth->{NAME} }. You should still use an array for efficiency reasons, but you could use a hash if you wanted to.

* — Just like array elements are returned in the order they are "physically" organised in the array, hash elements are returned in the order they are physically organised in the hash. You cannot control where an element is physically placed in a hash, and the position changes as the hash is changed. With an array, you decide the physical position of an element, and it will remain in that position.

edited Oct 05 '12 at 18:54

answered Oct 05 '12 at 17:51

ikegami

367,544
15
269
518

Thank you. I assumed that's where my problem was... But wasn't sure if maybe there was some simpler fix. I was using the hash keys to dynamically create the "headers" of the reports. – kreeves Oct 05 '12 at 18:47
(Not copied from Ven'Tatsu. My update was posted a few seconds before his.) – ikegami Oct 05 '12 at 20:18
Is 'NAME' supposed to be the name of a column? Currently, I pass the `$report_row` to the `write_row` method that is from `Spreadsheet::WriteExcel`. – kreeves Oct 05 '12 at 21:04
1

No, as I wrote it. Ven'Tatsu gave an example. – ikegami Oct 06 '12 at 03:25
Interesting... I never knew such functionality existed but now I see it in the docs. Thanks a lot to you and Ven'Tatsu. – kreeves Oct 08 '12 at 15:27

score 3 · Answer 2 · edited Oct 05 '12 at 18:57

When the order of columns in a DBI result matters you can get the column names and values as array references.

...
my $names = $report_result->{NAME}; # or NAME_lc or NAME_uc
while( my $report_row = $report_result->fetchrow_arrayref() ) {
    for my $col_idx ( 0 .. $#{$names} ) {
        print "$names->[$col_idx]: $report_row->[$col_idx]\n";
    }
}

Back before I had to worry about internationalization I used this a lot to generate CSV reports, just pass the NAME array to Text::CSV before passing the result arrays and writing a report just becomes writing a query.

Perl DBI Oracle not preserving column order after SELECT

2 Answers2