Begin Transaction . . End Transaction. Records being lost

[AndrewMozley]

I have developed a form (FRM_X) which can be invoked within a larger application (APP_Y). I have not developed the larger application (APP_Y) and do not have the source code for it.

Within my form I gather information from controls, and if all goes well I post records to several tables, mainly appending records. So the code in FRM_X says something like

IF . . all has gone well . . .
BEGIN TRANSACTION
SELECT DBF123
APPEND BLANK
REPLACE Fld1 WITH mydata1, Fld2 WITH mydata2 . . .
. . . .
(more tables)
END TRANSACTION
ENDIF
(Optional breakpoint)

Usually this works OK, but sometimes it does not. I can run this program up to a breakpoint after I have completed the transaction, and can see my new data (at this breakpoint) sitting happily at the end of the various tables.

However (when it fails) the records have clearly not been written to the tables. I can exit from application APP_Y; then when I examine the tables using VFP the records are not there.

Is it possible that I am operating within a nested transaction (within APP_Y), so that what I think I have saved is being over-ruled by APP_Y?

And is there any way that I can protect myself? I do not wish to compromise the integrity of APP_Y, but is there any command that I can issue with my code (FRM_X) which effectively says
"Really, really save these records" ?

Guidance much appreciated.

 

[Olaf Doschke]

If a transaction is open by APP_Y you could a) determine that via TxnLevel() and b) do additional END TRANSACTIONs. But of course APP_Y wouldn't expect it's own transactions to be ended by external code. What you should do anyway is to check TxnLevel(), if it is higher than 1 from your own transaction.

It's not impossible APP_Y makes use of transactions, but another thing is important. END TRANSACTION is not causing a commit of all buffers, VFP transactions just ensure an isolation level of any file you write to and being able to ROLLBACK. For that matter the usual buffering is extended by storing original unchanged data in case of ROLLBACK. To save data you do TABLEUPDATE and even if you don't buffer you should try to FLUSH FORCE and even to close tables. Using buffering and TABLEUPDATE() is recommended nevertheless, as you gain more control about the time of save and get feedback on failing inserts/updates or deletes. END TRANSACTION after successful TABLEUPDATE() then merely is closing all file locks and saved original data, it's not doing anything to the already changed DBF files anymore.

So in short: It's not about Transactions xor Buffering, both are working best together in conjunction.

Bye, Olaf.

 

[AndrewMozley]

Thank you for your thoughtful reply, Olaf. I will certainly check TxnLevel()

However, since I wrote, I had indeed tried putting a TableUpdate(.T., .T.) immediately before the END TRANSACTION and that has certainly had the effect of making my table updates permanent, and visible once the session is ended.

So this appears to deal with my problem. However it has occurred to me that I may be committing updates from the main application (APP_Y) which it might decide to ROLLBACK. It is fairly unlikely, but I suppose that I ought to Tableupdate() the tables I have amended individually. But there again (extremely unlikely) APP_Y might have made changes to my tables which it has thought better about!

I will continue to test the application . . . .

 

[Olaf Doschke]

>I ought to Tableupdate() the tables I have amended individually
Tableupdate() alwways works on a single workarea only, anyway. If you omit the alias parameter, it works on the current workarea, it never commits all changes of all buffers.

Also see what I said near the end (third post from bottom) in thread184-1716642.

Bye, Olaf.