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>
