Rob Caron
Microsoft Corporation
July 1999
Summary: Looks at Online Bookstore, a Web-based bookseller. Discusses the strategies and considerations involved in the development of this interoperability sample application. Concludes with a walk-through of the application. (23 printed pages)
Note The Online Bookstore sample files can be downloaded from "Setting Up the Online Bookstore Sample."
A fictitious mail-order bookseller specializing in titles from Microsoft® Press has decided to expand its business reach by launching a Web site, Online Bookstore. Because the company has an established order-processing system with large investments in mid-range and mainframe systems, it has decided to create a Web-commerce solution that will leverage these existing systems.
The key motivation for the project is to get a presence on the Web as soon as possible, because sales have declined following the advent of other online booksellers. This is the first endeavor of its kind for this company; therefore, project goals include keeping overall lifetime project costs low by utilizing existing infrastructure when possible and providing a low-cost-to-support solution. Performance is important but secondary to supportability costs and a short-term rollout schedule. If successful, the company will revisit the design to identify areas for improvement.
As a result of mergers and acquisitions, the company’s enterprise data resides in a variety of data stores. For instance, customer information is kept in an Oracle 8 database running on a Sun SPARC/Solaris system. Order and inventory data is stored in an IBM DB2 database running on an IBM MVS system inherited from a merger with another bookseller. CICS programs on this system are used to process credit card authorizations. The company’s shipping system is run from an IBM AS/400, with the shipping data stored in keyed, physical files.
The Online Bookstore sample application is designed using the Microsoft® Windows® Distributed interNet Application (Windows DNA) architecture application development model. The application is implemented in three layers: a data layer, a business layer, and a presentation layer. The data layer represents the myriad of data sources distributed across the company’s enterprise system. The business layer consists of several components running in one Microsoft® Transaction Server (MTS) package. This layer uses technologies provided by Microsoft® SNA Server to access the mainframe and AS/400. The presentation layer is a Web site hosted on Microsoft® Internet Information Server (IIS), which provides a thin client to the customer using a Web browser.
The data layer of the Online Bookstore consists of four data sources disbursed across four individual machines in the company’s enterprise system. This data is accessed from the application’s business component using Microsoft® ActiveX® Data Objects (ADO) and the application’s data component, which provides connectivity to the data sources. ADO uses OLE DB to access each data source using OLE DB providers that provide the required functionality to interact with each data source.
Data used by the Online Bookstore is kept in AS/400 keyed, physical files, an IBM DB2 database, an Oracle 8 database, and a Microsoft® SQL Server™ 7.0 database.
AS/400
The company’s shipping system is run on an IBM AS/400, with shipping data kept in keyed, physical files on the system. Each file represents one table of information. Metadata stored by the system is used to describe the data stored in each file, such as column descriptions. There are three files that are used by the Online Bookstore application: ShipCost, ShipMethod, and TrackNum.
| Table | Data |
| ShipCost | Shipping costs associated with each available shipping method. |
| ShipMethod | Available shipping methods. |
| TrackNum | Next available shipment tracking number. |
DB2
The company’s inventory and order data is kept in an IBM DB2 database that is running on an IBM MVS mainframe. Detailed information concerning each of the available titles sold by the company is stored here also for use in producing the mail-order catalog. This information is periodically replicated on the SQL Server database used by the Web site to minimize the amount of traffic on the DB2 caused by customers browsing the Web site. The Online Bookstore application uses four tables: Inventory, OrderDetails, OrderID, and Orders.
| Table | Data |
| Inventory | Current stock level of each title sold by the company. |
| OrderDetails | Information about each title in an order. |
| OrderID | Next available order ID number. |
| Orders | Information about each order. |
Oracle
The company’s customer information is kept in an Oracle 8 database that is running on a Sun SPARC/Solaris midrange computer. Both the online ordering system and the traditional ordering systems use this database. The Online Bookstore application uses two tables: CustomerID and Customers.
| Table | Data |
| CustomerID | Next available customer ID number. |
| Customers | Detailed information about each customer. |
SQL Server
Information about each title is kept in a SQL Server database that is running on a Windows NT® server. As mentioned earlier, this information is kept in an IBM DB2 database and periodically replicated to the SQL Server database. The Online Bookstore application uses three tables: Categories, TitlePrice, and Titles.
| Table | Data |
| Categories | Categories by which titles are categorized. |
| TitlePrice | Suggested retail and sale price of each title. |
| Titles | Detailed information about each title. |
To access all of the data sources listed earlier, the Online Bookstore application uses ADO and, subsequently, OLE DB. Much of the power and flexibility of ADO comes from its ability to connect to any of several different data providers and still expose the same programming model—regardless of the specific features of any given provider. However, because each data provider is unique, how the Online Bookstore application interacts with ADO will vary slightly between the different data providers. These differences are primarily related to the connection parameters, ADO Command object usage, and the behavior of the ADO Recordset object.
Microsoft OLE DB Provider for AS/400 and VSAM considerations
The Microsoft® OLE DB Provider for AS/400 and VSAM makes it possible for ADO to access and manipulate OS/400 and VSAM file system files at the record level. The provider uses the record-level input/output (RLIO) protocol of the IBM Distributed Data Management (DDM) architecture. The provider is implemented as a source-only DDM server, meaning it cannot act as a target DDM server capable of processing requests from a source DDM server. Because the target DDM servers are resident on host systems, no Microsoft software is required to be placed on the host systems.
AS/400 files can be classified as being either system-described or program-described. System-described files, such as logical files and keyed, physical files, are defined by metadata maintained by the operating system. An AS/400 logical file is similar to a view in a database, while an AS/400 keyed, physical file is similar to a table in a database. Metadata for program-described files are not kept by the operating system. Accessing these files requires a Host Column Description (HCD) file, which contains the required metadata. Program-described files on an AS/400 are usually referred to as flat files.
VSE (Virtual Storage Extended) and MVS (Multiple Virtual Storage) mainframe VSAM (Virtual Sequential Access Method) files are classified as being system-described and require an accompanying HCD file to make it possible for this provider to access them.
The provider permits connection to host files through an Advanced Peer-to-Peer Communication (APPC) LU6.2 connection using SNA Server. Alternatively, the provider can use a direct TCP/IP connection to the DDM TCP/IP port on the destination host computer. The chosen connection method will dictate the parameters required in the ADO Connection object’s ConnectionString property.
In ADO, the Command object is used to open a rowset (table) from a host file using DDM-specific commands or by executing Command Language (CL) commands on an AS/400 target DDM server. These commands are specified in the Command object’s CommandText property. The Command object’s CommandType property must be set to adCmdText. Because this provider implements only record-oriented access, access to data using SQL statements or host-based, stored procedures with this provider is not supported.
By default, the provider uses a server-based cursor. All indexed file access is based on a cursor located over the host file and not a local PC copy of the file. The provider can be configured to use a client-based cursor provided by the Microsoft® Data Access Components (MDAC) cursor services; however, using a client-based cursor limits access to the host files to read-only.
The provider does not currently support automatic enlistment in MTS transactions, nor does it support distributed transactions.
Microsoft® OLE DB Provider for DB2 considerations
The Microsoft OLE DB Provider for DB2 makes it possible for ADO to access and manipulate data in an IBM DB2 database. This provider is implemented as an IBM Distributed Relational Database Architecture (DRDA) application requester. As such, this provider can connect to most of the popular DRDA-compliant DB2 systems.
The provider permits connection to DB2 systems through an APPC LU6.2 connection using SNA Server or a direct TCP/IP connection. The chosen connection method will dictate the parameters required in the ADO Connection object’s ConnectionString property. Refer to the provider’s documentation to determine your connection choice, because not all DB2 systems are accessible from each type of connection.
The provider uses DB2 packages to issue dynamic and static SQL statements. There is a provider-specific property that the provider uses to identify a location in which to create and store DB2 packages. The provider will dynamically create packages in this specified location.
The provider supports remote database access from the Microsoft® Distributed Query Processor (DQP), which is demonstrated in the Online Bookstore application. To support this, the provider must be run in-process. When creating a DQP Linked Server, you must configure this provider to be loaded in-process. This procedure is discussed in the setup documentation that accompanies the Online Bookstore application.
By default, the provider uses a server-based cursor. All data access is based on a cursor located at the DB2 database. The provider can be configured to use a client-based cursor provided by the MDAC cursor services; however, using a client-based cursor limits access to the data to read-only.
To ensure data integrity, use the FOR UPDATE OF clause when creating a recordset that will be updated. This locks the data page when the data is fetched, ensuring no other process can modify the data before your program processes it. After the clause, specify only those columns that will be updated.
Code all read-only SELECT statements with the FOR READ ONLY cursor clause. This will ensure the cursor is unambiguous and can utilize a block fetch. When using a block fetch, the returned rows are grouped into a single block of data.
The provider does not currently support automatic enlistment in MTS transactions, nor does it support distributed transactions.
Microsoft® OLE DB Provider for Oracle considerations
The Microsoft OLE DB Provider for Oracle makes it possible for ADO to access and manipulate data in an Oracle database. The provider is a native provider that can only expose what Oracle can expose through the Oracle Call Interface API.
The provider permits connection to Oracle 7.3 or later databases. To use this provider, the Oracle networking components and client software must be installed on the same machine as the OLE DB provider. Because the provider uses the Oracle networking components to connect to the Oracle server, the ConnectionString property of the ADO Connection object only requires the name of an Oracle server available on the network and an Oracle username and password.
The Command object can be used to execute Oracle stored procedures using either the ODBC or the native Oracle PL/SQL syntax.
Currently, the provider only exposes forward-only, read-only rowsets. As a result, the only method to update information in Oracle using this provider is to issue a separate SQL UPDATE statement.
To obtain a scrollable cursor, specify the cursor location as adUseClient, the cursor type as adOpenStatic, and the lock type as adLockOptimistic. When working with large rowsets, you might want to specify that the rowset be obtained asynchronously using the adAsyncFetch option. This will return control to your application more quickly while the rowset is being constructed.
However, for optimal performance when a scrollable cursor is not required, it is best to set the cursor location to adUseServer, the cursor type to adOpenForwardOnly, and a lock type of adLockReadOnly.
The provider can participate in distributed or coordinated MTS transactions by using Microsoft® Distributed Transaction Coordinator (DTC).
Microsoft® OLE DB Provider for SQL Server considerations
The Microsoft® OLE DB Provider for SQL Server provides native access to SQL Server. The provider uses a set of system stored procedures called catalog stored procedures to obtain information from the SQL Server system catalog.
The provider permits connection to SQL Server 6.5 or later databases, communicating with network software through the SQL Server Net-Library interface.
SQL Server 7.0 fully supports OLE DB as a native programming interface, thus fully supporting applications that use ADO and OLE DB. SQL Server 7.0 uses OLE DB for communication between internal components and between SQL Server installations when using remote stored procedures. In addition, the provider supports rowsets from linked servers to support heterogeneous queries with other OLE DB data sources. Using this provider and Data Transformation Services (DTS), data can be moved and transformed between SQL Server 7.0 and any other OLE DB data provider.
The provider can participate in distributed or coordinated MTS transactions by using DTC.
The business layer of the Online Bookstore is contained in an MTS package that consists of the data, business, and COMTI components. The data and business components are implemented in Microsoft® Visual Basic® and provide the bulk of the functionality associated with the package. The COMTI component is an automation server that works with the COMTI run-time proxy to communicate with mainframe programs.
The data component, TierData, is an interface to the Online Bookstore data layer providing ADO Connection objects to the business component for a variety of data sources. The data component consists of one class module called cData.
cData.cls
| Procedure | Purpose |
| AS400Conn | Provides an ADO Connection object that is connected to an IBM AS/400 using the OLE DB Provider for AS/400 and VSAM. |
| DB2Conn | Provides an ADO Connection object that is connected to an IBM DB2 database using the OLE DB Provider for DB2. |
| OracleConn | Provides an ADO Connection object that is connected to an Oracle database using the OLE DB Provider for Oracle. |
| SQLConn | Provides an ADO Connection object that is connected to a SQL Server using the OLE DB Provider for SQL Server. |
The business component, TierLogic, provides the essential business logic for the Online Bookstore. The business component consists of one code module, Globals, and seven class modules: cCartItems, cCategory, cCustomer, cInventory, cMarketing, cOrder, and cTitle.
Globals.bas
Globals.bas provides a central location in the business component for functionality used throughout the component.
| Procedure | Purpose |
| CreateOjbectInContext | Creates the requested object in the context of the requesting object in MTS. |
cCartItems.cls
This class represents the customer’s shopping cart. It is a collection class for creating an object composed of a collection of cTitle objects. Each cTitle object represents a selected title in the shopping cart.
| Procedure | Purpose |
| FillCart | Fills the shopping cart with the items the customer has selected for purchase by building a collection of cTitle objects. |
| Add | Creates a cTitle object and adds it to the cart. |
| Remove | Removes a cTitle object from the cart. |
cCategory.cls
This class provides functionality related to title categories. It is used to retrieve information regarding a particular category.
| Procedure | Purpose |
| LoadCategory | Retrieves detailed category information. |
cCustomer.cls
This class provides functionality related to a particular customer. It is used to both save and retrieve customer information.
| Procedure | Purpose |
| Login | Authenticates customer credentials. |
| GetCustomerByEMail | Retrieves customer information using the customer’s e-mail address. |
| Add | Adds a new customer to the database. |
| Save | Saves customer data to the database. |
| GetNextCustID | Obtains the next available customer ID. |
cInventory.cls
This class provides functionality related to the current inventory of titles. It is used to both check and debit inventory.
| Procedure | Purpose |
| GetWarnings | Generates low stock warnings when requested. |
| OnHand | Gets the current quantity of a title in stock. |
| RemoveFromInventory | Debits the title inventory at purchase time. |
cMarketing.cls
This class provides functionality related to how titles are marketed on the Web site. It is used to package title information in a manner to entice the customer to purchase selected titles.
| Procedure | Purpose |
| GetBestSellers | Gets a listing of the current best sellers. |
| GetFeaturedTitles | Gets a listing of the featured titles. |
| GetNewTitles | Gets a listing of the newest titles. |
| GetCategoryList | Gets a listing of all title categories. |
| GetTitlesForCategory | Gets a listing of all titles in a category. |
| GetRemainingCategories | Gets a listing of all but the current category. |
| GetFewSentences | Parses out the first few sentences to create blurbs for the featured titles. |
cOrder.cls
This class provides functionality related to the processing of a customer’s order. This class also handles request for information regarding previous orders.
| Procedure | Purpose |
| Process | Processes a customer’s order. |
| CheckOrderStatus | Checks the status of orders placed by a customer. |
| GetShipMethodByID | Gets the name of a ship method using a ship method ID. |
| GetShipCostPerItem | Gets the shipping cost per item for a particular shipping method. |
| GetNextOrderID | Gets the next available order ID. |
| GetShipCostBaseRate | Gets the base shipping cost for a particular shipping method. |
| GetShipMethods | Gets a listing of the available shipping methods. |
cTitle.cls
This class provides functionality related to title information. Each instance of an object created from this class represents a single title.
| Procedure | Purpose |
| LoadTitle | Retrieves detailed information for a title. |
The COMTI component, CreditCard, works in conjunction with the COMTI run-time proxy to provide an interface to a mainframe program that authorizes credit cards.
Merchant
This class provides an interface to the mainframe program that is run on the mainframe. This class contains an interface only and no actual code, which exists on the mainframe.
| Procedure | Purpose |
| GetApproval | Authorizes credit cards prior to processing order. |
The presentation layer of the Online Bookstore consists of a Web site hosted from an IIS Web server, and a browser that serves as a thin client on the customer’s computer. The presentation layer is implemented using Microsoft® Active Server Pages (ASP) written primarily in Microsoft® Visual Basic® Scripting Edition (VBScript) to provide a dynamic interface to the customer. All images used on the Web site are stored on the Web server in an images directory. For educational purposes, Macromedia Shockwave files have been provided to demonstrate the operation of each of the primary pages on the site.
| File | Purpose |
| Images/*.gif | Contains book cover art. |
| Shockwave/*.swf | Contains Macromedia Shockwave files. |
| Styles/style.css | Contains the cascading style sheet for this Web site. |
| AboutApp.asp | Displays information about the sample. |
| AboutPage.asp | Displays information about the current page. |
| BookDetail.asp | Displays detailed information about a title. |
| Cart-Empty.asp | Empties the contents of the shopping cart. |
| Cart-Main.asp | Displays the contents of the shopping cart. |
| CategoryDet.asp | Displays detailed information about a category. |
| CategorySum.asp | Displays a summary of all categories. |
| ChangeTitle.js | Updates the title displayed above each page. |
| CheckOrderStatus.asp | Checks the status of prior orders. |
| Customer.asp | Multi-use; creates, modifies and displays customer information. |
| Default.asp | Contains the frameset used to display all other pages. |
| Home.asp | Displays a welcome message, listing of featured titles, new titles, and best sellers. |
| Menu-Left.asp | Provides site menu functionality. |
| Menu-Top.asp | Displays page titles and provides menu functionality for exploring the sample. |
| Payment.asp | Displays complete order and obtains credit card information from customer. |
| Process.asp | Processes the customer’s order and displays resulting message of success or failure. |
| Shipping.asp | Displays shipping options available for shipping the customer’s order. |
| SignIn.asp | Makes it possible for existing customers to identify themselves to the Web site. |
| TossCookies.asp | Removes Online Bookstore cookies from the customer’s computer. |
| Valiadations.js | Provides functions used in validating data provided in forms. |
| ViewSource.asp | Displays the ASP source used to generate the current page. |
To get a feel for the application, you can walk through it as if you were a new customer purchasing a book and checking on the order status of your new order.
First, the new customer points a Web browser to the Online Bookstore Web site. The Online Bookstore’s home page is displayed in the customer’s Web browser.
Figure 1. Online Bookstore Web site
Behind the scenes, the following steps are taking place:
When the customer clicks the new account button in the left frame, a form for creating a new account is displayed in the customer’s browser.
Figure 2. New account form
Behind the scenes, the following steps are taking place:
When the new customer completes the form and clicks Save to process the form, the customer’s information is displayed with a confirmation message.
Figure 3. Viewing saved information
Behind the scenes, the following steps are taking place:
Now, the new customer reviews the form and clicks Continue to proceed. The main page of the site now displays with the customer’s name.
Figure 4. Main page with new customer's name displayed
Behind the scenes, the following steps are taking place:
When a title from the “Today’s Top Sellers” list is selected, a page with detailed information is displayed.
Figure 5. Book details
Behind the scenes, the following steps are taking place:
The customer reviews the information about the selected title and decides to purchase this book. A page displaying the contents of the shopping cart, which now contains the selected title, is shown.
Figure 6. Viewing shopping cart contents
Behind the scenes, the following steps are taking place:
To start the checkout process, the customer clicks checkout. A page showing the available shipping options is shown first.
Figure 7. Selecting shipping method
Behind the scenes, the following steps are taking place:
The customer selects overnight delivery and clicks Next. The next page displays the completed order with a computed total cost, and requests information about the credit card to be used to pay for the purchase.
Figure 8. Payment information
Behind the scenes, the following steps are taking place:
When the customer is satisfied with the order, clicking Order Now is required to complete the order. A confirmation page displays an order number and other related information.
Figure 9. Order processed
Behind the scenes, the following steps are taking place:
If the customer decides to check on the order status by clicking order status, a page is displayed with the current status of the pending order.
Figure 10. Reviewing order status
Behind the scenes, the following steps are taking place: