Fabio,

I would like you to read the following information carefully because I am not going to repeat it for you again.

VFP never calls [set implicit_transactions on] and it never calls [IF @@TRANCOUNT > 0 COMMIT TRAN]. VFP doesn't target any specific backend. And as a good ODBC client it uses SetConnectOption(...,SQL_AUTOCOMMIT,..) and SQLTransact() functions to manage transactions. For VFP developers: SQLSETPROP(con,"Transactions",...) is mapped to SetConnectOption(...,SQL_AUTOCOMMIT,..), SQLCommit() is mapped to SQLTransact(...,....,SQL_COMMIT) and SQLRollback() is mapped When I say mapped, I mean that VFP forwards the call to ODBC and doesn't do anything else, ODBC driver decides what commands to call and when to call them.

If connection is closed while there is in an active transaction, driver commits changes.

If you are calling VFP functions SQLSETPROP(con,"Transactions",...), SQLCommit() and SQLRollback(), it is your choice how and when to call them, VFP is not going to fix your mistakes like missing SQLCommit()/SQLRollback() calls or missing SQLSETPROP(con,"Transactions",...) calls.

Here is more information from MSDN:

ODBC Programmer's Reference  

Committing and Rolling Back Transactions
To commit or roll back a transaction in manual-commit mode, 
an application calls SQLEndTran. Drivers for DBMSs that support 
transactions typically implement this function by executing a 
COMMIT or ROLLBACK statement. The Driver Manager does not call 
SQLEndTran when the connection is in auto-commit mode; it 
simply returns SQL_SUCCESS, even if the application attempts 
to roll back the transaction. Because drivers for DBMSs that 
do not support transactions are always in auto-commit mode, 
they can either implement SQLEndTran to return SQL_SUCCESS without 
doing anything or not implement it at all.

Note   Applications should not commit or roll back transactions 
by executing COMMIT or ROLLBACK statements with SQLExecute or 
SQLExecDirect. The effects of doing this are undefined. Possible 
problems include the driver no longer knowing when a transaction 
is active and these statements failing against data sources that 
do not support transactions. These applications should call 
SQLEndTran instead.

Please educate yourself, read a book about ODBC, read a book about ODBC transaction management. If you are still not happy with the way ODBC driver handles your calls, talk to whoever developed it.

Thanks,
Aleksey.





>Hi Aleksey:
>
>You have show the cause of the SPT bug
>

>...
>? "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) <========= here !!!
>...
>

>
>I show 6 example for explain.
>
>My examples without SQLEXEC.
>
>Example A:
>

>con=SQLCONNECT("LocalServer")     && point to any available SQL Server
>SQLSETPROP(con,"Transactions",2)  && this not send commands
>SQLSETPROP(con,"Transactions",1)  && this not send commands
>SQLDISCONNECT(con)		  && this not send commands
>
>Sql Profiler output:
>-- none
>

>
>Example B:
>

>con=SQLCONNECT("LocalServer")     && point to any available SQL Server
>SQLSETPROP(con,"Transactions",2)  && this not send commands
>SQLDISCONNECT(con)		  && this not send commands
>
>Sql Profiler output:
>-- none
>

>
>Example C:
>

>con=SQLCONNECT("LocalServer")    && point to any available SQL Server
>SQLSETPROP(con,"Transactions",2) && this not send commands
>SQLCOMMIT(con)			 && this not send commands
>SQLDISCONNECT(con)		 && this not send commands
>
>Sql Profiler output:
>-- none
>

>
>Examples with SQLEXEC.
>
>Example A1:
>

>con=SQLCONNECT("LocalServer")     && point to any available SQL Server
>SQLSETPROP(con,"Transactions",2)  && this not send commands
>SQLEXEC(con,"",1)		  && send set implicit_transactions on
>SQLSETPROP(con,"Transactions",1)  && send IF @@TRANCOUNT > 0 COMMIT TRAN
>SQLDISCONNECT(con)		  && this not send commands
>
>Sql Profiler output:
>SQL:BatchCompleted:	set implicit_transactions on
>SQL:BatchCompleted:	
>SQL:BatchCompleted:	IF @@TRANCOUNT > 0 COMMIT TRAN
>

>
>Example B1:
>

>con=SQLCONNECT("LocalServer")    && point to any available SQL Server
>SQLSETPROP(con,"Transactions",2) && this not send commands
>SQLEXEC(con,"",1)		 && send set implicit_transactions on
>SQLDISCONNECT(con)		 && send IF @@TRANCOUNT > 0 COMMIT TRAN
>
>Sql Profiler output:
>SQL:BatchCompleted:	set implicit_transactions on
>SQL:BatchCompleted:	
>SQL:BatchCompleted:	IF @@TRANCOUNT > 0 COMMIT TRAN
>

>
>Example C1:
>

>con=SQLCONNECT("LocalServer")     && point to any available SQL Server
>SQLSETPROP(con,"Transactions",2)  && this not send commands
>SQLEXEC(con,"",1)	          && send set implicit_transactions on
>SQLCOMMIT(con)			  && send IF @@TRANCOUNT > 0 COMMIT TRAN
>SQLDISCONNECT(con)		  && this not send commands
>
>Sql Profiler output:
>SQL:BatchCompleted:	set implicit_transactions on
>SQL:BatchCompleted:	
>SQL:BatchCompleted:	IF @@TRANCOUNT > 0 COMMIT TRAN
>

>
>On VFP, on one ODBC transaction=2, this command are identical:
>

>* command A
>SQLSETPROP(con,"Transactions",1)
>
>* command B
>SQLDISCONNECT(con)		
>
>* command C
>SQLCOMMIT(con)			
>

>
>This is the cause of the problem.
>
>Form me A is bad/bug, need to send IF @@TRANCOUNT > 0 ROOLBACK.
>Form me B is bug, donnot send commands.
>Form me C is correct.
>
>Fabio