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

Hi,

I have the following code, which extracts information from a table:

$select = "select recno,name from TABLE;";
$result=$db->exec("$select");

my @row;
while (@row = $result->fetchrow)
{
   $recno = $row[0];
   $name  = $row[1];
}

[download]

Its working well, but it is extracting only the last record. My table has 10 records, and I need to use all of these later on my program. Some of you know how can I do that?

I tried to define a temporary scalar (ranging from 1 to 10) and create another scalar using the first one ($RECNO$TEMP, where $RECNO0 = $row[0], $RECNO1 = $row1, and so one), but didnt work. Apparently, I can not create scalar using another scalar in Perl. Am I correct?

I tried to create a composed array, but it can not be used in Perl.

Thanks for any information on this.

Marcelo

lmachite@dir.iai.int

lmachite@terra.com.br

Replies are listed 'Best First'.
Re: How to preserve all instances from a database in an array?
by lima1 (Curate) on Mar 23, 2006 at 21:27 UTC 
    my @rec_data;
while (my $data = $result->fetchrow_hashref) {
    push @rec_data, [ $data->{rec_no}, $data->{name}];
}
for my $i ( 0 .. 9 ) {
    print $rec_data[$i]->[0];
    print $rec_data[$i]->[1];
}

    [download]
    (did not compile this code) UPDATE: davidrw is of course right. I focused on your array question. the point is to push a reference in an array. you should read perldoc perlref.
[reply]
[d/l]
      no point in using fetchrow_hashref if you're a) getting all the rows ("selectall" instead of "fetchrow") and b) want an array ("arrayref" instead of "hashref"): 
      my @rec_data = @{ $db->selectall_arrayref($select) };
foreach my $i ( 0 .. $#rec_data ){
  printf "%d) (recno,name)=(%s, %s)\n", $i+1, @{$rec_data[$i]};
}

      [download]
[reply]
[d/l]
[select]
Re: How to preserve all instances from a database in an array?
by ptum (Priest) on Mar 23, 2006 at 21:38 UTC

    Your best bet is to read over DBI and become familiar with the various methods there. In particular, you might find selectall_arrayref suitable for this situation, which returns a reference to an array of array references (say that five times fast!):

    use strict;
use warnings;
my $statement = "select foo, bar from mytable";
my $ary_ref  = $dbh->selectall_arrayref($statement);
if ($DBI::errstr) {
  # do something, your select failed!
}

my @rows = ();
@rows = @{$ary_ref} if ref($ary_ref) eq 'ARRAY';
foreach my $thisrow (@rows) {
  my @columns = ();
  @columns = @{$thisrow} if ref($thisrow) eq 'ARRAY';
  foreach (@columns) {
    print "$_\t";
  }
  print "\n";
}

    [download]

    (Code untested.)

    Update: One of the reasons your code didn't work was because you were storing your result row in two variables that immediately went out of scope, and you were overlaying the contents of @row each time through the loop. If you 'use strict;' and 'use warnings;' some of these things will become more obvious to you.

    In my example above, I dereference the outer array reference to get the rows, and then for each row, I dereference again to get the columns. I did this deliberately in an explicit manner to show you how the array references work. There are several more compact ways to write this, once you understand what is going on.


    No good deed goes unpunished. -- (attributed to) Oscar Wilde
[reply]
[d/l]
Re: How to preserve all instances from a database in an array?
by johngg (Canon) on Mar 23, 2006 at 23:22 UTC
    Perhaps you could try this. I have used it in the past and it worked then.

    use strict;
use warnings;

our @saveForLater = ();
our $cursor = $db->prepare("select recno,name from TABLE;");
$cursor->execute();
while(my ($recNo, $name) = $cursor->fetchrow())
{
    # Do something with results
    ...
    # Save data for further processing
    push @saveForLater, [$recNo, $name];
}
...
# Later in the program
foreach my $refToRow (@saveForLater)
{
    print "Record No. - $refToRow->[0]\n",
          "Name       - $refToRow->[1]\n";
    # Or whatever you want to do.
}

    [download]

    I hope this is of use.

    Cheers,

    JohnGG

[reply]
[d/l]

Back to Seekers of Perl Wisdom