Assignment 5
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.
Overview
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.
Architecture setup
Apache
Oracle 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/3.0.9.8.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.
Perl
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-5.8.0.806-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.
CGI
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!"
ODBC (DSN)
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
TNS Service Name: ORCL (from the drop down menu)
UserID: [whatever your oracle userID is]
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.
Perl's Win32:ODBC
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.
Conclusion
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.
Orderview.cgi,
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.
- $SQLforCustomer
- Give me all the customer's information for this order.
- $SQLforOrder_Prod
- Give me all the items ordered in this order. Yet I want you to be selective--give me only the description (from Product) and the quantity (from Order_Prod) for each item.
- $SQLforOrder
- Give me all the order details for this order. But this should also include the Kagi_No! You'll need to do an outer join so that you will still get results when there is no Kagi_No for the order. (Hint: Oracle's outer join notation uses "(+)").
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
- I don't know or like Perl. I know Java/VB/whatever better.
- I am open to you using different architecture components if you want to. I'd even consider giving extra credit for it (2 to 3 points). If you're interested, send me an email first. You would have to send me the source code of your dynamic page (such as an ASP page) or other server processing, a copy of the resulting webpage (which must contain ALL of the same info collected by orderview.cgi), and a brief note explaining what technologies you used and how they fit together.
- Apache is not working. I get the following error:
[alert] (5)Access is denied: FastCGI: CreateMutex() failed
Syntax error on line 92 of c:/oracle/ora90/sqlplus/admin/isqlplus.conf:
failed to create FastCGI application accept mutex
- Open c:\oracle\ora90\Apache\Apache\conf\oracle.apache.conf and comment out the line that includes isqlplus.conf. You may have to type "apache -k shutdown" a couple times at the command prompt or restart your computer so you can kill any running copies of apache before you can restart Apache with the new configuration.
- Apache is not working. I get the following error:
[warn] pid file c:/oracle/ora90/apache/apache/logs\httpd.pid
overwritten -- Unclean shutdown of previous Apache run?
[crit] Apache JServ encountered a fatal error; check your
ApJServLogFile for details if none are present in this file. Exiting
. - Apache creates the file apache/logs/httpd.pid every time it runs. Delete that file and you should not get the warning about an Unclean shutdown. Try starting Apache again. If it still doesn't work, you can turn of JServ, since you do not need it for this assignment. To do that, go through apache/conf/oracle.apache.conf and apache/conf/httpd.conf and comment out any line that refers to JServ. Restart Apache.
- Why are we installing Perl when it already comes with Oracle?
- It's true that Perl can be found at: C:\oracle\ora90\Apache\perl\5.00503\bin\MSWin32-x86\perl However, installing ActiverPerl is easier. (Trust me.) First, it will set your path for you; and it's a much easier path too. More importantly, it comes with Win32::ODBC already installed and configured. It's easier to install ActiverPerl than it is Win32::ODBC. However, you can do it however you want as long as both pieces work.
- It was working, but now I have an Internal server error 500!
- It probably means you messed up the Perl code and orderview.cgi won't run. In c:\oracle\ora90\Apache\Apache\cgi-bin, type "perl -c orderview.cgi" to see if you can find where the error is. Otherwise, "reinstall" orderview.cgi.
Grading
This assignment is worth 10 points.
- 3 points for sending me what was requested: the section of orderview.cgi and two attached webpages.
- 7 points for the correct SQL statements and corresponding results.
Solution
############################################# 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