Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW

use of DBI perl function fetchall_arrayref

by kmullin5016 (Acolyte)
on Jan 25, 2007 at 22:05 UTC ( #596638=perlquestion: print w/replies, xml ) Need Help??
kmullin5016 has asked for the wisdom of the Perl Monks concerning the following question:

I'm using fetchall_arrayref to access an Oracle database. I want to retrieve all the rows retrieved by a SQL query and then use the map function to process the entire array before displaying. Now, my doc says it returns a reference to an array, and this array contains references to other arrays. That's where I'm stuck. I don't know how to access them. Here is my test code which is trying to just print the info from the query, one column at a time.

my $sql_return = $sth_tss->fetchall_arrayref; print $CGI->p("sql_return is ", $sql_return); foreach $row ([$sql_return]) { foreach $ref ([$row]) { foreach $field ([$ref]) { print $field; } } }

At the first print print locations, I just see:


and the second (that is within the triply nested foreach loop) I see


Note: its a different address and I'm only getting one print. I hope I'm being clear on what I want to do. Can some one help on this?

Replies are listed 'Best First'.
Re: use of DBI perl function fetchall_arrayref
by imp (Priest) on Jan 25, 2007 at 22:18 UTC
    You were close.
    # Create an anonymous arrayref my $arrayref = [1,2,3]; # Create a reference to an existing array my @array = (1,2,3); my $arrayref2 = \@array; # Get a single value from the arrayref: my $one = $arrayref2->[0]; # Get all the values from the arrayref: my ($one,$two,$three) = @$arrayref2;
    In the code you posted you wrote:
    Which is putting the arrayref provided by DBI inside another arrayref. Instead do this:
    It might help you visualize the data structure if you use Data::Dumper, like this:
    use Data::Dumper; #... my $sql_return = $sth_tss->fetchall_arrayref; print Dumper $sql_return;
      and if you want to be explicit in dereferencing, enclose the references in braces. this sometimes helps increase readability:
      # dereferencing an array print @$reference; # EXPLICITLY dereferencing an array print @{$reference};

      The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it.
      - Terry Pratchett

Re: use of DBI perl function fetchall_arrayref
by ysth (Canon) on Jan 25, 2007 at 22:19 UTC
Re: use of DBI perl function fetchall_arrayref
by chakram88 (Pilgrim) on Jan 25, 2007 at 23:28 UTC
    Take a look at the perlref tutorial. Specifically Use Rule 1 applies directly to your question "how to access them"
    Whatever you want to do with a reference, Use Rule 1 tells you how to do it. You just write the Perl code that you would have written for doing the same thing to a regular array or hash, and then replace the array or hash name with {$reference} .
Re: use of DBI perl function fetchall_arrayref
by bradcathey (Prior) on Jan 26, 2007 at 02:54 UTC

    Good questions.

    You absolutely have to read gmax's DBI Recipes (plus anything else you can find of his). Anyway, his recipes outlined in that node have been woven into the fabric of all our code that interacts with MySQL, especially in conjunction with HTML::Template. You will not regret it.

    "The important work of moving the world forward does not wait to be done by perfect men." George Eliot
Re: use of DBI perl function fetchall_arrayref
by graff (Chancellor) on Jan 26, 2007 at 13:39 UTC
    My standard idiom for this sort of thing goes like this:
    my $rows = $sth->fetchall_arrayref; for my $row ( @$rows ) { my @fields = @$row; # do something with @fields... } # or, if the per-field activity is pretty dense: for my $row ( @$rows ) { for my $field ( @$row ) { # do something with $field... } }
    No doubt there are other approaches that some would consider more "elegant" or "clever", but usually when dealing with basic query activity like this, the plain and simple loops with descriptive variable names is just the easiest way.
      Ok, Here is what I've got:
      my $rows = $sth_tss->fetchall_arrayref; $count = 0; print $CGI->p("rows is ", @$rows); for my $row ( @$rows ) { $CGI->p("Row", ++$count); my @fields = @$row; for my $field ( $fields ) { print $CGI->p($field); } }

      and the code it generates is:

      ARRAY(0x40285608) ARRAY(0x402856a4) ARRAY(0x40285740) ARRAY(0x402 RRAY(0x40285878) ARRAY(0x402860e0) ARRAY(0x4028617c) ARRAY(0x40286218) + A 02862b4) ARRAY(0x40286350) ARRAY(0x402863ec) ARRAY(0x40286488) ARRAY(0 +x4 ARRAY(0x40287104) ARRAY(0x402871a0) ARRAY(0x4028723c) ARRAY(0x402872d +8) x40287374) ARRAY(0x40287fbc) ARRAY(0x40288058) ARRAY(0x402880f4) ARRAY +(0 0) ARRAY(0x4028822c) ARRAY(0x402882c8) ARRAY(0x40288364) ARRAY(0x40288 +f3 (0x40288fd8) ARRAY(0x40289074) ARRAY(0x40289110) ARRAY(0x402891ac) ARR +AY 248) ARRAY(0x40289e90) ARRAY(0x40289f2c) ARRAY(0x40289fc8) ARRAY(0x402 +8a AY(0x4028a100) ARRAY(0x4028a19c) ARRAY(0x4028ae14) ARRAY(0x4028aeb0) A +RR 8af4c) ARRAY(0x4028afe8) ARRAY(0x4028b084) ARRAY(0x4028b120) ARRAY(0x4 +02 RRAY(0x4028bddc) ARRAY(0x4028be78) ARRAY(0x4028bf14) ARRAY(0x4028bfb0) + A 028c04c) ARRAY(0x4028c0e8) ARRAY(0x4028d100) ARRAY(0x4028d19c)
      and so on. What am I doing wrong?

        You said that you ran this piece of code., Looking at it you have three print statements that do not appear to be printing out anything similiar to what you posted.

        For example each ARRAY reference should be between p() tags yet it is one long wrapped string.

        Are you sure you looking at the right location for this code?

        This line in your code:
        print $CGI->p("rows is ", @$rows);
        is telling perl to stringify a list of array references and print them out as strings to your web page. And that is exactly what you are seeing. Those "ARRAY(0x....)" things are perl's normal way of converting references into strings.

        Others have pointed other problems with the code, which you might have fixed. Do pay special attention to every "@" and "$", and to the (absence of) spaces around them -- perl is especially picky about those details, because it needs to be.

Re: use of DBI perl function fetchall_arrayref
by ptum (Priest) on Jan 26, 2007 at 17:53 UTC

    It sounds like others have already pointed you to some good resources for derefencing, etc., but there are two things which are conspicuous in their absence from your code:

    • You don't check if the fetchall_arrayref was successful, in terms of displaying $DBI::errstr.
    • You don't check if the array reference is really an array reference.

    I usually do something like this:

    my $ary_ref = $sth->fetchall_arrayref(); if ($DBI::errstr) { print "Error detected: $DBI::errstr\n"; return; } else { if (ref($ary_ref) eq 'ARRAY') { # go on to process the array reference. } else { # do something with Data::Dumper } }
      The fetchall_arrayref method can be used to fetch all the data to be returned from a prepared and executed statement handle. It returns a reference to an array that contains one reference per row.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://596638]
Approved by sweetblood
Front-paged by dbwiz
[Discipulus]: pps this is the chatterbox, not the search. here we have Super Search

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (7)
As of 2017-04-27 16:54 GMT
Find Nodes?
    Voting Booth?
    I'm a fool:

    Results (512 votes). Check out past polls.