DBI queries with same results (mysql)

[Rodrigo]

Hi all,

I created a script that query a Database in mysql. Everything goes
right except the fact that the results for various queries are the
same. I use $sth->finish() and $dbh->disconnect() correctly.

I don't know what is going wrong because when i use another script
to query my database (written in php), the results of the queries are
different (they are updated).

How can i write an perl script that give me the right results? I've
read a lot of docs about DBI, and all of them tell me the same thing.

Here's a piece of my code:

---
$drh = DBI->install_driver('mysql');

#$dsn = "DBI:$driver:database=$database";
$dsn = "DBI:$driver:dbname=$database";
$dbh = DBI->connect($dsn, $user, $password) or die "Nao consegui
conectar ao banco de dados, motivo: $DBI::errstr\n ";

#$sth = $dbh->prepare("SELECT count(ip_dst),ip_src,ip_dst FROM
acid_event GROUP BY ip_dst");
my $sql = qq{SELECT count(ip_dst),ip_src,ip_dst FROM acid_event GROUP
BY ip_dst};
$sth = $dbh->prepare($sql);

if (!$sth) {
die "Erro na query:" . $dbh->errstr . "\n";
}

if (!$sth->execute()) {
die "Erro na execucao da query:" . $sth->errstr . "\n";
}


my $names = $sth->{'NAME'};
my $numFields = $sth->{'NUM_OF_FIELDS'};

# Imprime os dados da tabela
my $count0 = 0;
my $count2 = 0;
my @contador = ();
my @ip_destino = ();

while (my $ref = $sth->fetchrow_arrayref) {
for (my $i = 0; $i < $numFields; $i++) {
if ($i == 0) {
$contador[$count0] = $$ref[0];
}
if ($i == 2) {
$ip_destino[$count2] = $$ref[2];
}
$contadorh{$$ref[0]} = $contador[$count0];
$ip_dst{$$ref[0]} = $$ref[2];
$count0++;
$count2++;
}#for
}#while

# Release the statement handle resources
$sth->finish;

# Disconnect from the database
$dbh->disconnect;

 

[Gerard Oberle]

If you run your script twice, and get the same results, then it
doesn't sound as though it has anything to do with releasing
resources.

However, I noticed something suspicious in your query. I am not sure
this accounts for your problem, but I believe it is wrong anyway. In
the statement

my $sql = qq{SELECT count(ip_dst),ip_src,ip_dst FROM acid_event GROUP
BY ip_dst};

Your select list includes ip_src and ip_dst, but only groups by
ip_dst. I am surprised that this even runs at all, since the database
engine has no way of knowing how ip_src and ip_dst relate to one
another.

Otherwise, I don't see anything wrong, either.

- Jerry Oberle
perl -e 'printf "mailto%c%s%c%s%cchase%ccom%c", 58, "Gerard", 46,
"Oberle", 64, 46, 10;'