in reply to DBI hashref does not return data in order of query

Hashes are unordered. On the bright side, DBI provides an ordered list of field names: @{ $sth->{NAME} } 

my @names = @{ $sth->{NAME} };

while ( my $row = $sth->fetchrow_hashref ) {
   my @ordered_values = @{$row}{@names};
   ...
}

[download]

is roughly equivalent to 

while ( my $row = $sth->fetchrow_arrayref ) {
   my @ordered_values = @$row;
   ...
}

[download]

Also see the FetchHashKeyName attribute.

Replies are listed 'Best First'.
Re^2: DBI hashref does not return data in order of query
by hallikpapa (Scribe) on Oct 23, 2007 at 03:10 UTC
    This looks like what I want. I put all of the columns I want in the correct order in @fields. In your example you just have $sth->{NAME}, but how would I push the already sorted array into the hash? Or am I missing something simple. I will continue to test. I tried this, and didn't seem to like it 
    my @names = @{ $sth->{@fields} };  <------Didn't like this
    while ( my $row = $sth->fetchrow_arrayref ) {
        @ordered_values = @{$row}{@names};
    }

    [download]
    Can't use an undefined value as an ARRAY reference
[reply]
[d/l]

      For portability reasons, I'd not use {NAME} at all, but {NAME_lc} or {NAME_uc}. If you do not want to pull out the column names out of the statement handle, (why not btw?), you can do something like ...

      my $sth;
my @columns = qw( col1 col2 col3 col4 col5 col6 );
{   local $" = ",";
    $sth = $dbh->prepare (
        "select @columns ftom table where day = '10-08-2007' order by 
+col3, col4");
    }
$sth->execute;
while (my $row = $sth->fetchrow_hashref) {
    ...
    }

      [download]

      Enjoy, Have FUN! H.Merijn
[reply]
[d/l]
[select]
Re^2: DBI hashref does not return data in order of query
by hallikpapa (Scribe) on Oct 28, 2007 at 22:03 UTC
    I had to put this off to the side for a bit, and just got back into it. Here is what I am trying to get work: 
    my @names          = @{ $sth->{NAME_uc} };
    my @ordered_values = ();
    while ( my $row = $sth->fetchrow_hashref ) {
        @ordered_values = @{$row}{@names};
    }
    print Dumper(\@names);
    print Dumper(\@ordered_values);
        my $cgi = CGI->new;
    print $cgi->header('text/xml');
    my $testxml =
      XMLout( \@ordered_values, NoAttr => 1, RootName => 'dataset', );
    print $testxml;

    [download]
    And here is the response from the Dumper: 
    $VAR1 = [
          'NPA',
          'GC_ID',
          'I_TG_NAME',
          'E_TG_NAME',
          'DN',
          'STATE'
        ];
$VAR1 = [
          undef,
          'AAAABUcKyqQhEAABI34stA.4515613',
          undef,
          undef,
          undef,
          undef
        ];

Content-Type: text/xml; charset=ISO-8859-1

<dataset>
  <anon></anon>
  <anon>AAAABUcKyqQhEAABI34stA.4515613</anon>
  <anon></anon>
  <anon></anon>
  <anon></anon>
  <anon></anon>
</dataset>

    [download]
    So @names is correct, but in the @ordered_values, I am getting a bunch of blank fields (checked if it was a query problem, it's not), and that gibberish line is actually data(the GC_ID field). Can you see what's wrong with the push I am doing? The XML::Simple function seems to want a hash of the data so it can put the key in the element tag, and the hash value inside the tags? <edit> Empty data was cause of the NAME_uc. Changed to NAME and it plugged in data, but everything still is ANON. Update This while loop also seems to overwrite the data in the @ordered_values array as when I print using XMLout (XML::Simple), there is only one XML entry.
[reply]
[d/l]
[select]

      It has already been said: use fetchrow_arrayref

      I suppose this is what you want: 

      
  my @ordered_values = ();
  while (my $row = $sth->fetchrow_arrayref)
  {
     push @ordered_values, [@$row];
  }
  print "-- DBI says:\n";
  print XMLout( \@ordered_values, NoAttr=>1, RootName=>'dataset', );

  print "\n";
  print "-- dumper says:\n";
  print Dumper(\@names);
  print Dumper(\@ordered_values);

      [download]
[reply]
[d/l]
[select]
        Thanks for the reply. I updated the original post in this thread accidently, but I will mention it here. It's reporting all of the data back properly, but it is not putting the column headers as the element tags, they are all coming back as <anon> still, which is clear because I am not pushing the @names array into @ordered_values, or the XMLout at all. And since there is like 500 records returned, I wanted to put each record in cdr tags, and surrounded by the dataset root tag. This is how it is running right now. 
        my @names          = @{ $sth->{NAME} };
    my @ordered_values = ();
    while ( my $row = $sth->fetchrow_arrayref ) {
        push @ordered_values, [@$row];
    }

    my $cgi = CGI->new;
    print $cgi->header('text/xml');
    print "-- DBI says:\n";
  print XMLout( \@ordered_values, NoAttr=>1, RootName=>'dataset', );

  print "\n";
  print "-- dumper says:\n";
  print Dumper(\@names);
  print Dumper(\@ordered_values);

        [download]
        This is what it returns. 
        <anon>
    <anon>AAAABUcKyqQhEAABI34stA.4515613</anon>
    <anon>989</anon>
    <anon>OQ-OZDN-D-UHMC-UHMC-0939</anon>
    <anon>MQ-MTBO-T-CCDPN-FTQ-1737</anon>
    <anon>9895555555</anon>
    <anon>MI</anon>
  </anon>

        [download]
        Thank you for your paitence!
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom