The user has hit submit in the order summary page (CheckoutConfirm.asp) and its action has brought us here, to ProcessOrder.asp, where we will extract the information and process the order. The order items, customer name and address and credit card information will be written to the database using the stored procedures we are about to create.
Transaction Integrity
It's very important that the order transaction should succeed completely and
the customer gets the goods they requested, or the transaction must fail unequivocally
and we must handle this failure and deal with it as we are best able. For example
we don't want to add their name and address to the database then find we are
unable to complete the order leaving a name and delivery address in the database
but an order with no items.
The most likely failure is that we have insufficient stock to complete the customer's
order. Although we checked that the stock was available when the customer first
added items to their basket, time has passed since then during which someone
else may have bought the remaining stock. If we are unable to fully complete
the customer's order, because we are short of one or more items, then we need
to inform the customer and give them the option of either proceeding with the
amounts available or canceling completely.
We face certain difficulties. We need to check that sufficient quantities of
each item are in stock. But if we do that first, then try and update the database
we may find in that fraction of a second another concurrent user has got in first
and bought our items.
I have dealt with this by checking the items exist and removing them from the
database in the same stored procedure. But what if the customer has ordered 10
different items and item 9 of the list is out of stock and the customer wants
to completely cancel the order? Do we keep a list of items that have been removed
from the stock table and use that information to put the items back on the shelf
as it were?
It all seems to be getting a little complex and too much like hard work so lets
make it easier for ourselves and enlist the help of Microsoft Transaction Server
and the transaction functionality it makes available to ASP.
The central focus for transactions in ASP is the ObjectContext object. This has
two methods, SetComplete and SetAbort and two events, OnTransactionCommit and
OnTransactionAbort.
If we wish to rollback all the database writes made in a transaction, we call
the SetAbort method. If our transaction processing has completed successfully
then we make a call to SetComplete and all the database changes will become permanent.
Calling SetAbort results in the OnTransactionAbort event firing and SetComplete
in OnTransactionCommit being fired. Note that even if SetComplete is called in
one part of the code, any other part of the code calling SetAbort will cause
the transaction to abort.
Although MTS will rollback all database changes, it does not currently rollback
any other changes that might have occurred, for example a change to a file on
disk. Also, the database must support the XA protocol - a two phase protocol
that allows applications and resource managers to communicate with a transaction
manager - which currently limits it to SQL Server. A further limitation is that
transaction support is only valid for one page, essentially the page becomes
a transaction and this must be committed or aborted before any more pages are
loaded otherwise the transaction will abort.
More Stored Procedures
Before we create the ProcessOrder.asp page we need to create the 3 stored procedures
it requires.
First, we need a stored procedure to add the customer's and the new order's details
to the database. The stored procedure consists of 2 insert statements which add
the data from the forms filled in by the customer. Because we want to make sure
any error is handled and the ASP page's transaction aborted we have added error
checks after each insert. If an error occurs global variable @@Error will contain
its value, otherwise it contains zero. Therefore if @@Error is not zero we end
the stored procedure and return @@Error which our ASP code can pick up as a return
parameter of an ADO Command object.
You'll remember that our Customer and Orders table both have a primary key field
which is an identity field, inserting a row automatically puts the next number
in sequence in those fields. We need to know this number for later when we add
order items or want to get back the customer details. To do this we use the @@Identity
variable which SQL Server populates with the last identity number inserted. Thus,
the @@Identity values for CustId and OrderId are put in the output variables
@CustId and @OrderId which we can access later in the ASP page.
CREATE PROCEDURE [NewOrder] ( @Title varchar(4), @FirstName varchar(50), @LastName varchar(50), @Email varchar(75), @Street varchar(75), @City varchar(50), @Locality varchar(50), @PostCode varchar(15), @Country varchar(50), @CCHolderName varchar(50), @CCType varchar(25), @CCNo varchar(20), @CCExpire varchar(7), @CustId int OUTPUT, @OrderId int OUTPUT) AS -- Insert Customer details INSERT INTO Customer ( Title, FirstName, LastName, Email, Street, City, _ Locality, PostCode, Country) VALUES ( @Title, @FirstName, @LastName, @Email, @Street, @City, _ @Locality, @PostCode, @Country) IF (@@ERROR <> 0) GOTO on_error -- Retrieve the automatically generated CustId VALUE SET @CustId = @@IDENTITY -- Insert order details INSERT INTO Orders (CustId,CCHolderName,CCType,CCNo,CCExpire,OrderDate) VALUES (@CustId,@CCHolderName,@CCType,@CCNo,@CCExpire,GetDate()) IF (@@ERROR <> 0) GOTO on_error -- Retrieve VALUE automatically put into OrderId field SET @OrderId = @@IDENTITY RETURN(0) on_error: RETURN(@@ERROR)
Our second stored procedure is the one that adds each item of the order to the database. First we check stock levels are sufficient to fulfill the order. We raise an error if the levels are too low to let the calling ASP program know. We return zero if everything was successful or the error number otherwise.
CREATE PROCEDURE [AddOrderItem] ( @OrderId int, @ItemId int, @Qty int) AS DECLARE @Return int -- check sufficent stock available IF (SELECT QtyInStock FROM Stock WHERE ItemId = @ItemId) < @Qty BEGIN SET @Return = 547 GOTO on_error END
Next we reduce the stock levels by the order quantity.
-- deduct stock levels UPDATE Stock SET QtyInStock = QtyInStock - @Qty WHERE ItemId = @ItemId IF (@@ERROR <> 0) BEGIN SET @Return = @@ERROR GOTO on_error END
Finally, we add the order item information to the OrderItem table.
-- add item to orderitem table INSERT INTO OrderItem(OrderId,ItemId,Qty) VALUES (@OrderId, @ItemId, @Qty) IF (@@ERROR <> 0) BEGIN SET @Return = @@ERROR GOTO on_error END RETURN 0 on_error: RETURN @Return
Our final stored procedure for this page is ItemAvailability which is used to return stock item information and quantity.
CREATE PROCEDURE [ItemAvailability] ( @ItemId int ) AS SELECT ArtistName, Title, QtyInStock FROM Stock JOIN Artist ON Artist.ArtistId = Stock.ArtistId WHERE ItemId = @ItemId
Before we continue we need to give the database user MMCustomer Execute permissions to the three stored procedures.
Comments