Script failing

[NatPres]

This part of a wider script is ment to remove a selection of queries from the database,

The incoming variable is the id number seperated by a : is 98223:

the script just executes the first command and fails of the rest.

$delme = $in{'delme'};
@delme = split(/:/, $delme);
print $q->header;
foreach $dele(@delme)
{
$sqlQuery = qq~DELETE FROM enquiries WHERE id = "$dele" and company_id = "$SESSION{'company_id'}"~;
$result = &executeSQL($dbh, $sqlQuery);
print qq~$sqlQuery $result<br>~;
}

And the results which are provided by the print command, The first one runs ok and the others fail.

DELETE FROM enquiries WHERE id = "343" and company_id = "1" 1
DELETE FROM enquiries WHERE id = "344" and company_id = "1" 0E0
DELETE FROM enquiries WHERE id = "345" and company_id = "1" 0E0
DELETE FROM enquiries WHERE id = "347" and company_id = "1" 0E0
DELETE FROM enquiries WHERE id = "348" and company_id = "1" 0E0
DELETE FROM enquiries WHERE id = "349" and company_id = "1" 0E0

Any ideas anyone?

 

[brigmar]

First thoughts:
Idea 1: 0E0 is being returned if zero rows are affected.
Idea 2: the executeSQL() function is possibly terminating the db connection.

I'm going with Idea 1, as a quick google shows that the value 0E0 is returned by the combination of DBI/mySQL when zero rows are affected by a non-SELECT statement.

 

[NatPres]

your idea about the executeSQL() being terminated is a valid point but as each time the foreach runs, it should be running the executeSQL() seprately. (shouldn't it?).

0E0 is being returned if zero rows are affected.
this is true i also did the google bit on this. As said in my first post only the first query is being run.

 

[brigmar]

One way to check.. change the order of the colon delimited string.
instead of
345:344:345:347:348:349
try shuffling the numbers
344:348:349:343:345;347

If the first iteration works, and subsequent ones fail, then it's a db connection issue.

If the 0E0's still match to their respective id's, then I'd have to say that there is no row in the db matching that criteria.

To further check that, change the DELETE to a SELECT, and iterate through the rows returned to count the number of rows that would otherwise be deleted.

 

[stevexff]

What do you get from

my $sql = qq~DELETE FROM enquiries WHERE id = ? and company_id = "$SESSION{'company_id'}"~;
my $sqh = $dbh->prepare($sql);

foreach (@delme) {
   my $result = $sqh->execute($_);
   print "$_ : $result<br>";
}

It would be good to see the code in &executeSQL()...

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]

 

[NatPres]

763 :
765 :
766 :
767 :
768 :

This is the result?
it's showing the id numbers of the records selected for delete

 

[stevexff]

OK, so we've established that $result doesn't get set to anything sensible. Did it delete the rows?

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]