revExecuteSQL not returning result with DELETE ALL

[marksmithhfx]

Hi there, I hope you can help me out with a little problem that is driving me crazy.

Database has 3 records in it; object_numbers 1,2,3

The following code works


put "delete from myDB WHERE object_number > 1" into tSQL
revExecuteSQL gdbLibDefaultID, tSQL
put return & the result after msg
refreshDataGrid -- code in card script

And displays "2" for the result (indicating 2 records deleted). The updated DataGrid also now only displays 1 record. All good.

However the following code returns the correct DataGrid result (no records) but 0 for the "the result" instead of 3.

put "delete from myDB" into tSQL -- i.e. delete all of the records
revExecuteSQL gdbLibDefaultID, tSQL
put return & the result after msg
refreshDataGrid -- code in card script

Why's that? It appears a delete without a WHERE clause does not return the number of rows affected. Can anyone independently verify that? Is it a bug?

Thanks

[bangkok]

which database do you use ?

MySQL ? Which version ?

"In MySQL 3.23, DELETE without a WHERE clause returns zero as the number of affected rows. "

http://dev.mysql.com/doc/refman/4.1/en/delete.html

[marksmithhfx]

which database do you use ?

"In MySQL 3.23, DELETE without a WHERE clause returns zero as the number of affected rows. "

http://dev.mysql.com/doc/refman/4.1/en/delete.html

Thanks Bangkok. I was using SQLite which is doing the same thing. Not consistent since the number of rows affected is clearly not 0. I can force it to return the number of rows affected by doing something silly like "DELETE from MyDB WHERE object_number > 0" which is the same thing since object_numbers auto increment from 1. Other suggestions for if you want to delete all the rows and know the number deleted?

Thanks
-- Mark

[mwieder]

It's not clear to me why you want the number of rows if you're going to delete all of them anyway, but...

Code: Select all

revDataFromQuery(tab,return,gdbLibDefaultID,"select count() from myDB")

should give you the number of rows in the myDB table. Then do your delete command.

[marksmithhfx]

should give you the number of rows in the myDB table. Then do your delete command.

Hi Mark,

It was more for confirmation during development:

revExecuteSQL myConnectID, mySQL
put the result into tResult
if tResult > 0 then
answer tResult && "records deleted"
else
answer error tResult
end if

If I delete a few records (using a WHERE clause) I would get a confirmation of the number deleted. If I delete all of them I get an error message. Seemed (and still seems) odd to me to have it work in one situation and not the other. BUT I DIGRESS, rules is rules

-- Mark

[mwieder]

Seemed (and still seems) odd to me

Seems odd to me, too, but I read it on the internet and it was in color, so there you go.

[afshannoomi]

I haven't tested with asterisk 10 or 11, it's better you use asteris

[sturgis]

If you want a count, while its silly to have to do so, you can use an always true condition and will end up with a count being returned.

Something like this:

Code: Select all

put "delete from myDB where 1=1" into tSQL -- i.e. delete all of the records

I suspect that for speed purposes when deleting all, the db engine skips... lots. no need to count, no going through the data, just remove it, so no row count is involved. Forcing any type of check will make it count, but will slow down the delete.