RSS
| << 17.1.3- Transactions and Databases | Chapter17 | 17.1.5- Transaction Scope >> |
Transactional ASP Pages
Ever since the integration of IIS 4.0 and MTS, ASP developers have been able to place ASP scripts within a transaction. These transactional ASP pages can include calls to server components that will also participate within the same transaction. These transactions are just like all transactions – if any one part of the transaction fails, then the entire transaction will be rolled back. The advantage of transactional ASP scripts is that it makes it easy to tie multiple components together into one single transaction.
Normally, all of the processing for a single transaction can be done on a single ASP page. So if you want to write an ASP that uses a number of different components within a single component, you can do so by including all the logic and method calls for that transaction within a single transactional ASP page.
To declare an ASP page as transactional, you would use the @TRANSACTION directive. For example:
<%@TRANSACTION = Required %>
Here, we've set the value of the @TRANSACTION directive to Required. In fact, there are four possible values:
- Not_Supported – Tells COM+ that the page will not join a transaction, even if one already exists.
- Supported – Tells COM+ not to start a transaction, but that the ASP page will join one if it already exists.
- Required – Tells COM+ to start a new transaction if one does not exist, or to join an existing one.
- Requires_New – Tells COM+ to start a new transaction for the ASP page, even if one already exists.
These values have the same effect as the settings for the transaction participation states that can be set for a component, using the COM+ administration tool. We'll see this later in the chapter.
If you're including an ASP page in a transaction, then the @TRANSACTION directive must be on the first line of the ASP page. If you put anything in the page before the @TRANSACTION directive, then you'll get a script error when you try to run the page.
If an ASP page is transactional, but the transaction finishes without having cast any votes for 'failure' or 'success', then COM+ will assume that the transaction has succeeded – and it will arrange for the changes to be written to disk. Also, if any objects are used within a transactional ASP page, then they can (and usually do) use the same context object of the ASP page to participate in the transaction.
The ObjectContext Object
When coding a transactional ASP script, you may want to be able to affect the outcome of the transaction in which the ASP page is executing. You can do that by casting a vote on the outcome of the transaction. For this, we are provided with a special object called the ObjectContext object.
The ObjectContext object has been available since the release of IIS 4.0 and ASP 2.0. It provides the functionality for all of the transaction-handling routines that a developer may need. This is exactly the same object that the server components participating in the transaction will be accessing, and features the SetAbort and SetComplete methods:
- We use the SetAbort method to cast a 'transaction failure' vote, and hence to tell COM+ that we think the transaction should not be completed. COM+ will then arrange for all the changes made during the transaction to be rolled back.
- We use the SetComplete method to cast a 'transaction success' vote, and hence to tell COM+ that we cannot see any reason that the transaction should not be completed. If every component participating in the transaction calls the SetComplete method, and none of the components call the SetAbort method, then COM+ will know that the transaction has completed successfully and that all of the changes must be written to disk.
Note that when we write components for use in transactions, they too will use the SetAbort and SetComplete methods to cast their votes on how they feel the transaction should complete.
So let's have a look at a short step-by-step example of an ASP script that shows both the @TRANSACTION directive and the use of the ASP ObjectContext object:
<%@ TRANSACTION=Required%>
<HTML>
<%
Dim objA, objB
Set objA = Server.CreateObject("MyDll.MyClass")
Set objB = Server.CreateObject("MyDll.MyClass")
If objA.Go = 0
ObjectContext.SetAbort
Elseif objB.Go = 0 Then
ObjectContext.SetAbort
Else
ObjectContext.SetComplete
End If
%>
</HTML>
This a deliberately simplified example just to show how SetComplete and SetAbort might be used:
- At the top, you can see the @TRANSACTION directive which indicates that the content of the page must be executed within a transaction – this means that COM+ will monitor the votes cast and will decide at the end whether the transaction is successful or not.
- Next, we declare two variables (objA and objB) and we use Server.CreateObject to create two instances of a fictional component called MyDll.MyClass
- Next, we call the Go method of the first object. If it returns the value 0, then we opt to vote for 'transaction failure'
- Next, we call the Go method of the second object. Similarly, we only vote for 'transaction failure' if the method returns the value 0
- If neither objA.Go not objB.Go returned the value 0, then we cast a vote for 'transaction success'
Whatever happens here, we actually only cast one vote in this example. The transaction finishes at the end of the ASP page's output – at that time, COM+ will analyze all the votes (in this case, there's one vote to check from the ASP page, plus any votes cast by the components during the execution of the Go method). Then COM+ will commit or roll back the transaction based on the votes.
Transaction Events
Within our ASP pages, we can listen to COM+ at the time it analyzes the votes and judges on whether the transaction should be committed or aborted – and it can react according to the decision that COM+ makes. In order to do this, we use two special transactional event hanlders, called OnTransactionCommit and OnTransactionAbort. When COM+ knows its decision, IIS listens to that decision and executes one of these two routines just before COM+ actually commits or aborts the transaction.
The OnTransactionCommit event handler is fired just before COM+ commits a transaction. If COM+ decides to abort the transaction then the OnTransactionAbort event handler will fire. Here's an example of what the code for these event handlers might look like:
<%@ TRANSACTION=Required%>
<%
... ASP content goes here.
... May call SetComplete or SetAbort any number of times during this ASP block.
... May also use components that contain calls to SetComplete and/or SetAbort.
'fires if the transaction commits
Sub OnTransactionCommit
Response.Write "<HTML>"
Response.Write "The transaction committed."
Response.Write "</HTML>"
End Sub
'fires if the transaction aborts
Sub OnTransactionAbort
Response.Write "<HTML>"
Response.Write "The transaction aborted."
Response.Write "</HTML>"
End Sub
%>
Like most transactional ASP pages, this page starts by declaring that it requires a transaction. Then the script performs some actions; when the page has finished, COM+ will determine the outcome of the transaction by checking whether any part of the transaction called the SetAbort method.
If the SetAbort was not called then the OnTransactionCommit event will fire, and the transaction will be committed. Otherwise the OnTransactionAbort will be called and the transaction will be rolled back.
OK, it's time to exercise all we've learned using an example. As we said at the beginning of this chapter, there are no home-made components in this chapter – we'll control the entire transaction using logic coded using ASP and VBScript. COM+ will manage the transaction behind the scenes, and decide on the success or failure of the transaction as a whole (by analyzing the votes that occur in the form of calls to the SetComplete and SetAbort methods).
Try it Out – A Transactional Active Server Page
In this example, we will create an HTML form page and a response ASP page that will let us move money from one bank account to another. This example will use transaction processing to ensure that the balances in the two accounts maintain their ACID properties if there is a problem.
As we explained above, our choice of database is important here – in particular, the example won't work using a Microsoft Access database because there is no resource manager. We'll explain how to complete this example using the MSDE (or SQL Server).
Remember that, if you wish, you can download all the code for these examples from the Wrox web site.
1. First, we'll set up the two server-side include (SSI) files that are needed for this example. The first of these is BankConnection.asp – this will contain the connection string that's necessary when we use the ADO Connection and Recordset objects in this example. So, create a new file in your editor, and add the following code to it:
<%
Dim strConn, strDatabaseType
' Use one of these lines, and comment out the other
strDatabaseType = "MSDE"
' strDatabaseType = "SQLServer"
' Now use that to set the connection string
If strDatabaseType = "MSDE" Then
strConn = "Provider=SQLOLEDB;Persist Security Info=False;" & _
"User ID=sa;Initial Catalog=Bank;" & _
"Initial File Name=C:\MSSQL7\Data\Bank.mdf"
Else
strConn = "Provider=SQLOLEDB; Data Source= my_server_name; Database=Bank; " & _
"User ID=sa; Password=;"
End If
%>
Save this file into your \inetpub\wwwroot\BegASPFiles folder, with the name BankConnection.asp.
2. If you're using MSDE then you'll need to place the file Bank.mdf into the C:\MSSQL7\Data\ folder, as specified above.
On the other hand, if you're using SQL Server then you'll need to set up the Bank database. One way is to use SQL Server's Import and Export Utility to import the data from the Bank.mdb file into SQL Server (Bank.mdb is provided as part of the supporting source code for this book, at www.wrox.com, and you'll also need to insert your own data server name in place of my_server_name above.
Whichever you're using, you'll need to be sure that you've selected the correct value for the string strDatabaseType, in the file BankConnection.asp.
Note again that it's not possible to run this example using a Microsoft Access database, because it does not have support for transactions.
3. Now here's the second of our two SSIs – it's a file called BankFunctions.asp, which contains the code for the five predefined functions that we'll use in the body of our example (as you'll see shortly). So create a second new file in your editor, and add the following code to it:
<%
' Function isAccountValid checks that the user has provided valid account details
Function isAccountValid(AccountID)
Dim rsAcct
strSQL = "SELECT AccountID FROM Account " & _
"WHERE AccountID='" & AccountID & "';"
Set rsAcct = dcBank.Execute (strSQL)
If Not rsAcct.EOF Then
isAccountValid = True
Else
isAccountValid = False
End If
rsAcct.Close
Set rsAcct = Nothing
End Function
' Function Balance requests balance details from the database
Function Balance(AccountID)
Dim rsBalance
strSQL = "SELECT ResultingBalance FROM Register " & _
"WHERE AccountID='" & AccountID & "' ORDER BY SeqID DESC;"
Set rsBalance = dcBank.Execute (strSQL)
Balance = rsBalance("ResultingBalance")
rsBalance.Close
Set rsBalance = Nothing
End Function
' Function AddMoney transfers the cash from the source account balance
' to the destination account balance
Function AddMoney(acctID, amount, CheckNum)
' positive amount for deposit, negative amount for withdrawl
Dim strTransType
If amount < 0 Then
strTransType = "W"
Else
strTransType = "D"
End If
Dim curBalance
curBalance = Balance(acctID)
strSQL = "INSERT INTO Register " & _
"(AccountID, CheckNumber, Transdate, TransType, " & _
" Amount, PriorBalance, ResultingBalance) " & _
"VALUES ('" & acctID & "'," & CheckNum & ",'" & Date & "','" & _
strTransType & "'," & amount & "," & curBalance & "," & _
CDbl(curBalance + amount) & ");"
dcBank.Execute strSQL
End Function
' Sub onTransactionCommit explains what to do when a transaction commits
Sub onTransactionCommit()
Response.Write "<HR><B>Resulting Balances:</B><BR>" & _
"Source Account ID: <B>" & srcAccountID & "</B> " & _
"Balance: <B>$" & srcBalance & "</B><BR>" & _
"Destination Account ID: <B>" & destAccountID & "</B> " & _
"Balance: <B>$" & destBalance & "</B>" & _
"<HR>[ <A href='BankHome.asp'>Write Another Check</A> ] " & _
"</BODY>" & _
"</HTML>"
If Not dcBank Is Nothing Then
If dcBank.State = adStateOpen Then
dcBank.Close
End If
Set dcBank = Nothing
End If
End Sub
' Sub onTransactionAbort explains what to do when a transaction is aborted
Sub onTransactionAbort()
Response.Write "<HR>" & _
"Transaction Aborted<BR>" & _
"Reason: " & strAbortReason & _
"<HR>[ <A href='bankHome.asp'>Write Another Check</A> ]" & _
"</BODY>" & _
"</HTML>"
If Not dcBank Is Nothing Then
If dcBank.State = adStateOpen Then
dcBank.Close
End If
Set dcBank = Nothing
End If
End Sub
%>
4. Save BankFunctions.asp in your BegASPFiles directory.
5. Now we'll create the file that allows the user to input values into this example. Using the editor of your choice, create a file called BankHome.asp and add the following code to it:
<!-- #INCLUDE FILE="BankConnection.asp" -->
<HTML>
<HEAD>
<TITLE>Rocks Banking for Programmers</TITLE>
</HEAD>
<BODY>
<%
Dim dcBank, rsBank
Set dcBank = Server.CreateObject("ADODB.Connection")
Set rsBank = Server.CreateObject("ADODB.Recordset")
dcBank.Open strConn
rsBank.Open "SELECT * FROM Account", dcBank
Dim strOptionString
strOptionString = ""
Do Until rsBank.EOF
strOptionString = strOptionString & _
"<OPTION VALUE='" & rsBank("AccountID") & "'>" & _
rsBank("HolderName") & " [" & rsBank("AccountID") & "]" & _
"</OPTION>"
rsBank.MoveNext
Loop
rsBank.Close
dcBank.Close
Set rsBank = Nothing
Set dcBank = Nothing
%>
<H2>Rocks Banking for Programmers</H2>
<H3>Cash Transfer Request - Please enter the following details:</H3>
<FORM ACTION="ExecuteTransaction.asp" METHOD="POST">
Select source account:
<SELECT NAME="SourceAccount"> <%= strOptionString %></SELECT><BR>
Select destination account:
<SELECT NAME="TargetAccount"> <%= strOptionString %></SELECT><BR>
Enter check number: <INPUT TYPE="TEXT" NAME="CheckNum"><BR>
Enter check amount: $<INPUT TYPE="TEXT" NAME="AmtToTransfer"><BR><BR>
<INPUT TYPE="SUBMIT" VALUE="Process This Check">
</FORM>
</BODY>
</HTML>
6. Save BankHome.asp into your BegASPFiles directory.
7. Now we'll create the main file in our little application – this last page is called ExecuteTransaction.asp. Create one more new file in your editor and add the following code to that file:
<%@ TRANSACTION=REQUIRED %>
<!--#INCLUDE FILE="BankConnection.asp"-->
<!--#INCLUDE FILE="BankFunctions.asp"-->
<!-- METADATA TYPE="typelib"
FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->
<HTML>
<HEAD><TITLE>Rocks Banking for Programmers</TITLE>
</HEAD>
<BODY>
<H2>Rocks Banking for Programmers</H2>
<H3>This page will execute your Cash Transfer Request, and report success or
failure</H3>
<%
Dim dcBank, srcAccountID, destAccountID
Dim strSQL, intCheckNum, dblAmtToTransfer
Dim srcBalance, destBalance
Dim strAbortReason
intCheckNum = Request.Form("CheckNum")
dblAmtToTransfer = Request.Form("AmtToTransfer")
srcAccountID = Request.Form("SourceAccount")
destAccountID = Request.Form("TargetAccount")
If srcAccountID = destAccountID Then
ObjectContext.SetAbort
strAbortReason = "Can't transfer funds when " & _
"the source and destination account are the same"
Response.End
End If
Set dcBank = Server.CreateObject("ADODB.Connection")
dcBank.Open strConn
If Not isAccountValid(srcAccountID) Or Not isAccountValid(destAccountID) Then
ObjectContext.SetAbort
strAbortReason = "Invalid account numbers"
Response.End
End If
srcBalance = Balance(srcAccountID)
destBalance = Balance(destAccountID)
Response.Write "<B>Prior Balances:</B><BR>" & _
"Source Account ID: <B>" & srcAccountID & "</B> " & _
"Balance: <B>$" & srcBalance & "</B><BR>" & _
"Destination Account ID: <B>" & destAccountID & "</B> " & _
"Balance: <B>$" & destBalance & "</B><HR><BR>" & _
"We are about to transfer <B>" & FormatCurrency(dblAmtToTransfer) & _
"</B> from the source account to the destination account...<BR>"
AddMoney srcAccountID, -dblAmtToTransfer, intCheckNum
AddMoney destAccountID, dblAmtToTransfer, intCheckNum
srcBalance = Balance(srcAccountID)
destBalance = Balance(destAccountID)
If srcBalance < 0 Or destBalance < 0 Then
' Account out of Balance - Roll Back
ObjectContext.SetAbort
strAbortReason = "Insufficient Funds in Source Account to Perform Transfer"
Else
' Accounts in Balance - Commit Transaction
ObjectContext.SetComplete
End If
%>
Note that this page uses the five functions that we defined in BankFunctions.asp, earlier in this example.
8. Save ExecuteTransaction.asp into your BegASPFiles directory.
|
9. Using your browser, open the BankHome.asp page:
|
|
10. Select a source account and a destination account, and type in a check number and the amount that you want to transfer. (For this first effort, we'll transfer $100.00 from account 4735-9275 to account 9832-5830 – there should be enough in the source account to support this transfer without going overdrawn!) Then press the Process This Check button to begin the transfer. You should see a page like this:
|
|
11. Note the balance that remains in the source account – we'll use that information to try a transaction that fails. To try a second transaction, press the Write Another Check link. This time, select the same source account (and any other destination account); and enter a check amount that is greater than the balance that currently remains in the source account. Then click the Process This Check button to begin the transaction:
|
|
As expected, the transaction failed and was aborted – and this page tells us that. Conveniently, it also explains why the transaction failed.
Let's take a look at what's happening when a transaction fails – and also when one completes successfully.
How it Works
Our first page, BankHome.asp, is very similar to the form that we used in the final example of Chapter 16 . We're using an HTML form in this page to handle the request:
<FORM ACTION="ExecuteTransaction.asp" METHOD="POST">
...
</FORM>
We're using the ExecuteTransaction.asp page to handle this request, so we specify that in the attributes for the <FORM>.
In order to facilitate the user's choice of account ID (and to reduce the possibility of errors), we will offer the user a choice of only those accounts that exist in our data store. The list of account IDs is contained in an HTML string called strOptionString – this string is used twice in the form to create a list of HTML <OPTION> tags for the drop-down listboxes in the form. We covered this technique in detail in the second example of Chapter 16.
But we want to move on to the most important new feature in this example, which is implemented in the ExecuteTransaction.asp page. This page is responsible for both the database access and the business rule processing. The database access will allow for reading and updating of the Register table, in the database. The business rules are the rules of the system – in this case, business rule processing means making the following validity checks on the cash transfer before the transfer itself actually takes place:
- First, that the balance on any account must not fall below 0
- Second, that the source and destination accounts chosen by the user are different accounts
If the user requests a cash transfer that fails to adhere to these two business rules, then the transfer will not take place – it will be aborted.
In order to achieve this, we will perform our database updates within the scope of a transaction. As we saw earlier, the @TRANSACTION directive will determine the transaction characteristics of the page. The value of REQUIRED will ensure that this page is executed within the context of a transaction, and that any objects used by this page will have a chance to participate in the transaction as well:
<%@ TRANSACTION=REQUIRED %>
Then we follow good programming practice, by declaring our variables before we use them. Four of these local variables will be used to hold the values passed to the page via the Request.Form collection, and we also assign those values straight away:
<%
Dim dcBank, srcAccountID, destAccountID
Dim strSQL, intCheckNum, dblAmtToTransfer
Dim srcBalance, destBalance
Dim strAbortReason
intCheckNum = Request.Form("CheckNum")
dblAmtToTransfer = Request.Form("AmtToTransfer")
srcAccountID = Request.Form("SourceAccount")
destAccountID = Request.Form("TargetAccount")
These four values are identity numbers for the source and target accounts, the check number for the check being processed, and the cash amount that the user wishes to transfer. These are the values that the user entered and submitted via the HTML form in the BankHome.asp page.
Of course, this is a technique that we've seen elsewhere in the book. We'll be using each of these values more than once during the course of this ASP page, so it makes sense to assign the values to local variables. They make the code more readable; more importantly, they avoid the need for repeated trips to search the Request.Form collection for values, so they actually make our code more efficient!
Applying the Business Rules – Comparing Account Numbers
Now it's time to get to the meat of this example. In the next part of ExecuteTransaction.asp, we're going to start checking the details of the requested transfer to ensure that it meets the rules of the system – the business rules. If the transfer meets these rules, then we'll allow it to complete – otherwise, we will force any changes made in relation to that transfer to be rolled back.
So our first step is to compare the ID values of the source and target accounts, and confirm that they are indeed different accounts. This is basically to prevent spurious entries from clogging up the database – because we don't want to waste system resources executing redundant transfers and storing their details. So we use an If ... Then ... End If block to compare the account ID values, and to take action if they are the same:
If srcAccountID = destAccountID Then
ObjectContext.SetAbort
strAbortReason = "Can't transfer funds when " & _
"the source and destination account are the same"
Response.End
End If
Here, if the accounts are the same, then the transfer shall not be allowed to continue. So the first thing we need to do is abort the transaction (using the SetAbort method). By calling the SetAbort method, we are registering a vote that states that the transaction should abort (i.e. that it should not complete).
Now, as we mentioned earlier, it only needs one 'abort' vote to abort the transaction – so if the account IDs were the same then we know that the transaction will be doomed to failure. But we also want to let the user know why the transaction has failed. To do this, we will write a descriptive error message, and store it in a string called strAbortReason. In this case, we tell the user that they can only transfer funds if they choose different accounts for the source and destination.
Having called SetAbort and written the abort reason, there are three ways that we could deal with the rest of the ASP script. Before we look at the method we've used here (the Response.End method), let's consider the other two options:
- Since we know that the transaction will be aborted in the end, we could let the rest of the script run to completion, and then rely on the rollback of the changes to the database to get back to the starting condition. But this just amounts to a lot of needless processing, because we'd just be making lots of changes in the knowledge that we were going to undo them again at the end. Let's avoid that.
- We could have built the entire page on a series of nested If ... Then ... Else statements, which would have avoided the unnecessary processing but would look clumsy and confusing, so let's avoid that too.
The solution that we do use in this example relies on two characteristics of ASP itself. First, as you can see in the code above, we use the Response.End to force the execution of the page to stop as soon as we reach the error condition. But don't forget that we still want to output the text of our strAbortReason string. We could do that after we've assigned the strAbortReason string and before we call Response.End, but this would again make for a good deal of cluttered code. So instead, we make use of a second ASP tool – by using one of the transactional events.
Here's what happens. Normally, when Response.End is called, the execution of the ASP script ends immediately. But in the occasion of a transacted ASP page, the Response.End causes the transactional event to be fired. This causes the contents of the event handler to be run, and they will form the last piece of code executed on the page. In this case, we've called SetAbort – so the TransactionAbort event will be raised and the contents of the OnTransactionAbort() event handler will run.
It is in this method that we can output the appropriate error message to the user. Here's our code for OnTransactionAbort():
Sub onTransactionAbort()
Response.Write "<HR>" & _
"Transaction Aborted<BR>" & _
"Reason: " & strAbortReason & _
"<HR>[ <A href='bankHome.asp'>Write Another Check</A> ]" & _
"</BODY>" & _
"</HTML>"
If Not dcBank Is Nothing Then
If dcBank.State = adStateOpen Then
dcBank.Close
End If
Set dcBank = Nothing
End If
End Sub
As you can see, if the transaction aborts (for any reason) then we simply write a message to the user explaining what went wrong, and we close the HTML. Then, optionally (if we've been using the ADO Connection object dcBank), we also tidy up its resources.
Applying the Business Rules – Validating Account Numbers
The next thing we do is check that the account numbers selected by the user are valid – i.e. that they exist and represent valid account in our data store. To so this, we first establish a connection to the data store, and create a recordset:
Set dcBank = Server.CreateObject("ADODB.Connection")
dcBank.Open strConn
Now we make a quick check on the account numbers provided by the user, and if there is a problem with either of them then we abort the transaction:
If Not isAccountValid(srcAccountID) Or Not isAccountValid(destAccountID) Then
ObjectContext.SetAbort
strAbortReason = "Invalid account numbers"
Response.End
End If
This is quite similar to the If ... Then ... End If structure we saw a moment ago: essentially, it says "if there's a problem with an account number, then vote to abort the transaction, declare the reason, and wrap up the page's execution". When Response.End is called, this fires the TransactionAbort event, and the OnTransactionAbort() event handler will fire (as we described above).
In order to validate the account number given, we're using a pre-written helper function called isAccountValid (whose code is contained in the SSI BankFunctions.asp). Here it is:
Function isAccountValid(AccountID)
Dim rsAcct
strSQL = "SELECT AccountID FROM Account " & _
"WHERE AccountID='" & AccountID & "';"
Set rsAcct = dcBank.Execute (strSQL)
If Not rsAcct.EOF Then
isAccountValid = True
Else
isAccountValid = False
End If
rsAcct.Close
Set rsAcct = Nothing
End Function
This function takes a single parameter that represents the account ID that we want to examine. The function will query the database using a SQL query, which creates a recordset containing any record in the Account table whose AccountID field matches the value passed as a parameter. If the Recordset object returned by this query is empty (i.e. EOF is True), then we can conclude that the account doesn't exist – in this case the function will return False. If the Recordset object is not empty, then we can conclude that the value passed represents a valid account – and in this case the function will return True.
The Pre-Transfer Message
Now we return to the ExecuteTransaction.asp file. If the transaction reaches this point without aborting, then we know that the account numbers are valid and different. Then, we can use another helper function, Balance(), to fetch the balance values of these two accounts from the data store, and assign them to a couple of local variables, whose values we can then display to the user:
srcBalance = Balance(srcAccountID)
destBalance = Balance(destAccountID)
Response.Write "<B>Prior Balances:</B><BR>" & _
"Source Account ID: <B>" & srcAccountID & "</B> " & _
"Balance: <B>$" & srcBalance & "</B><BR>" & _
"Destination Account ID: <B>" & destAccountID & "</B> " & _
"Balance: <B>$" & destBalance & "</B><HR><BR>" & _
"We are about to transfer <B>" & FormatCurrency(dblAmtToTransfer) & _
"</B> from the source account to the destination account...<BR>"
Let's have a look at the Balance() helper function, whose code is contained in the BankFunctions.asp SSI file:
Function Balance(AccountID)
Dim rsBalance
strSQL = "SELECT ResultingBalance FROM Register " & _
"WHERE AccountID='" & AccountID & "' ORDER BY SeqID DESC;"
Set rsBalance = dcBank.Execute (strSQL)
Balance = rsBalance("ResultingBalance")
rsBalance.Close
Set rsBalance = Nothing
End Function
This function accepts a single parameter – the account ID of one of our bank accounts – and returns the balance for that account. The balance of the account is determined by the latest entry for that account in the Register table. Don't forget that we've already validated the account number, using the isAccountValid() function; so we don't need to confirm the validity of the account number again here. Our query returns an ADO Recordset object, which contains all of the register entries sorted into reverse SeqID order. This means that the latest entry in the register (the one with the current balance) will be the first record in the recordset. We do that because the recordset's cursor is automatically placed onto the first record in the recordset – which is the record that we're interested in.
Making the Transfer
Returning to the ExecuteTransaction.asp page, once we've completed our pre-transfer message to the browser, it is time to make the transfer itself. As we've noted, the transfer of money from one account to another involves two discrete steps:
AddMoney srcAccountID, -dblAmtToTransfer, intCheckNum
AddMoney destAccountID, dblAmtToTransfer, intCheckNum
The first is to deduct the amount from the source account: this is done by calling the AddMoney() helper function, passing in the ID of the source account, the amount of the check as a negative number, and the check ID. The second step is to add the amount to the destination account: this is done by calling the AddMoney() function again, this time specifying the destination account's ID, the amount as a positive number, and the check ID again.
Let's take a look at the AddMoney() helper function. Its purpose is to perform an update to the Register table for a specified account. Whether we're deducting an amount from the account's balance, or adding an amount to an account's balance, the processes required are almost exactly the same – we're just adding a negative or positive number to the account's balance. So if we're making a deduction then we pass in the amount as a negative value; if we're making a credit then we pass in a positive value:
Function AddMoney(acctID, amount, CheckNum)
' positive amount for deposit, negative amount for withdrawl
Dim strTransType
If amount < 0 Then
strTransType = "W"
Else
strTransType = "D"
End If
Dim curBalance
curBalance = Balance(acctID)
strSQL = "INSERT INTO Register " & _
"(AccountID, CheckNumber, Transdate, TransType, " & _
" Amount, PriorBalance, ResultingBalance) " & _
"VALUES ('" & acctID & "'," & CheckNum & ",'" & Date & "','" & _
strTransType & "'," & amount & "," & curBalance & "," & _
CDbl(curBalance + amount) & ");"
dcBank.Execute strSQL
End Function
The AddMoney function accepts three parameters – the ID of the account whose balance we want to change, the amount by which we want to change the balance, and the check number associated with the change. The first step of this function is to determine whether the function is dealing with a deduction or a credit (this is based on the sign of the amount). This information will be added to the new record that we create in the Register table. Next, we need to know the current balance in the account, so that we can compute the new balance after the transaction. To do this, we simply use the Balance() helper function to retrieve the value.
Once we know the starting balance, we can build a SQL command that will perform the task of updating the account's balance. We'll do this by using a SQL INSERT command to add a new record to the Register table. The values that will be inserted include the type of the transaction (withdrawal or deposit), a date/time stamp, the balance prior to the transaction, and the balance after the transaction. Once we have created the SQL command, we process it by passing it to the Execute method of our ADO Connection object.
There are two parts to the transaction – the deduction and the credit – so remember that we call the AddMoney() method twice during the execution of ExecuteTransaction.asp. When we call the AddMoney() function and pass a negative value in the amount parameter, the function actually deducts money from the specified account.
Checking for Overdrawn Accounts
Now we return to ExecuteTransaction.asp once again. Once the transfer is made, we update the values of the variables that hold the current account balances:
srcBalance = Balance(srcAccountID)
destBalance = Balance(destAccountID)
Now, the AddMoney() method (that we used to update the balances) is an unintelligent method. All it does is change the balance of an account as specified by the parameters – it doesn't check the validity of such changes. It is up to us to apply the business logic, to check that the resulting balance in each account is valid within our system. As it happens, the business rules that govern our system dictate that no account must go overdrawn – the balance of any account must be greater than or equal to zero at all times.
So the next thing to do is check that this is the case for the new balance values of both accounts. If either account balance has dipped below zero, then we will need to abort the transaction, roll back the changes we've made and report the reason for the failed transfer:
If srcBalance < 0 Or destBalance < 0 Then
' Account out of Balance - Roll Back
ObjectContext.SetAbort
strAbortReason = "Insufficient Funds in Source Account to Perform Transfer"
Isn't transaction processing great? In the case above, we have made changes to the database, and then determined that some business rule has been violated – so we use SetAbort to vote for 'transaction failure', and that's enough to ensure that the transaction will be rolled back.
All we need to do is register a vote for 'transaction failure', by calling the SetAbort method, and that will roll back the transaction and undo those changes from the database. Without transaction processing, we would have to write all kinds of code to track the changes made to the database, and then back those changes out if it was necessary.
Well that's what happens if an account balance dips below zero. What happens if both account balances remain healthily above zero? In that case, the transaction has succeeded, and will be committed:
Else
' Accounts in Balance - Commit Transaction
ObjectContext.SetComplete
End If
We vote for 'transaction completion' by calling the SetComplete method. This casts our vote to make the transaction permanent in the database.
Unless we called the Response.End method at any point, the execution of the page finishes at the end of the ASP page. It's at this point that the transaction ends too – so the system must count up all the votes and decide whether the transaction was successful or not. Remember, if there were any votes for 'transaction failure' then the transaction will fail, and the TransactionAbort error message will fire: the onTransactionAbort() event handler will be executed, and any changes that were made will be rolled back.
Recall that the voting system for a transaction is not a 'majority' system. The transaction will fail unless the votes are unanimously in favor of 'transaction success'. A majority verdict is not good enough!
If there were no votes for 'transaction failure', then the transaction is a success, and the changes that we made to the data store can be committed and made permanent and durable (the transaction processor will arrange all that – we don't have to worry about it). The TransactionCommit event will fire, and the onTransactionCommit() event handler will be executed to handle the event. We haven't seen onTransactionCommit() yet, so let's look at it now:
Sub onTransactionCommit()
Response.Write "<HR><B>Resulting Balances:</B><BR>" & _
"Source Account ID: <B>" & srcAccountID & "</B> " & _
"Balance: <B>$" & srcBalance & "</B><BR>" & _
"Destination Account ID: <B>" & destAccountID & "</B> " & _
"Balance: <B>$" & destBalance & "</B>" & _
"<HR>[ <A href='BankHome.asp'>Write Another Check</A> ] " & _
"</BODY>" & _
"</HTML>"
If Not dcBank Is Nothing Then
If dcBank.State = adStateOpen Then
dcBank.Close
End If
Set dcBank = Nothing
End If
End Sub
In an earlier step, after the transaction was processed, we queried the database for the balances of the accounts. This was stored in a local variable and then the value was checked to make sure it was valid (not negative). Since these are the ending balances in the accounts, we can use these values to display the final balances to the user in the event handler. We will also provide a link for the user to click on if they wish to process another transfer.
The last step will be to close the connection to the database, and then release the connection object. You may think that this is superfluous, in that when the page ends the reference to the connection will be deleted, and the connection will automatically be closed. But good programming practice dictates that we should explicitly close all database objects, and then release the references to those objects, rather than relying on the system to do it for us.
You can see from the two example scripts that were run that if the business rules said the processing was OK, then a confirmation message will be printed, and the data in the database will be changed. If there was a business logic error, as would have happened if we tried to write a check for more money than we have, then the error would be displayed for the user, and any changes to the database would have been rolled back.
COM+ Can't Roll Back your Script Code
Before we move on to look at transaction scope, there's one more point to note in this section. We've talked about how, in the event of an aborted transaction, COM+ tells your database's resource manager to organize the rollback of any changes that have been made during the course of the transaction. However, you should be aware that COM+ is unable to rollback changes made during the transaction within the script itself – for instance, if the ASP page made changes made to session or application variables, they will not be automatically rolled back by COM+ when the transaction aborts.
One way round this is to use the transactional event handlers – particularly onTransactionAbort() – to manually rollback any changes to your script variables that COM+ cannot perform automatically.
| << 17.1.3- Transactions and Databases | Chapter17 | 17.1.5- Transaction Scope >> |
