CLOSE DATABASES all CLEAR DELETE DATABASE TestTransaction ?VERSION() con=SQLCONNECT("LocalServer") && point to any available SQL Server IF con<1 ? "FAILED to connect!!!" RETURN ENDIF SQLEXEC(con,"SET ANSI_PADDING ON") SQLEXEC(con,"create table testtransaction1 (f1 int, f2 char(10))") SQLEXEC(con,"create table testtransaction2 (f3 int, f4 char(10))") CREATE DATABASE TestTransaction CREATE CONNECTION testcon CONNSTRING (SQLGETPROP(con,"ConnectString")) CREATE SQL VIEW testview REMOTE CONNECTION testcon AS ; select * from testtransaction1,testtransaction2 ; WHERE testtransaction1.f1=testtransaction2.f3 DBSETPROP("testview","VIEW","SendUpdates",.T.) USE testview CURSORSETPROP("Buffering",5) ? "Open transaction in a wrong way." viewconn=CURSORGETPROP("ConnectHandle") SQLEXEC(viewconn,"BEGIN TRANSACTION") SQLEXEC(viewconn,"select @@TRANCOUNT as count", "trancount") ? "Current trancount:",trancount.count,"<======= here is your transaction is active" INSERT INTO testview values(1,"1111",1,"2222") SELECT testview ?[TABLEUPDATE(.T.)],TABLEUPDATE(.T.) SQLEXEC(viewconn,"select @@TRANCOUNT as count", "trancount") ? "Current trancount:",trancount.count,"<======= now your transaction is gone!!!" SQLEXEC(con,"select * from testtransaction1,testtransaction2 WHERE testtransaction1.f1=testtransaction2.f3","result") SELECT result LIST CLOSE TABLES SQLEXEC(con,"DROP table testtransaction1") SQLEXEC(con,"DROP table testtransaction2") SQLDISCONNECT(0)VFP output:
Visual FoxPro 08.00.0000.2521 for Windows Open transaction in a wrong way. Current trancount: 1 <======= here is your transaction is active TABLEUPDATE(.T.) .T. Current trancount: 0 <======= now your transaction is gone!!! Record# F1 F2 F3 F4 1 1 1111 1 2222SQL Profiler output:
SQL:BatchCompleted select * from testtransaction1,testtransaction2 WHERE testtransaction1.f1=testtransaction2.f3 SQL:BatchCompleted BEGIN TRANSACTION SQL:BatchCompleted select @@TRANCOUNT as count SQL:BatchCompleted set implicit_transactions on RPC:Completed exec sp_executesql N'INSERT INTO testtransaction1 (f1,f2) VALUES (@P1,@P2)', N'@P1 int,@P2 char(10)', 1, '1111 ' RPC:Completed exec sp_executesql N'INSERT INTO testtransaction2 (f3,f4) VALUES (@P1,@P2)', N'@P1 int,@P2 char(10)', 1, '2222 ' SQL:BatchCompleted IF @@TRANCOUNT > 0 COMMIT TRAN <========= !!! SQL:BatchCompleted set implicit_transactions off SQL:BatchCompleted select @@TRANCOUNT as count SQL:BatchCompleted select * from testtransaction1,testtransaction2 WHERE testtransaction1.f1=testtransaction2.f3The transaction is closed by TABLEUPDATE because it wasn't opened properly.