N342 Home Work 4 - Project Databases/XML/XSL

Modified:

Note

IE caches files by default which can prevent opening of a later version. The effect is particularly noticeable during repeated tests with an ASP file that has been changed but is not opened because of a cached version.
The solution is to require IE to check for new versions on every visit to a page. In IE:

Tools | Internet Options | Temporary Internet Files | Settings | Every Visit to Page

When running your ASP, receive error message about "Site may be down, try again later. Etc." IE, by default, does not display errors from a Web server.
Turn OFF friendly error messages by, in IE:

Tools | Internet Options | Advanced | Browsing | Uncheck "Show friendly HTTP error messages

Access denied - Blocked

The following error may occur when accessing files downloaded from one computer to another such as the ZIP files for the assignment. Vista (and perhaps XP) blocks access to files, presumably attempting to protect you from using files you have copied to your computer.
Unblock access to individual files by the following:

Open in Windows Explorer the directory containing the file, either the XML file or one referenced by the XML.

Right click on the file and open Properties.

Click Unblock.

The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.
Access is denied. Error processing resource 'file:///W:/N341/Project/TraderSummaryContent.htm'.

Unblock access to all files in a directory by the following:

Download Streams, copy Streams.exe to Windows directory if possible.

To unblock all the files in directory c:\temp and any subdirectories enter:
Streams –d –s c:\temp

Overview

Generally, Homework 4 is a continuation of Homework 3.

However, for the site to maintain current prices for stock, we introduce the notion of an administrative application, one that is run only by the site administrator.

1. BuyStock.asp

Places stocks to buy in the Cart. At completion, redirects to CartSummary.asp to display the shopping cart contents; there is no need to write a BuyStockContent.htm style sheet.

The stocks are input on the URL address line from the browser. To buy 10 shares of IBM and 15 shares of Microsoft (symbol is MSFT), the address line would be:

http://iu-uits-eiwp1.ads.iu.edu/username/N342/Project/BuyStock.asp?IBM=10&MSFT=15

The stocks and number of shares are placed on the URL line from a form using a GET method. Because we do not know beforehand the number or names of the variables, the Request(variable name) cannot be used. Instead, we can collect stock symbols and shares with the Request.QueryString object.

Request.QueryString.Count is the number of <key, item> pairs on the URL line. There are 2 for the above example.
Request.QueryString.Key(i) is the ith key starting at 1 (e.g. our key 2 is 'MSFT').
Request.QueryString.Item(i) is the ith item value (e.g. our item 2 is 15).

As an example, the following prints out each parameter key and item value. For testing, copy to the Project directory as BuyStock.asp, try executing from the browser with the address line above.

<%@ Language=JScript%>
<%
   var n = Request.QueryString.Count;
   for (i=1; i<=n; i++)
       Response.Write(Request.QueryString.Key(i)+"="+Request.QueryString.Item(i)+"<br>");
%>

Output:

IBM=10
MSFT=15

BuyStock.asp details

http://iu-uits-eiwp1.ads.iu.edu/username/N342/Project/BuyStock.asp?IBM=10&MSFT=15

STOCK table

STOCKID SYMBOL NAME PRICE

1 IBM IBM $15.00

2 MSFT Microsoft $10.00

CART table

ID SYMBOL SHARES PRICE BUY

Ray IBM 10 $15.00 true

Ray MSFT 15 $10.00 true

Each stock in the address line must be placed in the Cart with the ID of the trader, the stock SYMBOL, number of SHARES, PRICE, and BUY=true since the trader is buying.

The fundamental points for implementing BuyStock.asp are:

Request.QueryString.Key value is guaranteed to be a SYMBOL from the Stock table (e.g. Request.QueryString.Key(2)='MSFT').
Each SYMBOL must be SELECTed from the Stock table to retrieve the PRICE of the stock.
ID, SYMBOL, SHARES, PRICE and BUY fields must be INSERTed into the Cart table. The value of the Cart table fields are:
- ID - The Session("trader") variable.
- SYMBOL - Request.QueryString.Key(i)
- SHARES - Request.QueryString.Item(i)
- PRICE - From Stock table.
- BUY - true
When completed redirect to CartSummary.asp.

Testing - When integrated into the stock trading system, the BuyStock.asp script is executed from a HTML form with method="GET". The URL will include ALL named form inputs, not just those that are significant to BuyStock.asp.

Your script should verify, at a minimum, that inputs are non-blank and non-zero before adding to the cart.

For example, the following URL has UAL= and GMC=0; only IBM and MFST stocks should be added to the cart:

http://iu-uits-eiwp1.ads.iu.edu/username/N342/Project/BuyStock.asp?IBM=10&UAL=&MSFT=15&GMC=0

Test for empty string in JavaScript by:

if(Request.QueryString.Item(i) != "")

Note that no check should be made at this time that the trader has sufficient funds to buy stock, checking is performed later at cart checkout.

Hints:

Use the example script above as a starting point for experimentation.
The SELECT from the Stock table must be executed for each SYMBOL in the address line; place inside of the: for (i=1; i<=n; i++)
After each SELECT from Stock need to INSERT ID, SYMBOL, SHARES, PRICE and BUY fields into the Cart table.
The BUY field should be true, without quotes.
Start by displaying the INSERT using Response.Write statement.
All traders can be logged out and session variables deleted by executing Reset.asp script.
http://iu-uits-eiwp1.ads.iu.edu/username/N342/Project/Reset.asp

Testing

Login

http://iu-uits-eiwp1.ads.iu.edu/username/N342/Project/BuyStock.asp?IBM=10&UAL=&MSFT=15&GMC=0

In Access, check the CART table that IBM and MSFT have been added to buy for the trader.

2. SellList.asp and SellListContent.htm

List all the stocks in the Portfolio table for the trader ID and the value of each stock. The script is very similar to Portfolio.asp and testing style sheet can be very similar to TraderSummaryContent.htm file.

To write the SellListContent.htm style sheet, modify the TraderSummaryContent.htm file with the names of the SYMBOL, NAME, SHARES, PRICE, and STOCKVALUE.

The information needed is in two tables so a JOIN is required. Portfolio table holds the trader stock SYMBOL and number of SHARES, Stock table holds the SYMBOL and PRICE. Compute STOCKVALUE as SHARES*PRICE.

The challenge is to write the SQL to SELECT from the two tables based on the common SYMBOL field. Fortunately, what is needed is nearly the same as used in Portfolio.ASP discussed in class. Consult ASP-ADO-XML-XSL discussion on how to Join Database Tables. Test the JOIN using an Access to verify the JOIN outside of the ASP program.

Testing

Login

http://iu-uits-eiwp1.ads.iu.edu/username/N342/Project/SellList.asp

3. SellStock.asp

Places stocks to sell in the Cart. The address line only contains SYMBOLs of stocks owned by the trader from the Portfolio table and the number to sell. When completed redirects to CartSummary.asp to display the shopping cart contents, no need to write a SellStockContent.htm style sheet in XSL.

The script is very similar to the BuyStock.asp with the exceptions:

The Cart table BUY field is INSERTed as false (rather than true) to indicate selling stock.

Note that no check is made at this time that the trader has the stock to sell but the test is performed at cart checkout.

Testing

Login

http://iu-uits-eiwp1.ads.iu.edu/username/N342/Project/SellStock.asp?IBM=10&UAL=&MSFT=15&GMC=0

In Access, check the CART table that IBM and MSFT have been added to sell for the trader.

Hints

Database tables can be examined and hand edited using Access. Access can also be used to execute the SQL that will be used by the ASP script.
All traders can be logged out and session variables deleted by executing Reset.asp script.
http://iu-uits-eiwp1.ads.iu.edu/username/N342/Project/Reset.asp

4. UpdateStock.asp

Updates the PRICE of all stocks in the STOCK table using a RESTful stock quote service. See RESTful services for an example of accessing the stock quote service.

The application is run only by the site administrator to update the stock prices. In practice, administrator applications would be run automatically are some regular time interval.

Note that there is no user input or application output beyond the database table update. Nor is there any login or other security implemented, the assumption is the application would not be accessible to any but the site administrator.

Testing

http://iu-uits-eiwp1.ads.iu.edu/username/N342/Project/UpdateStock.asp

In Access, check the STOCK table that PRICE has been updated.

Hints

To see the XML from the stock quote, try: http://www.google.com/ig/api?stock=AAPL
The basic algorithm is:
rs = SELECT SYMBOL FROM STOCK
while !rs.EOF
    xmlDOM = Get Stock Quote from Google for SYMBOL
    if xmlDOM.getElementsByTagName("last")[0].attributes.getNamedItem("data").nodeValue != "")
        newPrice = xmldoc.getElementsByTagName("last")[0].attributes.getNamedItem("data").nodeValue
        UPDATE STOCK SET PRICE = newPrice
If you get an error similar to the following, the stock SYMBOL in the Stock table may not be valid. You can hand verify by entering a SYMBOL on the URL line for the stock quote.

Microsoft JScript runtime error '800a01a8'
Object required

Assignment

Complete:

Buy stocks from those available (BuyStock.asp) and insert into shopping cart (i.e. table CART).
List stocks owned to sell (SellList.asp).
Sell stocks from those owned (SellStock.asp) and insert into shopping cart (i.e. table CART).
Update prices in table STOCK (UpdateStock.asp) from the RESTful stock quote service.

Turn in - Due before class on the date listed in the syllabus.

OnCourse Drop Box
1. Compress the Project folder containing the homework files.
  - In Windows Explorer, right-click on the Project folder.
  - Click Send to then click Compressed (zipped) Folder.
  - Project.zip will be created.
2. Upload Project.zip to your CSCI N342 OnCourse Drop Box using the Display Name of HW4.
Web server
1. Copy HW4 files to W:\N342\Project
2. Test in IE.
  - In browser address enter: http://iu-uits-eiwp1.ads.iu.edu/username/N342/Project/Welcome.xml
  - Login
  - Verify by entering the address line test for each:
    1. UpdateStock.asp - http://iu-uits-eiwp1.ads.iu.edu/username/N342/Project/UpdateStock.asp
    2. BuyStock.asp - http://iu-uits-eiwp1.ads.iu.edu/username/N342/Project/BuyStock.asp?IBM=10&UAL=&MSFT=15&GMC=0
    3. SellList.asp - http://iu-uits-eiwp1.ads.iu.edu/username/N342/Project/SellList.asp
    4. SellStock.asp - http://iu-uits-eiwp1.ads.iu.edu/username/N342/Project/SellStock.asp?IBM=10&UAL=&MSFT=15&GMC=0
Email notice of completion to rwisman@ius.edu with subject: YOUR NAME - N342 HW4 - username