Re: DBI and fetchall

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 $_;
   }
[download]

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";
   }
[download]

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)"?

Comment on Re: DBI and fetchall_arrayref Select or Download Code

Replies are listed 'Best First'.
Re^2: DBI and fetchall_arrayref by runrig (Abbot) on Feb 19, 2009 at 18:05 UTC
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).	[reply]
Re^3: DBI and fetchall_arrayref by mje (Curate) on Feb 20, 2009 at 13:21 UTC
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)\|\|[]}) ) ) { ... }` [download]	[reply] [d/l]
Re^4: DBI and fetchall_arrayref by hbm (Hermit) on Feb 20, 2009 at 13:37 UTC
The example shown was in fact using $max_rows In fairness, I added $max_rows as an update, after runrig replied. mje, are you familiar with the error I mentioned? Thanks!	[reply]