Transactions

< BACK  NEXT >

[oR]

There are many situations where a number of SQL statements must be executed. It is imperative that all of these statements succeed or, if one fails, that the changes made by other statements are removed from the database. This is important to ensure data integrity. For example, in the case where the INSERT statement was used to add a new order, consisting of several OrderDetails records, all the records should be added to the database or, if one insertion fails, the other records should be removed. This can be achieved by using transactions.

Executing BeginTrans through a Connection interface starts a transaction. The SQL statements can then be executed through that same Connection interface. Once complete, the application can call CommitTrans if all completed successfully, or RollbackTrans to backout any changes made from the time the BeginTrans was executed.

For example, the following two SQL statements will delete all the Orders and OrderDetail records. These statements should be in a transaction, since all the information needs to be deleted, or none.

 DELETE FROM Orders DELETE FROM OrderDetails 

Listing 16.15 shows a transaction placed around these two DELETE statements, together with exception handling. If an exception is detected, a ROLLBACK is executed. If no exception occurs the changes are committed to the database.

Listing 16.15 transactions

 void Listing16_15() {   AdoNS::_ConnectionPtr pConnection;   HRESULT hr;   EXCEPTION_RECORD ExceptionRecord;   if(!GetConnection(pConnection))       return;   _bstr_t bStrSQL;   __try   {     pConnection->BeginTrans();     bStrSQL = _T("DELETE FROM Orders");     ExecuteSQL(pConnection, bStrSQL);     bStrSQL = _T("DELETE FROM OrderDetails");     ExecuteSQL(pConnection, bStrSQL);     pConnection->CommitTrans();   }   __except (hr = GetExceptionCode(),         EXCEPTION_EXECUTE_HANDLER)   {     cout   _T("Trapped Failure: ")   hr   endl;     pConnection->RollbackTrans();   }   pConnection->Close();   cout   _T("Finished")   endl; } 

< BACK  NEXT >