sub print_stats
{
my($table) = @_;
my $sth = $dbh->prepare('SELECT * FROM '.$table)
or die "Couldn't prepare statement: " . $dbh->errstr;
$sth->execute # Execute the query
or die "Couldn't execute statement: " . $sth->errstr;
my $names = $sth->{NAME}; # or NAME_lc or NAME_uc
while( my $report_row = $sth->fetchrow_arrayref() ) {
for my $col_idx ( 0 .. $#{$names} ) {
print "$names->[$col_idx]: $report_row->[$col_idx]\n";
}
}
}
No answer to your current problem, but ...
- Don't repeat yourself. DBI can be configured to automatically die on error. Change the DBI->connect(...) call to include RaiseError => 1 in the \%attr parameter and drop the or die ... after each call to a DBI method. (By the way: Did you notice that you don't check for errors from fetchrow_arrayref()? RaiseError=1 would have checked for errors automatically.)
- Don't use SELECT *. It is generally considered bad practice and should be used only for debugging, not for production code. The problem with SELECT * is that the number and the order of the columns returned may change at any time. Explicitly naming the required columns gives a constant order, no surprising extra columns, and it will complain loudly when an expected column suddenly disappears (because someone changed the database schema without changing the program code).
- Quote identifiers, even if that seems to be optional. $table is not quoted at all. This is not a big problem as long as $table contains only "friendly" names (matching /^[A-Za-z][A-Za-z0-9]*$/) that do not need quotes. But it is a big problem when the name needs quotes; and it is a huge problem if $table can be changed by users to contain something malicious. Hello, Bobby Tables. The proper, database-independant way to quote database identifiers like table names and column names is to use $dbh->quote_identifier(...). This way, any malicious value for $table will simly end in an error message complaining that the table could not be found.
- (Related to the previous point, but not in the code you posted:) Always pass values to the database via placeholders, never via string interpolation or string concatination. Only placeholders allow reusing prepared statements with different values. And only placeholders can avoid the messy quoting business, because SQL statement and values can be transmitted to the database on separate ways that don't need no quoting. For databases that insist on getting the values inside the SQL command, DBI and the database-specific DBD will handle quoting the values for you, so even then, use placeholders.
- Indent your code properly. Your future self may be tempted to invent a time machine and hurt you for the mess you are writing now. perltidy can help you preventing temporal paradoxa.
Alexander
--
Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)