DBI, shortcut for fetchrow array

Forum Moderators: coopster & phranque

Message Too Old, No Replies

DBI, shortcut for fetchrow array

csdude55

6:01 pm on Sep 11, 2021 (gmt 0)

In trying to optimize my code, I discovered that I was using an unnecessarily long method of prepare -> execute -> fetchrow_array when getting 1 row of information. It's faster to code and process to use:

($colA, $colB) = $dbh->selectrow_array("SELECT * FROM table WHERE foo=? LIMIT 1", undef,
 'bar');

Which function is best when I need to retrieve multiple rows, though? I'm currently using the lengthier method, so I'm just trying to shorten it (and wouldn't mind if it processes faster):

$sth = $dbh->prepare("SELECT * FROM table WHERE foo=? LIMIT 10");
$sth->execute('bar');

while (($colA, $colB) = $sth->fetchrow_array()) {
 # whatever
}

csdude55

5:43 am on Sep 12, 2021 (gmt 0)

After poking and pushing wires around, I've figured out 2 alternatives. But both are ugly, so I'm not sure if they're really "better".

# Alternative 1
@sth = $dbh->selectall_array("SELECT * FROM table WHERE foo=? LIMIT 10", undef,
 'bar');

foreach $key (@sth) {
 ($colA, $colB) = @$key;
 print "$colA => $colB\n";
}

# Alternative 2
$sth = $dbh->selectall_arrayref("SELECT * FROM table WHERE foo=? LIMIT 10",
 { Slice => {} },
 'bar');

foreach $key (@$sth) {
 print "$key->{colA} => $key->{colB}\n";
}

If someone can suggest how to compress this to a one-liner, then it might be the smoothest:

foreach $key (@sth) {
 ($colA, $colB) = @$key;

I know that this won't work:

foreach ($colA, $colB) (@sth) {