Page #92 (Chapter 8. Transactions)


The Stock Trader

We need an example to explore the transaction support under COM+. As I have dabbled in trading stocks over the Internet, I would like to use a simple stock trading system as an example. As for those lessons learned when trading stocks, I will leave that for another book.

Our brokerage firm,, allows clients to trade stocks over the Internet. In order to do so, a client has to maintain an account with the firm.

Figure 8.1 identifies the requisite components to set up our stock trading system. The figure also illustrates the interaction between these components.

Figure 8.1. A sample stock trading system.

The trading system is based on a three-tier Windows DNA strategy.

The presentation layer (the first tier) is a Web-based user interface.

The business logic (the second tier) consists of three components: AccountMgmt.DLL, StockExchange.DLL, and TradeMgmt.DLL.

The data for the clients and the stocks is stored in two different databases (the third tier): AccountsDB and StocksDB.

The account management component maintains the clients accounts. The stock management component maintains the stocks database. The trade management component lets the client buy a stock. It interacts with the other two components to update the respective databases.

The Database Layer

In this application, the Microsoft Data Engine (MSDE) will be the database server. It is a Microsoft SQL Server 7.0-compatible data storage server and is packaged with Microsoft Office 2000. Details of installing MSDE can be found in the MSDN article Creating and Deploying Access Solutions with the Microsoft Data Engine [Smi-99] by Scott Smith.

Using MSDE

MSDE comes with a command line program, osql.exe. We will use this program to create our databases. To use this program interactively, you can type:

 osql.exe  U userid 

where userid is the identification of the user allowed to access the database server. In our example, the userid is sa and the password field is empty.

Program osql.exe also lets you run SQL statements in a batch mode. Simply create a file containing the SQL statements and run the program specifying the filename as a parameter, as shown in the following example:

 osql.exe  U sa  i MyQueryFile.sql 

That s it as far as using MSDE goes. Now let s create our databases!

The Accounts Database

The AccountsDB database maintains account balances for our clients. It contains one table, Accounts, that defines two fields, Client (the client s name) and Balance (funds that can be used to purchase stocks). Table 8.1 shows the data stored in this table.

Table 8.1. AccountsDB Database









To create this database, run the following SQL statements:

 create database AccountsDB  go  use AccountsDB  create table Accounts ([Client] varchar (15) NOT NULL,     [Balance] int NOT NULL)  create unique index Client on Accounts([Client])  insert into Accounts Values ('Don', '100000')  insert into Accounts Values ('Chris', '90000')  insert into Accounts Values ('Richard', '80000')  go  quit 

The go SQL statement explicitly forces the execution of preceding SQL statements. For more information on SQL statements, consult the MSDE documentation.

The Stocks Database

The StocksDB database maintains information on the stocks that are currently traded on our fictitious stock exchange. It contains one table, Stocks, that defines three fields, Symbol (for stock symbols), Shares (number of outstanding shares for the stock that may be purchased at the market price), and MarketPrice (current market price for the stock). Table 8.2 shows the data stored in the Stocks table.

Table 8.2. StocksDB Database










To create this database, run the following SQL statements:

 create database StocksDB  go  use StocksDB  create table Stocks ([Symbol] varchar (5) NOT NULL,     [Shares] int NOT NULL,     [MarketPrice] int NOT NULL)  create unique index [Symbol] on Stocks([Symbol])  insert into Stocks Values ('MSFT', '50000', 95)  insert into Stocks Values ('INTC', '30000', 75)  go  quit 

Now let s take a look at the various components of the business logic.

The Business Logic

The Account Management Component

Component AccountMgmt.DLL is used to update the AccountsDB database. It has just one interface, IAccountMgr, that supports just one method, Debit. The interface is defined as follows:

 interface IAccountMgr : IDispatch  {    HRESULT Debit([in] BSTR bsClient, [in] long lAmount);  }; 

Method Debit decreases the account balance of the specified client by the amount specified. The implementation is shown in the following code fragment:

 STDMETHODIMP CAccountMgr::Debit(BSTR bsClient, long  lAmount)  {    try {       ADOConnectionPtr spConn = OpenAccountsDB();        long lCurrentBalance = GetBalance(spConn, bsClient);        if (lCurrentBalance < lAmount) {          return Error(_T("Not enough balance"),             GUID_NULL, E_FAIL);        }        long lNewBalance = lCurrentBalance - lAmount;        UpdateBalance(spConn, bsClient, lNewBalance);     }     catch(_com_error& e) {       return Error(static_cast<LPCTSTR>(e.Description()),          GUID_NULL, e.Error());     }     return S_OK;  } 

The code snippet here uses Microsoft s Active Data Objects (ADO) to manipulate the database. ADO simplifies programming by isolating the details of underlying ODBC (Open Database Connectivity) drivers and/or native OLE DB drivers. In the simulation program, ADO uses a native OLE DB driver called SQLOEDB to access the MSDE database. Covering ADO is beyond the scope of this book. However, the code snippets that I will be presenting should illustrate the use of ADO interfaces clearly. More information on ADO can be found on the Microsoft platform SDK documentation. In particular, the SDK article, Migrating from DAO to ADO using ADO with the Microsoft Jet Provider [Hen-99] has a great introduction to ADO.

Method Debit calls OpenAccountsDB to open the AccountsDB database. It then calls GetBalance to obtain the balance for the specified client. Finally, it calls UpdateBalance to update the account balance for the client. The implementation of these methods is as follows:

 // File StdAfx.h  ...  #import "c:\program files\common files\system\ado\msado15.dll"\    rename ( "EOF", "adoEOF" )  typedef ADODB::_ConnectionPtr ADOConnectionPtr;  typedef ADODB::_RecordsetPtr ADORecordsetPtr;  #define CHECKHR(hr) \    { if (FAILED(hr)) _com_issue_error(hr ); }  // File AccountMgr.cpp  ADOConnectionPtr CAccountMgr::OpenAccountsDB()  {   ADOConnectionPtr spConn;    HRESULT hr =       spConn.CreateInstance(__uuidof(ADODB::Connection));    CHECKHR(hr);    // Use native OLE DB driver for MSDE when connecting to    // to the database    _bstr_t bsDSN = "provider=sqloledb;database=AccountsDB";    _bstr_t bsUser = "sa";    hr = spConn->Open (bsDSN, bsUser, (LPCTSTR) NULL, NULL);    CHECKHR(hr);    return spConn;  }  long CAccountMgr::GetBalance(ADOConnectionPtr spConn,    BSTR bsClient)  {   ADORecordsetPtr spRS;    HRESULT hr = spRS.CreateInstance(__uuidof(ADODB::Recordset));    CHECKHR(hr);    // Construct a SQL query    TCHAR buf[256];    _stprintf(buf,      _T("SELECT * FROM Accounts WHERE [client] = '%S'"),        (LPCWSTR) bsClient);    // Get the recordset    _variant_t vConn = static_cast<IDispatch*>(spConn);    hr = spRS->Open(buf, vConn, ADODB::adOpenKeyset,      ADODB::adLockPessimistic, ADODB::adCmdText);    CHECKHR(hr);    return spRS->Fields->Item["Balance"]->Value;  }  void CAccountMgr::UpdateBalance(ADOConnectionPtr spConn,    BSTR bsClient, long lBalance)  {   // Construct a SQL statement to update the balance    TCHAR buf[256];    _stprintf(buf,      _T("UPDATE Accounts SET Balance = %ld WHERE        [client] = '%S'"), lBalance, (LPCWSTR) bsClient);    // Execute the SQL statement    _variant_t vRecordCount;    spConn->Execute(buf, &vRecordCount, -1);  } 

In the code above, if any ADO call fails, an exception of type _com_error is issued and further processing is stopped.

The Stock Exchange Component

This component supports one interface, IStockMgr. The interface is described as follows:

 interface IStockMgr : IDispatch  {   HRESULT BuyStock([in] BSTR bsSymbol, [in] long lShares,      [out, retval] long* plValue);  }; 

Method BuyStock checks to see if the requested stock symbol and the requested number of shares are available in the StocksDB database. If a match is found, it reduces the number of available shares in the database and returns the total value of the trade, which is the product of the number of requested shares and the market price of the stock. The implementation for this method follows:

 STDMETHODIMP CStockMgr::BuyStock(BSTR bsSymbol,    long lRequestedShares, long *plValue)  {   try {     ADOConnectionPtr spConn = OpenStocksDB();      long lAvailableShares, lMarketPrice;      GetStockInfo(spConn, bsSymbol, lAvailableShares,        lMarketPrice);      if( lAvailableShares < lRequestedShares) {       return Error(_T("Not enough shares"),          GUID_NULL, E_FAIL);      }      // Reduce the available number of shares      lAvailableShares -= lRequestedShares;      UpdateAvailableShares(spConn, bsSymbol, lAvailableShares);      *plValue = lRequestedShares * lMarketPrice;    }    catch(_com_error& e) {     return Error(static_cast<LPCTSTR>( e.Description()),        GUID_NULL, E_FAIL);    }    return S_OK;  } 

The code here references two methods, GetStockInfo and UpdateAvailableShares. Their implementation is similar to those we saw earlier for the account management component. Consequently, the implementation is not shown here.

The Trade Management Component

Our final component of the business logic, the trade manager, is responsible for the overall management of buying stocks. It supports an interface, ITradeMgr. The interface is defined as follows:

 interface ITradeMgr : IDispatch  {   HRESULT BuyStocks([in] BSTR bsClient, [in] BSTR bsSymbol,      [in] long lShares);  }; 

Interface method BuyStocks attempts to buy the specified number of shares for the specified stock on behalf of the specified client. To accomplish this, the trade manager interacts with the other two components of the business logic. The implementation for this method is as follows:

 STDMETHODIMP CTradeMgr::BuyStocks(BSTR bsClient, BSTR bsSymbol,    long lShares)  {   try {     //      // First operation - Obtain the stocks.      //      IStockMgrPtr spStockMgr(__uuidof(StockMgr));      long lAmount = spStockMgr->BuyStock(bsSymbol, lShares);      //      // Second operation - Debit the clien't account balance      //      IAccountMgrPtr spAccountMgr(__uuidof(AccountMgr));      spAccountMgr->Debit(bsClient, lAmount);    }catch(_com_error& e) {     return Error(static_cast<LPCTSTR>( e.Description()),        GUID_NULL, e.Error());    }    return S_OK;  } 

The Simulation

Consider the case when one of the clients, say, Don, wants to buy 100 shares of MSFT. The following VbScript code shows the logic:

 set TradeMgr = CreateObject("TradeMgmt.TradeMgr")  TradeMgr.BuyStocks "Don", "MSFT", 100 

When this code is executed, 100 shares of MSFT are removed from the StocksDB database, and Don s account balance is debited by $9500 (recall that the market price of MSFT was $95).

To verify that the transaction succeeded, you can query the databases by running osql.exe. This is left as an exercise for you.

Now, consider another scenario. Let s say another client, Chris, wants to buy 1000 shares of MSFT. The following VBScript code shows the logic:

 set TradeMgr = CreateObject("TradeMgmt.TradeMgr")  TradeMgr.BuyStocks "Chris", "MSFT", 1000 

When this code is executed, 1000 shares of MSFT are removed from the StocksDB database. However, Chris account balance will not get debited, as he does not have sufficient funds; he is short by $5000.

This transaction has created a problem for us. A thousand shares of MSFT have just disappeared from the exchange. At this rate, our stock exchange will pretty soon run into the ground!

An obvious solution is to modify the code so that the stocks are inserted back into the market in case of a failure. However, this solution is neither practical nor maintainable for transactions that involve many complex operations to perform. Moreover, it violates many important requirements for a transaction.

Let s examine the requirements for a transaction.


COM+ Programming. A Practical Guide Using Visual C++ and ATL
COM+ Programming. A Practical Guide Using Visual C++ and ATL
ISBN: 130886742
Year: 2000
Pages: 129 © 2008-2017.
If you may any questions please contact us: