Howdy:
Perl / DB question -
I am running a PostgreSQL database and would like to
count and sum tables and put that data into a new
table for trending.
I have a perl script that connects to the database and
can do (I *think* this part is working) three separate
queries, but now that I have the data, I want to put them
all into the table at the same time. So, it sounds like
I have to put all of them into one array.
The reason I have the queries separated is because of
the lack of data returned (the SQL portion ran for
over a day and there was no return at all when I merged
the queries into one SQL statement).
So: Now that I have three queries, how can I merge
that data into an array (to put that info into
a table)?
(I hope this makes some sense, somehow.)
## Perl Script ##
#!/usr/bin/perl -w
# created 09 July 02 -X
#
# script to connect to PostgreSQL do a count
# and summarize the i_pd (dollars) from
# three separate tables (2000 - 2002)
# and print the result (maybe just put
# into a file and statically update the
# html page for this type of stuff)
use diagnostics;
use DIB;
#use Net::SMTP;
$date=`date`;
$trendtxt='/tmp/claim_trend.txt';
$addr='[EMAIL PROTECTED]';
## connect to the database and run 3 queries
# because I can not get a return due to
# the sheer size of the tables and lack
# of indices on numeric fields
#
my $dbh=DBI->connect('dbi:Pg:dbname=local', 'joeuser')
or die "Can not connect: $!";
my $sql = qq|
select count (*) as total_cnt_2000,
sum(i_pd) as total_sum_2000
from claimsum2000
| ;
my $xsql = qq|
select count (*) as total_cnt_2001,
sum(i_pd) as total_sum_2001
from claimsum2001
| ;
my $ysql = qq|
select count (*) as total_cnt_2002,
sum(i_pd) as total_sum_2002
from claimsum2002
| ;
## set up the query, check for errors, authenticate
#
my $sth=$dbh->prepare($sql) or die "Error =", DBI::errstr;
unless ($sth->execute) {
print"\n\tExecute failed for stmt:\n\t$sql\nError = ", DBI::errstr;
$sth->finish;
$dbh->disconnect;
die "\n\t\tClean up finished\n";
}
## no idea if this will work
# i want to set this up like the one above
#
my $xsth=$dbh->prepare($xsql) or die "Error =", DBI::errstr;
unless ($xsth->execute) {
print"\n\tExecute failed for stmt:\n\t$xsql\nError = ", DBI::errstr;
$xsth->finish;
$dbh->disconnect;
die "\n\t\tClean up finished\n";
}
## no idea if this will work
# i want to set this up like the one above
#
my $ysth=$dbh->prepare($ysql) or die "Error =", DBI::errstr;
unless ($ysth->execute) {
print"\n\tExecute failed for stmt:\n\t$ysql\nError = ", DBI::errstr;
$ysth->finish;
$dbh->disconnect;
die "\n\t\tClean up finished\n";
}
## putting data into text file for
# delivery (email)
open COUNTFILE, ">$trendtxt";
print COUNTFILE "Today is: $date\n\n";
print COUNTFILE "Summary of dollars and total count in
Claimsum2000\n\n";
print COUNTFILE "Please review\n\n";
print COUNTFILE "count for 2000\tsum for 2000\tcount for 2001\tsum
for 2001\tcount for 2002\tsum for 2002\n";
# where the problem begins ... how can i create an array
# for sth before THIS point?
#
## --> can i create an array @array=$sth+$xsth+$ysth?
## --> how does that work? can it work that way?
## --> how can i create the below statement from the
## --> statement from above? what am i missing?
# while (my $a, $b, $c, $d, $e, $f) = $array->fetchrow) {
while (my ($count, $sum)=$sth->fetchrow) {
print COUNTFILE "$count\t$sum\n";
# insert data into table
$dbh->do("insert into t_claimsum_trend values (
'$count',
'$sum',
null,
null,
null,
null,
now() )");
}
close COUNTFILE;
# maybe i should mail the results
$sendmailcount = "/usr/sbin/sendmail $addr < $trendtxt";
print `$sendmailcount`;
$dbh->disconnect;
## end of broken perl script##
Suggestions? Thanks!
-X
