DBI convert fetched arrayref to hash

Question

I'm trying to write a program to fetch a big MySQL table, rename some fields and write it to JSON. Here is what I have for now:

use strict;

use JSON;
use DBI;

# here goes some statement preparations and db initialization

my $rowcache;
my $max_rows       = 1000;
my $LIMIT_PER_FILE = 100000;

while ( my $res = shift( @$rowcache )
    || shift( @{ $rowcache = $sth->fetchall_arrayref( undef, $max_rows ) } ) ) {

    if ( $cnt % $LIMIT_PER_FILE == 0 ) {

        if ( $f ) {
            print "CLOSE $fname\n";
            close $f;
        }

        $filenum++;
        $fname = "$BASEDIR/export-$filenum.json";

        print "OPEN $fname\n";
        open $f, ">$fname";
    }

    $res->{some_field} = $res->{another_field}
    delete $res->{another_field}

    print $f $json->encode( $res ) . "\n";

    $cnt++;
}

I used the database row caching technique from Speeding up the DBI and everything seems good.

The only problem I have for now is that on $res->{some_field} = $res->{another_field}, the row interpreter complains and says that $res is Not a HASH reference.

Please could anybody point me to my mistakes?

Answer 1

If you want fetchall_arrayref to return an array of hashrefs, the first parameter should be a hashref. Otherwise, an array of arrayrefs is returned resulting in the "Not a HASH reference" error. So in order to return full rows as hashref, simply pass an empty hash:

$rowcache = $sth->fetchall_arrayref({}, $max_rows)