LiveCode Forums.

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

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

bangkok wrote: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

It's not clear to me why you want the number of rows if you're going to delete all of them anyway, but...
should give you the number of rows in the myDB table. Then do your delete command.

mwieder wrote: 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

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.

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

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: 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.