There is an easier way to do what you want. DBI has a bunch of utility methods which replace common actions. In fact, more than half your code can be replaced with a single method call.

You should try replacing this:

$sth5 = $dbh->prepare("SELECT round,department,score FROM  trees WHERE
+ sport = \'$sport\' AND year = \'$year\' ORDER BY round DESC")|| die 
+"couldn't  prepareSQL statement";
$sth5->execute || die "can't execute sql statement";

$rowcount = 0;
while (@row = $sth5->fetchrow_array()) {
  $round = $row[0];
  $teamname = $row[1];          
  $Score    = $row[2];          

  push @list_of_teamdata, qw("$round $teamname $score");
    $rowcount++;

  }
[download]

With this: (Note: code is spread out for better formatting)

my $statement = q{
    SELECT round
         , department
         , score
      FROM trees
     WHERE sport = ?
       AND year = ?
  ORDER BY round DESC   
};

my $list_of_teamdata = $dbh->selectall_arrayref(
  $statement,
  {},
  $sport,
  $year,
);
[download]

Here's what I did:

• Used placeholders in the SQL query, which are the question marks. By placing these in the query we are telling DBI and/or the database that we are going to fill that information in later.

  As you can see, in selectall_arrayref, the $sport and $year are passed in as the third and fourth arguments, in the order corresponding to the SQL query. If you had more placeholders, you could pass in more arguments.

• Replaced the while loop with a single call to selectall_arrayref. The sequence of commands you are doing is a very common way of fetching data out of the database, and was placed in a single method to make our lives easier.

  IMHO, DBI's utility methods are underused and a few minutes familiarizing with them could not only save alot of typing, but simplify your code considerably, because they produce consistent results every time.

Comment on (dkubb) Re: (2) more dbi issues Select or Download Code