[SOLVED} DB Edits and DBGrid blank after posting record

[goodname]

sqoKeepOpenOnCommit was added in FPC 3.0.  May have focused a little too much on read-only queries but anything that reduces or removes long running idle transactions is good and commitRetaining usually creates these types of transactions.

the transaction is directly activated again when you browse the dataset.

Are you saying that if for example a onScroll event happens that the transaction is opened again? I hope this is not the case.

[rvk]

Are you saying that if for example a onScroll event happens that the transaction is opened again? I hope this is not the case.

Yes, I just tested it with a small project (see attached).
After adding a Contact, when you start to browse the transaction is activated again. I think this is correct because every reading needs to have it's own transactional-context. I think that's just how it works. So you would always have a transaction open, even for just reading.

Any SQL Statement starts a transaction in Oracle.

http://stackoverflow.com/a/4488944/1037511

(when you set stoExplicitStart and don't activate the transaction before opening the dataset you'll get an error)

Code: [Select]

Error: attempt to implicitly start a transaction on Connection "SQLite3Connection1", transaction "SQLTransaction1".
(my test-project even crashes with an error when exiting)

Code: [Select]

Operation cannot be performed on an active dataset

[goodname]

So you would always have a transaction open, even for just reading.

Yes you must open a transaction for each query or set of queries. It is best practice to close it when your finished using it as open idle transactions do use database server resources. Databases engines usually consider long running idle transactions to be a client side bug.  I'm guessing that a onScroll event triggers a database refresh which means that I'm going to have to put Commit's into the afterScroll events to prevent these idle transactions.

[rvk]

... which means that I'm going to have to put Commit's into the afterScroll events to prevent these idle transactions.

Or .Rollback. No need to commit if you haven't changed anything.

Also please note that this ONLY a problem when you use a master/details dataset.
The master dataset internally closes and opens the details dataset which in turn seem to activate the transaction.

Also the transaction is always automatically activated when opening the dataset so you could (for a master/details dataset) set the Rollback of the transaction in the AfterOpen-event. That seems to work ok.

Code: Pascal  [Select][+][-]

1. procedure TForm1.MasterAfterOpen(DataSet: TDataSet);
2. begin
3.   if SQLTransaction1.Active then
4.     SQLTransaction1.Rollback;
5. end;
6.  
7. procedure TForm1.DetailAfterOpen(DataSet: TDataSet);
8. begin
9.   if SQLTransaction1.Active then
10.     SQLTransaction1.Rollback;
11. end;

Although you need to be careful with Rollback. In a Master/details dataset the details could be closed/opened while you're editing a master in which case you can loose the edits. But if you're sure it's just a read-only dataset Rollback is better.

[goodname]

For a moment there I thought this issue applied to all queries. It makes sense that it happens in master/client queries as client queries get new parameter data from the master query every time the master scrolls.

My understanding is that rollback can trigger a warning message if called outside of a transaction while commit does not. Don't think it is a big issue which is used as they will both work the same for read only queries. For write queries the number of possibilities is greatly increased so greater planning and testing are required. Previous to fpc 3.0 and sqoKeepOpenOnCommit there was no way to close a transaction without closing the dataset as well which meant long running idle transaction were unavoidable.