in reply to Re^2: DBI hashref does not return data in order of query
in thread DBI hashref does not return data in order of query

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]

Replies are listed 'Best First'.
Re^4: DBI hashref does not return data in order of query
by hallikpapa (Scribe) on Oct 28, 2007 at 23:46 UTC
    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]

      Not an DBI question anymore, then. Let's give XML::Writer a chance:

      Assuming your data is in the @ordered_values array: 

      use XML::Writer;
my $writer = XML::Writer->new( NEWLINES=>0, DATA_MODE=>1, DATA_INDENT=
+>2, );
$writer->startTag("dataset");
for(my $i=0; $i< @ordered_values;$i++)
{
      my $row = $ordered_values[ $i ];
      $writer->startTag("row", "num" => $i);
      for(my$j=0; $j < @$row; $j++)
      {
              $writer->startTag("cdr", "name" => $names[$j]);
              $writer->characters( $row->[ $j ] );
              $writer->endTag();
      }
      $writer->endTag();
}
$writer->endTag();
$writer->end();

      [download]
[reply]
[d/l]
        This did it. I really appreciate your help!
[reply]

In Section Seekers of Perl Wisdom