Rowsaffected problem

[k1attila1]

Hello everybody

I'm using SQLDB - SQLQUERY component

I need to know the number of affected rows in SQLQuery.AfterPost, SQLQuery.AfterDelete eventhandler, but SQLQuery.RowsAffected always give me  the number of the selected row.

How do I get the right value?
Is there a workaround?

Thanks


 best regards

[Zoran]

If you edit records of an active select query, only one row can be affected in OnPost and OnDelete, right? So, the RowsAffected property is not needed for this. I believe that you don't use the query correctly.

[k1attila1]

Dear Zoran !

I think it is not true always.

If you use ReadCommited isolation level it isn't sure. (there is no recordlock....)

It could be only 1 or 0 saved record.

1 : everything is ok, record is saved

0 : 0 row AFFECTED . I realized that, if an update isn't succesfully (e.g. :Firebird can't find the record according to the WHERE parameter) it won't raise error . 

It is only cause : "0 row(s) affected" without exception.

This cause "lost update" problem.

Attila Kovács

[k1attila1]

Sorry :

IBX know this problem.
ZEOS know this problem.

SQLDB ?????

[ludob]

TSQLQuery is a buffered dataset. That means that Post is an in memory operation only. In AfterPost or AfterDelete nothing has been done with the database yet. So RowsAffected is not valid in that context. See http://wiki.freepascal.org/Working_With_TSQLQuery#Cached_Updates for more info.

ApplyUpdates will store all changes to the database but will use multiple queries if multiple rows have been changed. RowsAffected returns the number of rows affected by the last query on the SQLConnection and therefore does not reflect the total number of rows affected by ApplyUpdates.

[Zoran]

Dear Zoran !

I think it is not true always.

Okay, sorry, Attila.

TSQLQuery is a buffered dataset. That means that Post is an in memory operation only. In AfterPost or AfterDelete nothing has been done with the database yet. So RowsAffected is not valid in that context. See http://wiki.freepascal.org/Working_With_TSQLQuery#Cached_Updates for more info.

ApplyUpdates will store all changes to the database but will use multiple queries if multiple rows have been changed. RowsAffected returns the number of rows affected by the last query on the SQLConnection and therefore does not reflect the total number of rows affected by ApplyUpdates.

This might be a solution for you (I'm not sure if this can really solve your problem, as I do not know details of your application, but I believe that in most cases it will do):

Here is my suggestion — don't use buffering, call ApplyUpdates immediately after each post (you can put a call to ApplyUpdates in OnPost and OnDelete) and then call RecordsAffected (which will, I believe, give the correct answer at this point).
When you don't have buffering, you can than use db transactions to commit or rollback the changes you made — call SQLTransaction1.StartTransaction to start new transaction before the user begins with updating (maybe in the Form's OnShow event), then call SQLTransaction.Rollback (to undo the changes the user has made) or SQLTransaction.Commit (to confirm the changes) when leaving the form.

[k1attila1]

Thanks , Zoran.

But it can't help, i alway get back -1.

I think this is a failure of SQLDB component set. (i studied the source code of SQLDB.)

It isn't a bug , it is "only" reduced ability of SQLDB.

You can use it with "Repeatable Read" isolation level, but you mustn't use it "ReadCommited"
isolation level. (lost update)

ZEOS  : raise an exception if  affected rows is 0
IBX : you can read rowsaffected after delete,insert , update.

IBDataset1.QModify.RowsAffected
IBDataset1.QInsert.RowsAffected
IBDataset1.QDelete.RowsAffected

Thanks, for your kind
Attila Kovács

[Zoran]

Okay, then please report this bug (or feature request if that's what it is) in mantis.
Note, however, that SQLdb is part of FCL, not LCL, so even if this feature gets added, you still won't be able to use it with fpc 2.6.2.