Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

DBI::st=HASH output

by DrAxeman (Beadle)
on Aug 06, 2005 at 00:39 UTC ( #481411=perlquestion: print w/ replies, xml ) Need Help??
DrAxeman has asked for the wisdom of the Perl Monks concerning the following question:

Great Monks,

I've progressed with my quest for a script to calculate the averages of all columns. However, things are not working like I hoped. The final loop in my script "should" create a SELECT statement to calculate the average of the column (except the first one, but I'll try that later). It isn;t working though.
My output is getting close but looks like
ERWWCOMMUNITIES_Memory_Pages_sec, DBI::st=HASH(0x853cf7c) ERWWCOMMUNITIES_NetworkInterface_CompaqEthernet_FastEthernetAdapter_Mo +dule_BytesTotalPERsec, DBI::st=HASH(0x85457e8) ERWWCOMMUNITIES_NetworkInterface_MSTCPLoopbackinterface_BytesTotalPERs +ec, DBI::st=HASH(0x8545014)
My script:
#!/usr/bin/perl #use strict; use warnings; use DBI; use Text::CSV; # Connect to the database, (the directory containing our csv file( +s)) my $dbh = DBI->connect("DBI:CSV:f_dir=.;csv_eol=\n;"); # Associate our csv file with the table name 'results' $dbh->{'csv_tables'}->{'results'} = { 'file' => 'test.csv'}; #my ($avg) = $dbh->selectrow_array("SELECT avg(ERWWCOMMUNITIES_Mem +ory_Pages_sec) FROM results"); my $file = 'test.csv'; my $csv = Text::CSV->new(); open (CSV, "<", $file) or die $!; while (<CSV>) { next if ($. != 1); if ($csv->parse($_)) { @columns = $csv->fields(); } else { my $err = $csv->error_input; print "Failed to parse line: $err"; } } foreach $field (@columns) { ##print ("\$avg \= \$dbh\-\>selectrow_array\(\"SELECT avg\($fi +eld\) FROM results\"\) \n"); $avg = $dbh->prepare("SELECT avg($field) FROM results"); print ("$field, $avg \n"); } ###print ("$avg,\n"); close CSV;
What am I doing wrong? Is there an easier way than what I've figured out?

Comment on DBI::st=HASH output
Select or Download Code
Replies are listed 'Best First'.
Re: Database processing
by davidrw (Prior) on Aug 06, 2005 at 01:16 UTC
    You're already reading the file using DBD::CSV, so reading again w/Text::CSV is redundant. You can (db-independently) get the column names with a SELECT query, and then create another SELECT for the avg's (which you can do in one call). (note: untested)
    my $sth = $dbh->prepare("SELECT * FROM results WHERE 1=0"); $sth->execute; my @cols = @{$sth->{NAME}}; my $avgSQL = 'SELECT ' . join(', ', map { "avg($_) as $_" } @cols ) . ' FROM results'; my $avgsHashRef = $dbh->selectrow_hashref($avgSQL); use Data::Dumper; print Dumper $avgsHashRef;

    Note that in your code $avg = $dbh->prepare(...) line didn't actually execute the code. Simply doing $avg = $dbh->selectrow_array(...) instead work would have done want you intended.
      I'm getting an error that I can't figure out:
      SQL ERROR: Bad set function before FROM clause. Execution ERROR: No command found!. $VAR1 = undef;
      Here's the code, just to make sure I'm using it right.
      #!/usr/bin/perl use strict; use warnings; use DBI; #use Text::CSV; use Data::Dumper; # Connect to the database, (the directory containing our csv file( +s)) my $dbh = DBI->connect("DBI:CSV:f_dir=.;csv_eol=\n;"); # Associate our csv file with the table name 'results' $dbh->{'csv_tables'}->{'results'} = { 'file' => 'test.csv'}; my $sth = $dbh->prepare("SELECT * FROM results WHERE 1=0"); $sth->execute; my @cols = @{$sth->{NAME}}; my $avgSQL = 'SELECT ' . join(', ', map { "avg($_) as $_" } @cols ) . ' FROM results'; my $avgsHashRef = $dbh->selectrow_hashref($avgSQL); print Dumper $avgsHashRef;
        hmm... print out $avgSQL and make sure it's valid sql. If there's anything funky (/\W/) in the column names they'll need to be escaped.
        Your script works for me (maintainer of DBD::CSV and SQL::Statement) with a smaller dataset. Could you let me know which version of SQL::Statement you have? If you have anything ealier than 1.14, you should probably upgrade.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://481411]
Approved by planetscape
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (16)
As of 2015-07-29 17:02 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (266 votes), past polls