Hi Andy,

>Hi Aleksey
>
>>What you are missing is the fact that after you execute SQLSETPROP(con,"Transactions",2), the server will automatically switch to implicit transaction as soon as you try to execute any command that is supposed to be tracked by a transaction.
>
>Sorry, again, but I am not missing anything. What you say is true, but only for SQL Server. As I tried to p[oint out it is not true for servers that do not support Implicit Transactions in the way that SQL Server does.
>


My answer was SQL Server specific simply because your conclusion was made based on SQL Server behavior. But I can easy make my answer backend independent:

If backend supports transactions, executing SQLSETPROP(con,"Transactions",2) will put the connection into manual transaction mode. After this point, all commands executed thru this connection will be part of the single transaction until SQLCommit/SQLRollback is executed. ODBC client doesn't have to execute any additional transaction management commands to make this happen, all required locking and logging will be done as appropriate.

How ODBC driver implements manual transaction mode is backend/driver specific. If it supports Implicit transaction, it may decide to use this feature, if it doesn't support Implicit transaction, it'll use BEGIN TRANSACTION or some other backend specific command(s). Same true for commit/rollback. The whole purpose of ODBC is to make a client to be backend independent as much as possible.

After executing SQLSETPROP(con,"Transactions",2), client has a right to assume that connection is in transaction state until driver is specifically instructed to finish it in one way or another. If connection is in automatic transaction mode, client has a right to assume that each execution is committed separately.

After that, what is SQLSETPROP(con,"Transactions",2) as not a logical equivalent of BEGIN TRANSACTION?




>The statement that you made that
>
>SQLSETPROP( [con], 'Transactions', 2) is equivalent to 'BEGIN TRANSACTION'
>
>is simply wrong, because, as you point out, even forcing implicit mode does not actually start a transaction. If you do the following you will NOT have a transaction on the back end, even in SQL Server.
>
>

>SQLSETPROP( [con], 'Transactions', 2)
>SQLEXEC( [con], "SET implicit_transactions OFF" )
>SQLEXEC( [con], "INSERT INTO [table]....." )
>

>
>However, the following WILL give you a transaction even in SQL Server
>
>

>SQLSETPROP( [con], 'Transactions', 2)
>SQLEXEC( [con], "SET implicit_transactions OFF" )
>SQLEXEC( [con], "BEGIN TRANSACTION" )
>SQLEXEC( [con], "INSERT INTO [table]....." )
>

>
>I do understand what you mean, I am merely trying to the qualify the blanket statement that you made (i.e. nit-picking :)


These examples are not legitimate even for nit-picking:
1) Again, they are SQL Server specific.
2) More important, they mix ODBC transaction management with native transaction management commands sent directly to the server. It is explicitly stated that those actions are NOT recommended and they lead to unpredictable results, thus result can not be used as an argument.

If you will be able to provide example that works within ODBC guidelines and demonstrates the fact that backend violates the assumption "SQLSETPROP(con,"Transactions",2) is a logical equivalent of BEGIN TRANSACTION" i.e.: doesn't combine transaction sensitive commands into single transaction, doesn't perform appropriate locking and logging, fails to commit/rollback changes, I will be the first agree with you. :-)

Thanks,
Aleksey.