Multiple references pointing to the same memory location

ezekiel has asked for the wisdom of the Perl Monks concerning the following question:

I have a subroutine that works like this:


# Loop over a series of keys

foreach $key (@keys) {
    
    # Use the key to do a database lookup

    $sth->execute($key);

    # Get the results of the lookup ie all the entries in
    # the row keyed by the key.

    $columns_ref = $sth->fetchrow_arrayref();

    # Push this reference onto another array to build up
    # an array of the rows corresponding to each key

    push(@entries, $columns_ref);

}
[download]

Later on I want to loop through @entries and use each reference to access each row in turn. Everytime I execute the statement handle, the array returned is stored in the same memory location ($columns_ref). Hence at the end of the loop @entries looks something like:

(Array(0x8057040), Array(0x8057040), Array(0x8057040), ... )

So when I loop over @entries later, I get the same memory location and hence the same (last) row over and over, rather than each row in turn.

Is there a way to get around this? I am sure there is a simple solution but I just can't see it at the moment.

I have tried the following:


foreach $key (@keys) {
    $sth->execute($key);
    $temp_columns_ref = $sth->fetchrow_arrayref();
    @temp_columns = @$temp_columns_ref;
    $columns_ref = \@temp_columns;
    push(@entries, $columns_ref);
}
[download]

This works but it is ugly and I am worried about the memory/performance involved in creating the copy of the array (@temp_columns) as there are generally a large number of rows each with a large amount of data.

Any comments on the memory implications of references versus array would also be helpful as my understanding of this area is limited.

Thanks.

Comment on Multiple references pointing to the same memory location Select or Download Code

Replies are listed 'Best First'.
Re: Multiple references pointing to the same memory location by davorg (Chancellor) on Jul 31, 2001 at 12:01 UTC
This is a very common error. So common, in fact, that it's listed in the Common Mistakes section of the Perl Data Structures Cookbook. The problem (as you can see) is that you are reusing the same reference each time round the loop. There are two ways round this. You can either take a copy of the array each time like this: `foreach $key (@keys) { $sth->execute($key); $columns_ref = $sth->fetchrow_arrayref(); push(@entries, [@$columns_ref]); }` [download] Or you can make the reference variable lexical to the block, thus forcing a new variable to be created each time, like this: `foreach $key (@keys) { $sth->execute($key); my $columns_ref = $sth->fetchrow_arrayref(); push(@entries, $columns_ref); }` [download] Personally, I prefer the second alternative. Update: Having read other responses in this thread saying that DBI will always return the same reference, it looks like the second option won't work in this case. The first one will tho'. -- <http://www.dave.org.uk> Perl Training in the UK <http://www.iterative-software.com>	[reply] [d/l] [select]
Re: Multiple references pointing to the same memory location by meonkeys (Chaplain) on Jul 31, 2001 at 12:00 UTC
from perldoc DBI: Note that the same array reference is returned for each fetch, so don't store the reference and then use it after a later fetch. I interpret this to mean there is no way around the full array copy. I mainly use Oracle... Tim Bunce seems to recommend using bind params for maximum efficiency on the dbi_users list.	[reply]
Re: Multiple references pointing to the same memory location by mitd (Curate) on Jul 31, 2001 at 12:09 UTC
Here is a direct quote from Programming the Perl DBI, Decartes and Bunce, O'Reilly, Page 223 Section fetchrow_arrayref. 'Note that the same array reference will currently be returned for each fetch, don't store reference and then use it after a later fetch' In other words you are getting a shallow copy of row array instead of the deep copy you want/need. ( of course this what you have already discovered :( ). Why not dereference rowarray_ref and simple store and array of arrarys. `... push(@entries, [ @$colunms_ref ]); ...` [download] Update 'Holy Flatlander Chips' bwana147 caught a my baadddd very baddd deref causing column_ref flating out. I can't claim a typo on this one folks I just plain forgot :(. thx bwana147. In addition after a short CB discussion with davorg we reach a consensus of two that this could all be resolved by simply using fetchrow_array and avoiding all his pesky derefencing. mitd-Made in the Dark 'My favourite colour appears to be grey.'	[reply] [d/l]
Re: Re: Multiple references pointing to the same memory location by bwana147 (Pilgrim) on Jul 31, 2001 at 13:10 UTC
Be careful here: the OP wanted a list of array refs, each one containing a row of data. If you `push(@entries, @$columns_ref)`, you'll end-up with `@entries` being a flat list of all the columns, without any way of knowing where each row starts or ends. You really must create a new array, copy the values from the array referenced by `$column_ref` and push a reference to the new array. This can be done easily, as has already been pointed out: `push(@entries, [ @$columns_ref ]);` [download] --bwana147	[reply] [d/l] [select]
Re: Multiple references pointing to the same memory location by DBX (Pilgrim) on Jul 31, 2001 at 15:22 UTC
You could probably avoid extra steps by taking an actual array and then referencing it, rather than doing 'ref-deref-ref': `for my $key (@keys) { $sth->execute($key); my @columns = $sth->fetchrow(); push(@entries, \@columns); }` [download] Without having seen anymore of your code, it also looks like you might want to bind some variables. Anytime you are iterating over an execute statement, binding is usually the way to go rather than rebuilding and repreparing with different variables.	[reply] [d/l]
Re: Multiple references pointing to the same memory location by thpfft (Chaplain) on Jul 31, 2001 at 16:12 UTC
I suspect you can make the problem go away by fixing up your SQL. You're trying to retrieve records for an arbitrary list of ids, or something similar, right? If your database supports it, use IN (list) instead, and you can avoid the loop entirely. eg: `my @entries; my $idlist = join(',',map("'$_'",@keys)); my $sth = $dbh->prepare("SELECT * FROM mumble WHERE id IN($idlist)"); $sth->execute(); while (my $aref = $sth->fetchrow_arrayref()) { push(@entries,$aref); }` [download] It's nice to learn why my 'push while fetch' constructions never worked, though. update: if your @keys might contain anything more complicated than simple strings or numbers, it's better to use: `my $idlist = join(',',map($dbh->quote($_),@keys));` I think.	[reply] [d/l] [select]
Re2: Multiple references pointing to the same memory location by pmas (Hermit) on Jul 31, 2001 at 22:35 UTC
Using IN(...)clause of SQL was exactly I wanted to propose when reading question on top - and it is already answered. ++ to you thpfft. Just small warning: different SQL dialects might have different limitations on how many items might go to IN(...) clause. So check your documentation and make sure you will tear your list and submit it to ->execute in chunks your SQL can handle, with smallest number of $sth->execute calls. And also be aware now you have piece of SQL code which is database-dependent. How bad it is depends of how many CGI scripts you have to manage. Maybe you want to put all SQL-dialect dependent snippets of code into separate module? pmas To make errors is human. But to make million errors per second, you need a computer.	[reply]
Re: Multiple references pointing to the same memory location by runrig (Abbot) on Aug 01, 2001 at 01:17 UTC
If you have too many keys for an IN clause, then you may as well fetch an array since thats what you're saving. Also, if you're only fetching one row, you should call $sth->finish, though the following shouldn't be any different (implicitly calling finish when done) if only one row exists for each key: `foreach my $key (@keys) { # You are using 'RaiseError', right? $sth->execute($key); while (my @temp_columns = $sth->fetchrow_array) { push(@entries, \@temp_columns); } }` [download] Update:I see DBX had mostly the same answer. Oh well, it was a good answer, and worth repeating :)	[reply] [d/l]
Re: Multiple references pointing to the same memory location by BrentDax (Hermit) on Aug 02, 2001 at 11:16 UTC
I don't know of any other algorithm for that. However, I can offer a less verbose syntax. `foreach $key (@keys) { $sth->execute($key); $columns_ref=[@{$sth->fetchrow_arrayref()}]; push(@entries, $columns_ref); }` [download] I'm not sure if that'll save you much in performance, but it does save screen space. (Plus it avoids a couple extra symbol table entries, which can't be a bad thing, right?) =cut --Brent Dax There is no sig.	[reply] [d/l]