Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

DBI and fetchall_arrayref

by Anonymous Monk
on Feb 19, 2009 at 17:26 UTC ( #745134=perlquestion: print w/ replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hello monks!

I'm racking my brains here...

I'm using DBI to access a mysql database. I execute a simple select (select b from blah where user = bahbah) and put it in;

my $result = $sth->fetchall_arrayref({});

Now, I need to check whether there is anything in it or not. I had tried getting the value of;

@$result[0]->{'b'}

But Perl tells me;

Can't use an undefined value as a HASH reference


I'm hoping for some insight here.

Thanks all.

Comment on DBI and fetchall_arrayref
Select or Download Code
Re: DBI and fetchall_arrayref
by perlesque (Initiate) on Feb 19, 2009 at 17:44 UTC
    Try checking how many rows are returned first:
    if ( scalar @$result > 0) { ... process rows ... print $result[0]->{'b'}."\n"; } else { ... no rows returned logic ... }

      Small correction:

      print $result->[0]->{'b'}."\n";
        Small correction

        Actually, $result->[0]{b} will work just fine as well. You don't need to dereference explicitly after the first one, nor do bareword keys need to be quoted.

        Concision, concision!

        • another intruder with the mooring in the heart of the Perl

        you don't even need arrows or periods :)
        #!/usr/bin/perl -- use strict; use warnings; my $result = [ { b => 'small erection' } ]; print $result->[0]->{'b'}."\n"; print $result->[0]{b}."\n"; print "$$result[0]{b}\n"; print "$result->[0]{b}\n"; __END__ small erection small erection small erection small erection
Re: DBI and fetchall_arrayref
by hbm (Hermit) on Feb 19, 2009 at 17:54 UTC

    From what I've read and from my recent experience with DBI, you'll have an easier time if you bind your columns. You can do something like this:

    my $b; my $sql = qq{SELECT b FROM blah WHERE user = ?}; my $sth = $dbh->prepare("$sql"); $sth -> execute("bahbah"); $sth -> bind_col(1,\$b); while( $sth->fetchall_arrayref ) { print "$b\n" if $_; }

    Update: Indeed, runrig, that was bad. I munged the following two methods (perhaps neither of which fully applies since the OP is fetching hashrefs.)

    my $b; my $sql = qq{SELECT b FROM blah WHERE user = ?}; my $sth = $dbh->prepare("$sql"); $sth -> execute('bahbah'); $sth -> bind_col(1,\$b); while( $sth->fetch ) { print "$b\n"; } ################ # OR: ################ my $sql = qq{SELECT b FROM blah WHERE user = ?}; my $sth = $dbh->prepare("$sql"); $sth -> execute('bahbah'); my $rows = []; # cache for batches of rows while(my $row = shift(@$rows) || shift(@{$rows=$sth->fetchall_arrayref([0],10_000)|| +[]})) { print "$row->[0]\n"; }

    Incidentally, while both produce the same lists, do you know why the latter also gives "ERROR no statement executing (perhaps you need to call execute first)"?

      First, the fetchall_* methods fetch all of the rows, so you usually don't see fetchall in the conditional of while loops. Second, $_ isn't set anywhere in your code, so I don't know why you're testing it (the while() does not set it).
        First, the fetchall_* methods fetch all of the rows

        Not always true. The fetchall_arrayref takes a $max_rows optional argument which limits the number of rows fetched in one go to $max_rows. The example shown was in fact using $max_rows. e.g., from the DBI docs:

        If $max_rows is defined and greater than or equal to zero then it is used to limit the number of rows fetched before returning. fetchall_arrayref() can then be called again to fetch more rows. This is especially useful when you need the better performance of fetchall_arrayref() but don't have enough memory to fetch and return all the rows in one go.

        Here's an example (assumes RaiseError is enabled):

        my $rows = []; # cache for batches of rows while( my $row = ( shift(@$rows) || # get row from cache, or reload ca +che: shift(@{$rows=$sth->fetchall_arrayref(unde +f,10_000)||[]}) ) ) { ... }
Re: DBI and fetchall_arrayref
by runrig (Abbot) on Feb 19, 2009 at 18:11 UTC
    The docs say:
    If there are no rows to return, "fetchall_arrayref" returns a reference to an empty array.
    So just testing for if (@$result) {...} should tell you if there are results.
Re: DBI and fetchall_arrayref
by bradcathey (Prior) on Feb 20, 2009 at 14:28 UTC

    BTW and slight OT, I gave up on "fetching" a couple of years ago after I read gmax's wonderful DBI Recipes. No prepping or executing needed.

    my $stmt = qq/SELECT * FROM mytable WHERE id = ?/; my $result = $dbh->selectall_arrayref($stmt, {Slice => {}}, $subscribe +r_id);

    You end up with an AoH, which is perfect for me as I'm usually populating a loop in HTML::Template

    —Brad
    "The important work of moving the world forward does not wait to be done by perfect men." George Eliot
Re: DBI and fetchall_arrayref
by Anonymous Monk on May 04, 2009 at 13:49 UTC
    See perldoc DBI

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://745134]
Front-paged by Arunbear
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (10)
As of 2014-12-19 09:48 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (77 votes), past polls