Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

DBI::st=HASH output

by DrAxeman (Scribe)
on Aug 06, 2005 at 00:39 UTC ( [id://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?

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
Domain Nodelet?
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?Last hourOther CB clients
Other Users?
Others scrutinizing the Monastery: (5)
As of 2024-03-28 17:42 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found