The objective of this assignment is to let you play around with database applications, especially in a Web environment.
Due: 09 May 2003
You will need:
These three files can be downloaded together in this zip file. You will also need to install Perl as explained below.
While databases are powerful tools--as you've seen in A4 in generating things like reports on the most profitable country--most users don't use SQL themselves. They use applications connected to the database. We are going to write an "application" program to generate the original "blue book" view from SDNeeds. We will make this a web application to also give you some experience with that domain. Given an order number, it will return all the info in the "blue book" view related to that order.
For a web architecture, we will use the following:
Countless variations are possible with different languages or technologies. We will install each piece and then check its connection with the next piece. Once we have the architecture up and running, we'll turn to the actual application program.
ApacheOracle comes with Apache (modified and embellished) already installed. The apache program is at: c:\oracle\ora90\Apache\Apache\apache.exe
(As in all the following examples, your path may be slightly different. But it should be close enough that you can find where it is you're supposed to be.)
You can start and stop Apache through the Start Menu (Start->Programs->Oracle -> Oracle HTTP Server -> Start HTTP). (Or you can use the Dos command prompt by moving into c:\oracle\ora90\Apache\Apache and typing "apache" to start the server.) You should see something like this:
Oracle HTTP Server Powered by Apache/1.3.22 (Win32) mod_plsql/220.127.116.11.3b mod_ssl /2.8.5 OpenSSL/0.9.6b mod_fastcgi/2.2.12 mod_oprocmgr/1.0 mod_perl/1.25 running. ..
(If this didn't happen, see the Q&A section below.) When you're ready to shut the server down, press Ctrl-C.
Now, to make sure Apache is really working, open your browser and go to http://localhost:7778/ (Normally, the 7778 isn't necessary with Apache, but Oracle has configured it to run on that port.) You should see the default web page with links to local documentation of all the pieces of the Oracle HTTP server.
You need to download and install ActiveState's version of Perl (if you don't already have it). (The download page is at http://www.activestate.com/Products/Download/Download.plex?id=ActivePerl if you want to learn more about what you're getting into.)
Get this MSI file so you can easily uninstall Perl later. To install this MSI file, you will most likely need to run a Windows Installer update. In that case, you will need InstMsiW.exe for NT platforms. Run InstMsiW.exe first, then double-click ActivePerl-18.104.22.1686-MSWin32-x86.msi to start the install process.
During the install: you only need Perl (not the examples, or server, or PerlScript), though it won't hurt anything to have it all. Make sure you keep the option checked to set the path for you. Perl will be installed at c:\perl, with the interpreter at c:\perl\bin\perl.
Once you have it installed, check that it works. Type "perl hello.cgi" in the same directory as your copy of hello.cgi. You should get a couple lines of text containing a "Hello World" message surrounded in a bit of HTML.
You now know that Apache works and Perl works. Now we need to get them to work together. Copy hello.cgi into c:\oracle\ora90\Apache\Apache\cgi-bin Now, make sure the path at the beginning of the hello.cgi file points to your perl interpreter. While the Apache server is running, point your browser to http://localhost:7778/cgi-bin/hello.cgi You should see a simple "Hello, World!"
Now that you have the web server side of things working, we need to get the database side to work--specifically, ODBC. We need to create a DSN (data source name) as mentioned in lecture. Go to Start->Settings->Control Panel->Administrative Tools->Data Sources (ODBC). Click the System DSN tab. Add... Oracle in OraHome90 (or your closest equivalent). This will open a new window for extra data. Give it the following:
Data Source Name: SnarkDreams
Leave all the other settings as they are. Click Test Connection and give your password. You should get a "Connection successful"! This means ODBC can talk to Oracle. Click all the OKs on the way out to save your changes.
Finally, we need to get Perl talking to the ODBC. To do this, we will use the Win32::ODBC module for perl. Luckily, it comes with ActiveState and is already installed. Open the file odbctest.pl and fill in your username and password in the appropriate variables. Save, and then type "perl odbctest.pl". You should get a confirmation that Perl can connect to Oracle.
Hopefully installing all these components has given you a sense of what is going on here. A web server doesn't do much processing on its own. It hands any processing work off to scripting languages or other server-side processes, such as Perl, PerlScript, ASP, JSP, PHP, etc, etc. There are many options. However, to get the data they need, these scripting languages or server programs need to talk to the database. They can do this through native calls, but it's easier to go through ODBC. Doing so means that if you change DBMSs, you won't have to change your applications (much). Also, there are more FAQs and other resources to help you out with interfacing with a standard like ODBC.
You can add other pieces to this basic architecture. You can add OLE DB to give a more object-oriented front to ODBC. You can include a number of web modifications or servlets to help your scripting languages run faster. But hopefully you get the basic idea.
Now that we're sure the architecture is all up and running, we can use it.
Take a look at orderview.cgi. As I said, this script will create a view like that in the "blue book" in SDNeeds. I know Perl code is a little weird, but I think the comments should give you a good idea of what's going on. Most of it is error-checking and formatting the output. The parts that interact with database are actually quite simple. There's basically four functions that Win32::ODBC uses to get all the info: new(), which creates a new connection, Sql(), which gets the table resulting from an single SQL statement, FetchRow(), which gets the next row in that table, and DataHash(), which turns that row into a set of key/value pairs for easier processing.
So, what do you have to do? At the top of the script are a couple parameters for your username and password. Then there are three variables for SQL statements, which you need to provide. Remember, each needs to be a single SQL statement. Perl doesn't mind about white space, including line breaks, so feel free to format the SQL so it's readable. Don't include a closing semi-colon for the SQL within the "quotes". You may want to write these first in SQLPlus so you can see what sort of data you're getting back.
Copy orderview.cgi into c:\oracle\ora90\Apache\Apache\cgi-bin. Make sure the path to Perl at the beginning of the file is correct. With Apache running, go to http://localhost:7778/cgi-bin/orderview.cgi and play around with it. Save the webpages resulting for SDNumber 5 and for one other order of your choice.
What to submit
Orderview.cgi has markers for where to begin and end a cut. Cut and paste this information--your user info and 3 SQL statements--and paste it into an email. Also attach to that email the two produced webpages you saved-- one for order 5 and one for another order of your choice.
Let me know if you have any questions.
Questions & Answers
This assignment is worth 10 points.
############################################# start CUT $DSN="SnarkDreams"; $username="ztomasze"; $password="ztomasze"; #Provide the three needed sql statements below. $SQLforCustomer ="select * from Customer where customer_ID IN (select customer_ID from SDORDER where SDNumber = $sdno)"; $SQLforOrder_Prod = "select order_prod.quantity, product.description from order_prod, product where order_prod.sdnumber = $sdno and product.name = order_prod.name"; $SQLforOrder = "select sdorder.*, kagi_no from sdorder, kagi_no where sdorder.sdnumber = $sdno and sdorder.sdnumber = kagi_no.sdnumber(+)"; ############################################# end CUT
|~ztomasze Index :
TA Details: ICS321: Assignment 5
|Last Edited: 08 May 2003|
©2002 by Z. Tomaszewski.