Oracle automatically enters the transaction mode whenever a user modifies data. This must be followed by an explicit COMMIT to write the changes to the database. If a user wants to undo the changes, the user can issue the ROLLBACK statement.
By default, SQL Server automatically commits each change as it occurs. This is called autocommit mode in ODBC. If you do not want this to occur, you can use the BEGIN TRANSACTION statement to signal the start of a block of statements comprising a transaction. After this statement is issued, it is followed by an explicit COMMIT TRANSACTION or ROLLBACK TRANSACTION statement.
To ensure compatibility with your Oracle application, it is recommended that you use the SQLConnectOption function to place your SQL Server application in implicit transaction mode. The SQL_AUTOCOMMIT option must be set to SQL_AUTOCOMMIT_OFF in order to accomplish this. This code excerpt from the sample programs demonstrates this concept:
SQLSetConnectOption(hdbc1, SQL_AUTOCOMMIT,-sql_AUTOCOMMIT_OFF);
The SQL_AUTOCOMMIT_OFF option instructs the driver to use implicit transactions. The default option SQL_AUTOCOMMIT_ON instructs the driver to use autocommit mode, in which each statement is committed immediately after it is executed. Changing from manual commit mode to autocommit mode commits any open transactions on the connection.
If the SQL_AUTOCOMMIT_OFF option is set, the application must commit or roll back transactions explicitly with the SQLTransact function. This function requests a commit or rollback operation for all active operations on all statement handles associated with a connection handle. It can also request that a commit or rollback operation be performed for all connections associated with the environment handle.
SQLTransact(henv1, hdbc1, SQL_ROLLBACK);
(SQLTransact(henv1, hdbc1, SQL_COMMIT);
When autocommit mode is off, the driver issues SET IMPLICIT_TRANSACTIONS ON statement to the server. Starting with SQL Server 6.5, DDL statements are supported in this mode.
To commit or roll back a transaction in manual commit mode, the application must call SQLTransact. The SQL Server driver sends a COMMIT TRANSACTION statement to commit a transaction, and a ROLLBACK TRANSACTION statement to roll back a transaction.
Be aware that manual commit mode can adversely affect the performance of your SQL Server application. Every commit request requires a separate round-trip to the server to send the COMMIT TRANSACTION string.
If you have single atomic transactions (a single INSERT, UPDATE, or DELETE immediately followed by a COMMIT), use the autocommit mode.
In the sample programs, the manual commit mode has been turned on, even for atomic transactions, to demonstrate how easily a SQL Server application can be developed that closely mimics the operation of a similar application designed for the Oracle RDBMS.