Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris

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
[Corion]: Meh. I have a very simple mechanism to scrape+track prices, and even to send me mail if something changes (via cron), but SQLite doesn't support window functions, so my simple SQL to determine a price change won't work :-( Maybe I should store the DB ...
[Corion]: ... in Pg, but that would mean that I'd have to deal with credentials and stuff :)
[Corion]: In fact, the whole thing is just four simple programs, one App::scrape, another being DBIx::RunSQL, and one some glue to convert a JSON object into an SQL INSERT statement (+DBI connect/execute), so it would be an incredibly simple solution...
[Corion]: ... but that simplicity falls down due to SQLite letting me down and me being too lazy to move to a real DB. Maybe DBD::CSV can handle window functions...
[hippo]: Pg has the "trust" mechanism if you don't fancy using credentials for a trivial db.
[Corion]: hippo: Oooh, that sounds quite nice - let me read the documentation on that :)
[hippo]: https://www. docs/current/ static/auth- methods.html#AUTH- TRUST
[erix]: or compile & run an instance as yourself (not user 'postgres')
[erix]: easiest setup is to run with PG* env variables set, and a custom .pgpass files (can be a $PGPASSFILE)
[Corion]: Hmm - "trust" sounds interesting, but I have still to find whether I can restrict that on a per-DB level ;)

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (10)
As of 2018-02-23 15:08 GMT
Find Nodes?
    Voting Booth?
    When it is dark outside I am happiest to see ...

    Results (302 votes). Check out past polls.