>Hello, Aleksey
>
>It's very interesting to observe how one can present a bad situation as a good one. Well... I'll keep this thread till someone will do something about it.
>
>We have two cases: SQLSETPROP(connection,"Transactions",1) and SQLSETPROP(connection,"Transactions",2). Let's have a closer look at both of them:
>
>To protect the server, I use a shared connection (I always did that). So:
>
>Case 1: That's my way to do a multitable update:
In ODBC in general, it was always NOT recommended to use transaction management techniques other than: SetConnectOption(...,SQL_AUTOCOMMIT,..) and SQLTransact() functions. There is a simple reason for that, not every ODBC driver is able to keep track of those techniques and adjust AUTOCOMMIT option accordingly. In fact, I can not name any ODBC driver that is able to do that. For an application that should target many different and, potentially, unknown backends (for instance VFP), there is only one way to check/set current transaction state - SQL_AUTOCOMMIT option. If you are breaking this mechanism, you are looking for trouble.
It is perfectly safe to call BEGIN TRANSACTION in a batch or in a stored procedure as long as they close this transaction.
For the case of multitable update, I would do this:
SQLSETPROP(con,"Transactions",2)
SQLEXEC(con,"...")
...
SQLEXEC(con,"...")
SQLROLLBACK(con)/SQLCOMMIT(con)
SQLSETPROP(con,"Transactions",1)
Later in this message I will show that CursorAdapter doesn't interfere with this approach.
>
>
>lnResult = SQLEXEC(connection,"BEGIN TRANSACTION")
>IF lnResult = -1
>* Error checking here and return.
>ENDIF
>
>* At this moment I have one open transaction on the backend. Looks ok to me.
>
>llSuccess = TABLEUPDATE(.T.,.F.,"cursoradapter1")
>IF NOT llSuccess
>*Error checking here and return
>ENDIF
>
>llSucces = TABLEUPDATE(.T.,.F.,"cursoradapter2")
>
>
>Then I thought: maybe the cursoradapter wants to enclose it's own update in a transaction, but it can't start it, because of the SQLSETPROP() setting. Let's make it have the transaction begun, then. So I modified base class, adding a SQLEXEC("BEGIN TRANSACTION") in BeforeCursorUpdate() event, hoping the multitable update structure will look like this:
>
>
>BEGIN TRASACTION (1) - started programatically
>
>BEGIN TRASACTION (2) - in BeforeCursorUpdate()
>update
>COMMIT TRAN
>
>BEGIN TRANSACTION (2) - in BeforeCursorUpdate()
>update
>COMMIT TRAN
>
>COMMIT TRANSACTION (1) - executed programatically.
>
>
>In this case, I would be somehow happy. No luck. In this case, the code works like this:
>
>
>lnResult = SQLEXEC(connection,"BEGIN TRANSACTION")
>IF lnResult = -1
>* Error checking here and return.
>ENDIF
>
>* At this moment I have one open transaction on the backend. Looks ok to me.
>
>llSuccess = TABLEUPDATE(.T.,.F.,"cursoradapter1")
>* Stepping on through the code, BeforeCursorUpdate fires,
>
>IF NOT llSuccess
>*Error checking here and return
>ENDIF
>
>* Not to the second CA:
>
>llSucces = TABLEUPDATE(.T.,.F.,"cursoradapter2")
>* Stepping through the code, I see BEGIN TRANSACTION executed,
>
>
>Heh? Can you tell me again to study the examples, PLEASE?
>
What I can tell you is that there are a lot of things I and you don't know about. And it is good to learn as much as possible. For instance, you stated somewhere on this thread that remote view doesn't use transactions. However, it does use transactions and making wrong statements you are confusing people a lot. Run the following code and see what SQL Profiler shows:
CLOSE DATABASES all
CLEAR
DELETE DATABASE TestTransaction
?VERSION()
con=SQLCONNECT("LocalServer")
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)
INSERT INTO testview values(1,"1111",1,"2222")
?[TABLEUPDATE(.T.)],TABLEUPDATE(.T.)
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)Here is what I see in SQL Profiler:
SQL:BatchCompleted select * from testtransaction1,testtransaction2 WHERE testtransaction1.f1=testtransaction2.f3
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 select * from testtransaction1,testtransaction2 WHERE testtransaction1.f1=testtransaction2.f3
Audit Logout
SQL:BatchCompleted DROP table testtransaction1
SQL:BatchCompleted DROP table testtransaction2
Audit Logout
Marked lines are indication of the fact that remote view uses transaction during TABLEUPDATE. You better watch out for this scenarios in your application if you are managing transactions behind VFP back.
>
>Case 2 - manual transactions.
>
>One of the windows' standards are MDI forms. My application is compliant with that. So, I can open two or more forms at once.
>
>Connection is shared, and first CA opens one transaction. In the form's Unload I have to SQLCOMMIT() or SQLROLLBACK() it, right?
>
>Go figure what happens with the second form, if the first one closes the transaction.
>
>Now I'm angry. Please prove me I am wrong, I miss all the problem here and I'm completely out of the programming stuff. I will thank you if you'll be able to do that.
>
If you are not managing transaction behind VFP back, remote view and CursorAdapter are smart enough to determine whether they need to start and commit/rollback their own transaction. Run the code below and analyze SQL Profiler and VFP output, you'll see that if Transactions=2 neither remote view nor CursorAdapter start/end transaction, but they do this if Transactions=1. It also shows that, if BEGIN TRANSACTION command is executed, VFP is not aware of the active transaction.
CLOSE DATABASES all
CLEAR
?VERSION()
con=SQLCONNECT("LocalServer")
IF con<1
? "FAILED to connect!!!"
RETURN
ENDIF
SQLEXEC(con,"SET ANSI_PADDING ON")
SQLEXEC(con,"create table #testtransaction (f1 int, f2 char(10))")
SQLEXEC(con,"insert into #testtransaction values(1,'11111')")
LOCAL oCA as CursorAdapter
oCA=CREATEOBJECT("CursorAdapter")
oCA.DataSourceType="ODBC"
oCA.DataSource=con
oCA.SelectCmd="select * from #testtransaction"
oCA.Tables="#testtransaction"
oCA.KeyFieldList="f1"
oCA.UpdatableFieldList="f1, f2"
oCA.UpdateNameList="f1 #testtransaction.f1, f2 #testtransaction.f2"
oCA.BufferModeOverride= 5
oCA.UpdateType= 2
oCA.CursorFill()
? "----------USE automatic commit------------"
?
SELECT (oCA.Alias)
LIST
SQLSETPROP(con,"Transactions",1)
SQLEXEC(con,"select @@TRANCOUNT as count", "trancount")
? "Current trancount:",trancount.count
UPDATE (oCA.Alias) SET f2=RTRIM(f2)+"2"
SELECT (oCA.Alias)
?[SQLGETPROP(con,"Transactions")=],SQLGETPROP(con,"Transactions")
?"TABLEUPDATE(.T.):",TABLEUPDATE(.T.)
?[SQLGETPROP(con,"Transactions")=],SQLGETPROP(con,"Transactions")
SQLEXEC(con,"select @@TRANCOUNT as count", "trancount")
? "Current trancount:",trancount.count
SQLEXEC(con,"select * from #testtransaction","currentstate")
SELECT currentstate
LIST
? "----------Done with automatic commit------------"
?
? "----------USE manual commit------------"
?
SQLSETPROP(con,"Transactions",2)
SQLEXEC(con,"select * from #testtransaction","currentstate")
SELECT currentstate
LIST
SQLEXEC(con,"select @@TRANCOUNT as count", "trancount")
? "Current trancount:",trancount.count
UPDATE (oCA.Alias) SET f2=RTRIM(f2)+"3"
SELECT (oCA.Alias)
?[SQLGETPROP(con,"Transactions")=],SQLGETPROP(con,"Transactions")
?"TABLEUPDATE(.T.):",TABLEUPDATE(.T.)
?[SQLGETPROP(con,"Transactions")=],SQLGETPROP(con,"Transactions")
SQLEXEC(con,"select @@TRANCOUNT as count", "trancount")
? "Current trancount:",trancount.count
SQLEXEC(con,"select * from #testtransaction","currentstate")
SELECT currentstate
LIST
? "Rollback Transaction:",SQLROLLBACK(con)
SQLEXEC(con,"select @@TRANCOUNT as count", "trancount")
? "Current trancount:",trancount.count
SQLEXEC(con,"select * from #testtransaction","currentstate")
SELECT currentstate
LIST
SQLSETPROP(con,"Transactions",1)
?[SQLGETPROP(con,"Transactions")=],SQLGETPROP(con,"Transactions")
? "----------Done with manual commit------------"
?
? "----------Try T-SQL BEGIN TRANSACTION------------"
SQLEXEC(con,"BEGIN TRANSACTION")
SQLEXEC(con,"select @@TRANCOUNT as count", "trancount")
? "Current trancount:",trancount.count
?[SQLGETPROP(con,"Transactions")=],SQLGETPROP(con,"Transactions")
SQLEXEC(con,"COMMIT TRANSACTION")
? "----------Done with T-SQL BEGIN TRANSACTION------------"
USE IN (oCA.Alias)
SQLDISCONNECT(con)VFP output:
Visual FoxPro 08.00.0000.2521 for Windows
----------USE automatic commit------------
Record# F1 F2
1 1 11111
Current trancount: 0
SQLGETPROP(con,"Transactions")= 1
TABLEUPDATE(.T.): .T.
SQLGETPROP(con,"Transactions")= 1
Current trancount: 0
Record# F1 F2
1 1 111112
----------Done with automatic commit------------
----------USE manual commit------------
Record# F1 F2
1 1 111112
Current trancount: 1
SQLGETPROP(con,"Transactions")= 2
TABLEUPDATE(.T.): .T.
SQLGETPROP(con,"Transactions")= 2
Current trancount: 1
Record# F1 F2
1 1 1111123
Rollback Transaction: 1
Current trancount: 0
Record# F1 F2
1 1 111112
SQLGETPROP(con,"Transactions")= 1
----------Done with manual commit------------
----------Try T-SQL BEGIN TRANSACTION------------
Current trancount: 1
SQLGETPROP(con,"Transactions")= 1
----------Done with T-SQL BEGIN TRANSACTION------------Sql Profiler output:
SQL:BatchCompleted SET ANSI_PADDING ON
SQL:BatchCompleted create table #testtransaction (f1 int, f2 char(10))
SQL:BatchCompleted insert into #testtransaction values(1,'11111')
SQL:BatchCompleted select * from #testtransaction
SQL:BatchCompleted select @@TRANCOUNT as count
SQL:BatchCompleted set implicit_transactions on
RPC:Completed exec sp_executesql N'DELETE FROM #testtransaction
WHERE f1=@P1 AND f2=@P2 ; INSERT INTO #testtransaction (f1,f2) VALUES (@P3 ,@P4 )',
N'@P1 int,@P2 varchar(10),@P3 float,@P4 varchar(10)', 1, '11111 ', 1.000000000000000e+000, '111112 '
SQL:BatchCompleted IF @@TRANCOUNT > 0 COMMIT TRAN
SQL:BatchCompleted set implicit_transactions off
SQL:BatchCompleted select @@TRANCOUNT as count
SQL:BatchCompleted select * from #testtransaction
SQL:BatchCompleted set implicit_transactions on
SQL:BatchCompleted select * from #testtransaction
SQL:BatchCompleted select @@TRANCOUNT as count
RPC:Completed exec sp_executesql N'DELETE FROM #testtransaction WHERE f1=@P1 AND f2=@P2 ;
INSERT INTO #testtransaction (f1,f2) VALUES (@P3 ,@P4 )', N'@P1 int,@P2 varchar(10),
@P3 float,@P4 varchar(10)', 1, '111112 ', 1.000000000000000e+000, '1111123 '
SQL:BatchCompleted select @@TRANCOUNT as count
SQL:BatchCompleted select * from #testtransaction
SQL:BatchCompleted IF @@TRANCOUNT > 0 ROLLBACK TRAN
SQL:BatchCompleted select @@TRANCOUNT as count
SQL:BatchCompleted select * from #testtransaction
SQL:BatchCompleted IF @@TRANCOUNT > 0 COMMIT TRAN
SQL:BatchCompleted set implicit_transactions off
SQL:BatchCompleted BEGIN TRANSACTION
SQL:BatchCompleted select @@TRANCOUNT as count
SQL:BatchCompleted COMMIT TRANSACTION
Audit Logout
Now, you are the one you should be angry with.
Thanks,
Aleksey.
