Thanks for the info. That seems to have worked but all my XML element tags now say anon, instead of being the column header of the query results.

my %xml_hash = ( dataset => [] );
    while ( my $row = $sth->fetchrow_arrayref ) {
        push @{ $xml_hash{cdr} }, $row;
        #print Dumper($row);
    }
[download]

Any pointers on how to do that? Thanks again!

A couple things I noticed, 1) you are assigning the array reference to the same hash key each iteration through the loop (is this what you want?), 2) pushing the array ref into the hash gives you an anonymous array within an anonymous array, which I don't think is what you want. It might make more sense to assign the row to the key (provided you are using different keys through the loop).

If you just want an array of array references (unless there's a field in the returned row that is unique enough to use as a hash key) you can try:

my @allrows = ();
while (my $row = $sth->fetchrow_arrayref) {
    push @allrows, $row;
}
[download]

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.

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

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

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.

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]