Name_______________________________________

SS#____________________________Database Name _______

e-mail address__________________________________________

1. (50 points). The Kazoo Company wishes to provide customers the ability to submit product orders using XML. The firm has provided its customers with the following XML DTD to utilize when sending orders:

<?xml version="1.0"?>
<!DOCTYPE kazoo-order [
<!ELEMENT kazoo-order (buyer_name, address, city, state, zip, month, day, year, payment_type, product+)
<!ELEMENT buyer-name (#PCDATA) >
<!ELEMENT address (#PCDATA) >
<!ELEMENT city (#PCDATA) >
<!ELEMENT state (#PCDATA) >
<!ELEMENT zip (#PCDATA) >
<!ELEMENT month (#PCDATA) >
<!ELEMENT day (#PCDATA) >
<!ELEMENT year (#PCDATA) >
<!ELEMENT payment_type (#PCDATA) >
<!ELEMENT product ( product_code, quantity, price_per_unit) >
<!ELEMENT product_code (#PCDATA) >
<!ELEMENT quantity (#PCDATA) >
<!ELEMENT price_per_unit (#PCDATA) >
]

The following two tables are used on the server:

A table named ord is part of DSN named T1. The table structure is: buyer varchar(30) order_id int line_number int address varchar(50) city varchar(30) state char(2) zip char(5) month (2) day char(2) year char(4) p_type char(1) prod_code int quant int p_p_u decimal (12,2) primary key (order_id, line_number)

As second table o_id stores a unique integer for the order number. The table structure is: buyer varchar(30) order_id int primary key (order_id)

An asp program named proc is used to process incoming orders. Customers send orders using the XMLHTTP protocol (i.e., the user has a page with script that sends the XML data as a string to the proc.asp program). The program performs the following functions in a single pass:

1. the program receives the xml data from the user. The program has the following code to receive the XML:

          Set xmlReq = Server.CreateObject("Microsoft.XMLDOM")
          xmlReq.load(Request)
          if xmlReq.parseError <> 0 then
               Response.write xmlReq.parseError.reason
          end if

2. A function named get_val has been written for you. This function is passed: (1) the object xmlReq and (2) a tag name. It returns the value (i.e., content) of the tag. This is used to retreive the values in the XML for the non-repeating items (i.e., buyer_name through payment_type).

3. A sub named get_lines has also been written for you. When called, it fills an array named fred (defined globally). fred should be dimensioned in your program to contain 100 rows and 3 columns. fred is used to store one row for each product ordered. The columns contains: product_code; quantity; and price_per_unit. There is also a global variable named num_rows that is set in get_lines that counts the number of rows (i.e., products) ordered. The sub is passed the object xmlReq.

4. Each request creates one or more rows in the SQL-Server ord table. The primary key component order_id is created by generating a random integer up to 6 digits (i.e., order_id =cint(1000000*rnd). Use "randomize" to create a new seed number each time. The order_id is then stored in the o_id table. Check to be sure this order_id hasn't been used before. If it has, generate another and test again until a unique order_id is created. The program should then create one row in the ord table for each product ordered. The data for buyer through payment_type is obtained from get_val. The specific product data is obtained from the table fred that was filling in by get_lines. Each product ordered is assigned a line number. The number of table rows to be added is set by the global variable num_rows in the sub get_lines. line_number begins with 1 and is incremented by 1.

5. The program then returns to the XMLHTTP request the entire customer's order information (including order_id and the line_number values). The format of the response data should be: one HTML line per non-repeating data item (its description followed by its value) followed by a table with one row for each product. Each table row contains four columns (the columns are: line_number, product, quantity, price_per_unit). Provide column headings for the table. Include a grand total for the entire purchase (i.e., the sum of all quantity * price_per_unit). The response value is a single string (i.e., all the HTML created should be stored and returned as one string).

2. (5 points) Write the function get_val.

3. (5 points) Write the sub get_lines.