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

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.

Replies are listed 'Best First'.
Re^3: DBI hashref does not return data in order of query
by erix (Prior) on Oct 28, 2007 at 23:38 UTC

    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]

        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]

In Section Seekers of Perl Wisdom