Hi, thanks Marcello :)
I finally figured it out by painfully following 10 sets of data through
the process.
First, this was causing two problems:
#now cycle through result set in next query
foreach $myhsid (@hsid)
{
$myhport = pop(@hport);
$myhmac = pop(@hmac);
print "trying $myhsid $myhport $myhmac\n";
Foreach was only iterating through UNIQUE instances of $myhsid. So the
whole dataset was not used. Also I was pop'ing two values from the
set, while foreach was iterating through the other array TOP DOWN,
where pop goes BOTTOM UP. D'oh!
So I ended up with something like:
for ([EMAIL PROTECTED]; $count !=0; $count--) {
$myhsid=pop(@hsid);
So now I get the count from @hsid, and then pop all arrays so the
datasets stay matched :)
Unfortunately the database is huge. When I left work Friday my script
was still running after about 30 hours. It looks like 'order by hdate
desc limit 1' is a LOT SLOWER than 'order by hdate limit 1' for some
reason. Must be internal MySQL stuff...
Thanks,
Lincoln
From: Marcello <[EMAIL PROTECTED]>
Date: October 5, 2005 11:35:16 AM EDT
To: beginners@perl.org
Subject: Re: mysql through dbi problem
linc ha scritto:
Howdy,
I'm having some serious trouble. I'm running a query and getting the
data, but having problems using that data in the next query. Thus:
#first query
$sth = $dbh->prepare
( "select distinct hmac, hport, hsid from netdata_history" );
$sth->execute( );
$sth->bind_columns
( undef, \$hmac, \$hport, \$hsid );
while ( ( $hmac, $hport, $hsid ) = $sth->fetchrow_array )
{
push @hsid, $hsid;
push @hport, $hport;
push @hmac, $hmac;
}
You are using bind_columns and fetchrow_array together.
I bet this is causing you trouble.
DBI.pm documentation (bind_columns section) suggests something like:
my ( $hmac, $hport, $hsid );
$sth->bind_columns( \$hmac, \$hport, \$hsid );
while($sth->fetch) {
push(@hsid, $hsid);
push(@hport, $hport);
push(@hmac, $hmac);
}
Otherwise, leave out the bind_columns part:
my ( $hmac, $hport, $hsid );
while ( ( $hmac, $hport, $hsid ) = $sth->fetchrow_array ) {
push(@hsid, $hsid);
push(@hport, $hport);
push(@hmac, $hmac);
}
$sth->finish();
#now cycle through result set in next query
foreach $myhsid (@hsid)
{
$myhport = pop(@hport);
$myhmac = pop(@hmac);
print "trying $myhsid $myhport $myhmac\n";
$sth = $dbh->prepare
( "select * from netdata_history
where hsid=" . $dbh->quote( $myhsid ) . "
and hport=" . $dbh->quote( $myhport ) . "
and hmac=" . $dbh->quote( $myhmac ) . "
order by hdate limit 1" )
or die "Can't execute SQL statement: $DBI::errstr\n";
$sth->execute( );
$sth->bind_columns
( undef, \$hdateoldest, \$hsidoldest, \$hportoldest, \
$hmacoldest, \$hipoldest )
or die "Can't execute SQL statement: $DBI::errstr\n";
while ( ( $hdateoldest, $hsidoldest, $hportoldest, $hmacoldest,
$hipoldest ) = $sth->fetchrow_array )
{
warn "Data fetching terminated early by error: $DBI::errstr\n"
if $DBI::err;
print "oldest for this combination $hdateoldest\n";
}
$sth->finish();
So, my first query gets the distinct list of unique sets for those
three
fields.
My next query seems to get empty sets returned! In other words,
"oldest
for this combination" never prints. Is there something about using
@hsid as the iterator in the foreach? Is it making the data I send to
the query in $myhsid out of sequence or reverse order from the data in
the other two arrays?
I also wonder if any of the fields I'm returning are null, does that
affect things?
Anyways, I've rewritten this several times. I don't know why my
queries
are coming back with empty sets. Shouldn't it be impossible to get an
empty set since the data is coming from the database in the first
query?
Any pointers appreciated.
Thanks,
Linc
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
<http://learn.perl.org/> <http://learn.perl.org/first-response>
