Weird DBI/Array Use

[madhatter]

I'm trying to run all my DBI calls through a subroutine:

sub db_execute {
    my(@return,@a,$cursor);
    unless($VARS{connected}){db_connect();$VARS{connected}++;}
    undef @return;
    $cursor = $dbh->prepare($_[0]);
    $cursor->execute || db_error();
    unless($_[1]){
        while (@a = $cursor->fetchrow){push (@return,@a)}
        return (@return);
    }
}

The idea is to get it to automatically handle the errors. The connect and error subs work.

What I want to do is push to @return a reference (I think) to @a, so later I can call something along the lines of:

my $SQL =  "select a,b,c from table";
my @lines = db_execute($SQL);
while( ($a,$b,$c) = @lines ){
     ..
}

What changes do I have to make above to get this to work?

Thanks,
madhatter

[salvadors]

Rather than rolling your own version of this, perhaps you could use $dbh->selectall_arrayref instead?

Then you can do something like:

sub db_execute {
  ...
  return $dbh->selectall_arrayref($query);
}

my $result = db_execute($SQL);
foreach my $row (@$result) {
  my ($a, $b, $c) = @row;
  ...
}

Note that this way you can also skip the 'prepare' stage you're doing, as it seems pretty pointless the way you're doing it (you're not gaining any of the benefit of preparing frequently-used queries by doing it inside this sub). However, if you move the 'prepare' elsewhere, and pass this subroutine a prepared query instead, selectall_arrayref will still handle this just fine.

Tony

[chipmunk]

How about:

sub db_execute {
    my(@return, @a, $cursor);
    unless ($VARS{connected}) {db_connect(); $VARS{connected}++;}
    $cursor = $dbh->prepare($_[0]) || db_error();
    $cursor->execute || db_error();
    unless ($_[1]) {
        return @{ $cursor->fetchall_arrayref };
    }
}

my $SQL = "select a, b, c from my_table";
my @rows = db_execute($SQL);
for (@rows) {
  my @row = @$_;
  ...
}

(I'm not sure what the unless($_[1]) is for...)

Update: Use fetchall_arrayref() instead of looping over fetchrow().

[madhatter]

If I know that my query is going to return only one entry, how do I avoid using a loop to gather the returned data? Anything I try just returns me an array reference.

my $result = db_execute($SQL);
foreach(@$result){
    my ($title,$body) = @$_;
    print $title;
}

Thanks,
madhatter

[Fastolfe]

If you have an array reference that you "know" will only contain one item, then it sounds like you just want to access the first element of the array:

my $result = db_execute($SQL)->[0];
my ($title, $body) = @{$result};

# or

my ($title, $body) = @{db_execute($SQL)->[0]};

Check out perlref, perllol and especially perldsc for information about references and the ways you can use them.

[repson]

If you only want one row then do use
my @result = $dbh->selectrow_array("SELECT MAX(foo) FROM blah");

If you just want one column then
my @rows = @{ $dbh->selectcol_arrayref("SELECT id FROM blah") };

Otherwise do a full $dbh->selectall_arrayref as salvadors suggests or use the full prepare, execute, fetchrow syntaxes like you were doing originally.

For information on these syntaxes and more read up on the DBI docs.