madhatter has asked for the wisdom of the Perl Monks concerning the following question:

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);
    }
}

[download]
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 ){
     ..
}

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

Thanks,
madhatter

Replies are listed 'Best First'.
Re: Weird DBI/Array Use
by salvadors (Pilgrim) on Jan 08, 2001 at 01:28 UTC

    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;
  ...
}

    [download]

    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

[reply]
[d/l]
[select]
Re: Weird DBI/Array Use
by chipmunk (Parson) on Jan 08, 2001 at 01:24 UTC
    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 = @$_;
  ...
}

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

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

[reply]
[d/l]
[select]
Re: Weird DBI/Array Use
by madhatter (Sexton) on Jan 08, 2001 at 03:29 UTC
    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;
}

    [download]
    Thanks,
    madhatter
[reply]
[d/l]
      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]};

      [download]
      Check out perlref, perllol and especially perldsc for information about references and the ways you can use them.
[reply]
[d/l]
      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.

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom