Perl numeric in mysql query 2

[jpillonel]

Hello,

I use the DBI module to connect to a mysql database.

First I run a count on a table and I like, if the result of these is query is not 0, to run a delete on this table.

I do this, but I have an error:

Use of uninitialized value in numeric ne (!=) at mysql_epur_function.pl line 116
.

CODE :

$countrowstodelete = $dbh ->prepare("
SELECT count(*) FROM $_[0]
WHERE TO_DAYS(NOW()) - TO_DAYS(eventtimestamp) > 120");
$countrowstodelete->execute;
while ( ($nbcountrowstodelete) =$countrowstodelete->fetchrow() ) {
print "Number of rows to delete $nbcountrowstodelete !\n" ; } ;

if ($nbcountrowstodelete != '0')
{
$deleterows = $dbh ->prepare("
delete from transportlog
WHERE TO_DAYS(NOW()) - TO_DAYS($_[1]) > $_[2]");
$deleterows->execute;
print "Rows deleted\n";
}
else {
print "No rows to delete \n";
};

The problem comes from the if statement because it seams tha the result of the query is not numerci ???

if ($nbcountrowstodelete != '0')

Thanks for your help

 

[PaulTEG]

if ($nbcountrowstodelete != 0)
or
if ($nbcountrowstodelete ne '0')

HTH



Paul
------------------------------------
Spend an hour a week on CPAN, helps cure all known programming ailments ;-)

 

[jpillonel]

I had already tried with

if ($nbcountrowstodelete != 0)
or
if ($nbcountrowstodelete ne '0')

and I have the same problem

Thanks but it doesn't works

 

[PaulTEG]

same error, or same problem?

Paul
------------------------------------
Spend an hour a week on CPAN, helps cure all known programming ailments ;-)

 

[jpillonel]

I have the same error and the same problem.

I'm not a Perl programmer and I really don't know why ...

 

[PaulTEG]

Is it printing "Rows deleted\n"; at all.

I think this might be the last case of the while. If it's a numeric, !=, if it's a string then ne.

Paul
------------------------------------
Spend an hour a week on CPAN, helps cure all known programming ailments ;-)

 

[stevexff]

Am I missing the point here? You run a SELECT COUNT(*) on the table to see if there are any qualifying rows to delete. This only ever returns 1 row. Then we have a while loop to read the single row, with a conditional delete in the middle.

Can't you just run DELETE FROM $_[0]
WHERE TO_DAYS(NOW()) - TO_DAYS(eventtimestamp) > 120"); and be done with it? SQL DELETE is a set-level action. If there are any qualifying rows, they will be deleted. If not, not.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[cdlvj]

SELECT count(*) [b] as $ct [/b] FROM $_[0]

 

[cdlvj]

sorry,

test $ct for 0.

 

[jpillonel]

stevexff:
Some tables have more than 10'000'000 rows, for that I dont' want ro run a delete on the table if it's not necessary. I must run a count to know the number of lines present before --> if not equal a 0 (different than 0) --> run the delete --> and after the script execution, count the number of lines. But you're right, I don't need to run a loop to fetch a single row --> how to do that ?

cdlvj :
what do you mean, using a variable in the AS statement ?

Thanks for your help !

 

[stevexff]

OK. When you run a COUNT(*) on the table with a WHERE clause, the DBMS has to read all the rows in the table to see if they meet the criteria. If you are lucky and all the items you specify are in indexed columns on a single index, then the optimiser might choose to read the index instead.

It follows exactly the same process on the DELETE statement, so as far as I can see all you are doing is doubling the work the DBMS has to do when there are qualifying rows for deletion.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[cdlvj]

That is not necessarily true, as some DB's use some internal stuff to return the count.

I have run select count on tables with 100's of thousands of rows, and it returns the result in macroseconds.

 

[stevexff]

Agreed, if all you are doing is a straight 'how many rows on the table' type COUNT(*). But in this case we are using a WHERE clause, so there is no other way for the DBMS to do it.

Also, did you mean 'macroseconds' or 'microseconds'?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[jpillonel]

In a french forum, somebody give me this code and it works fine. Creating the variable with my and by doesn't fetching the row in a loop ... works fine.

Thank everybody for your help

my $countrowstodelete = $dbh->prepare ("SELECT count(*) FROM $_[0] WHERE TO_DAYS(NOW()) - TO_DAYS(eventtimestamp) > $_[2]");
$countrowstodelete ->execute();
my $nbcountrowstodelete = $countrowstodelete->fetchrow() ;
$countrowstodelete->finish();

if ($nbcountrowstodelete > 0){
print "Nombre de lignes a supprimer $nbcountrowstodelete !\n" ; # Affiche le nombre de lignes à supprimer
$deleterows = $dbh ->prepare("delete from transportlog WHERE TO_DAYS(NOW()) - TO_DAYS($_[1]) > $_[2]");
$deleterows->execute;
}
else {
print "Aucune ligne a supprimer \n";
};