in reply to DBI fetchall_arrayref using $max_size doesn't dereference normally.

If you call $sth->fetchall_arrayref(undef,$max_rows) like you do, then your result will be an array of arrays, not an array of hashes, like you would like.

Moreover, your usage of $max_rows is not recommended. The docs state clearly that you should use a buffer to fetch records using that parameter.

# example from DBI docs my $max_rows=10_000; my $rows = []; # cache for batches of rows while( my $row = ( shift(@$rows) || shift(@{$rows=$sth->fetchall_arrayref({},$max_rows) || []}) ) ) { ... # do something with $row (not $rows, which is only the cache) }

Last, but not least, be aware that you are using the same statement handler for a outer and an inner loop. That is a mistake! The second "prepare" will reset $sth, and it will not continue fetching records. You must use two of them, one for fetching records, and one for updating.

Please see:

Before trusting your code with 1 million records, try it with some smaller data set, to ensure that it's doing what you need.

 _  _ _  _  
(_|| | |(_|><

Replies are listed 'Best First'.
Re^2: DBI fetchall_arrayref using $max_size doesn't dereference normally.
by Tatnall (Beadle) on Jul 28, 2005 at 21:10 UTC

    The code is currently at work, thank you. I'm sure there is an even better way than the way I modified the code per your suggestion since I don't understand all of what is happening or why.

    If you or someone else could unpack the while statement it could be greatly appreciated.
      The docs say that when there are no more rows, fetchall_arrayref returns a reference to an empty array. That would imply your while loop would never terminate (a reference is always true, even if it's a reference to an empty array). gmax's suggested code above fixes that.