A Real Example Using Perl and Oracle

This document was last modified

In the Oracle account cs290adm there is a database named big_ten that looks like this:

name city state enrolled founded nickname colors joined
Illinois Champaign IL 35000 1867 Fighting Illini Orange and Blue 1896
Indiana Bloomington IN 35551 1820 Hoosiers Cream and Crimson 1899
Iowa Iowa City IA 27051 1847 Hawkeyes Old Gold and Black 1899
Michigan Ann Arbor MI 36617 1817 Wolverines Maize and Blue 1896
Michigan State East Lansing MI 41545 1855 Spartans Green and White 1949
Minnesota Minneapolis MN 39000 1851 Golden Gophers Maroon and Gold 1896
Northwestern Evanston IL 7400 1851 Wildcats Purple and White 1896
Ohio State Columbus OH 48300 1870 Buckeyes Scarlet and Gray 1912
Penn State University Park PA 40471 1855 Nittany Lions Blue and White 1990
Purdue West Lafayette IN 35156 1869 Boilermakers Old Gold and Black 1896
Wisconsin Madison WI 39826 1848 Badgers Cardinal and White 1896

name	city	state	enrolled	founded	nickname	colors	joined
Illinois	Champaign	IL	35000	1867	Fighting Illini	Orange and Blue	1896
Indiana	Bloomington	IN	35551	1820	Hoosiers	Cream and Crimson	1899
Iowa	Iowa City	IA	27051	1847	Hawkeyes	Old Gold and Black	1899
Michigan	Ann Arbor	MI	36617	1817	Wolverines	Maize and Blue	1896
Michigan State	East Lansing	MI	41545	1855	Spartans	Green and White	1949
Minnesota	Minneapolis	MN	39000	1851	Golden Gophers	Maroon and Gold	1896
Northwestern	Evanston	IL	7400	1851	Wildcats	Purple and White	1896
Ohio State	Columbus	OH	48300	1870	Buckeyes	Scarlet and Gray	1912
Penn State	University Park	PA	40471	1855	Nittany Lions	Blue and White	1990
Purdue	West Lafayette	IN	35156	1869	Boilermakers	Old Gold and Black	1896
Wisconsin	Madison	WI	39826	1848	Badgers	Cardinal and White	1896

Searching the Database from a Perl Script run in Unix

Let us examine the Perl script big-ten.pl.

Notice that there is NO...

do "cgi-lib.pl" || die "Cannot implement cgi-lib. \n";
&ReadParse;

...in this Perl script because it does not get any data from an HTML form.

The lines...

$sUsername = "cs290w\@csdb";
$sPassword = "cs290w";

...specify the Username and Password you must use to logon to the Oracle account created for CS 290W students. The Username is "cs290w@csdb". The "\" is necessary so that Perl will treat the "@" symbol as just a regular character.

Notice that the first SELECT is...

$hQuery = 
&ora_open($hConnection, 
"SELECT * FROM cs290adm.big_ten");

This gets all data (*) from the big_ten table in the cs290adm account. The cs290adm is important on this. It would not work as... $hQuery = &ora_open($hConnection, "SELECT * FROM big_ten");
...because then Oracle would assume that big_ten is in the cs290w account and it is NOT.

After getting all data from the big_ten table, the subroutine fDisplay is called. What does it do?

sub fDisplay
{
 @amValues = &ora_fetch($hQuery))
 while (@amValues != 0)
  {
  $sName=$amValues[0];
  $sCity=$amValues[1];
  $sState=$amValues[2];
  $iEnrolled=$amValues[3];
  $iFounded=$amValues[4];
  $sNickname=$amValues[5];
  $sColors=$amValues[6];
  $iJoined=$amValues[7];
  print "$sName $sCity $sState $iEnrolled $iFounded \n";
  print "$sNickname $sColors $iJoined \n\n";
  @amValues = &ora_fetch($hQuery))
  }
 print "***************************************** \n";
}

It does an &ora_fetch($hQuery) -- that is, it uses the handle $hQuery that it received from the &ora_open and places all values received into the array @amValues. After going through the loop some number of times (maybe zero), the &ora_fetch will receive no values ... causing the while loop to exit. Inside the body of the while loop, note that the fDisplay subroutine places each of the array values into an appropriate scalar and then prints them all.

The second Select Is...

$hQuery = 
&ora_open($hConnection, 
"SELECT * FROM cs290adm.big_ten WHERE state = 'IN'");

...which will retrieve all data for Big Ten schools in Indiana.

The third Select Is...

$hQuery = 
&ora_open($hConnection, 
"SELECT * FROM cs290adm.big_ten WHERE enrolled > 30000");

...which will retrieve all data for Big Ten schools with enrollments exceeding 30000 students.

The fourth Select Is...

$hQuery = 
&ora_open($hConnection, 
"SELECT * FROM cs290adm.big_ten WHERE enrolled > 30000 AND
founded < 1855");

...which will retrieve all data for Big Ten schools with enrollments exceeding 30000 students AND which were founded before 1855.

The final Select Is...

$hQuery = 
&ora_open($hConnection, 
"SELECT * FROM cs290adm.big_ten WHERE colors LIKE '%Gold%'");

...which will retrieve all data for Big Ten schools whose school colors contain the character string "Gold" in it anywhere.

Searching the Database from a Perl Script called as a cgi Program

Take a look at the HTML File Big Ten Search Engine. If you look at the source you will notice the following: Why was this not just... Because then it would be run by the Web server on icdweb -- which does not have access to Oraperl. This is important. Unless you do it this way, you will receive a fatal server error!

Notice that the three variables are named "st", "enr", and "found" respectively. More about them later.

Now let us look carefully at the Perl script big-ten-srch.cgi. At the top notice...

This is all necessary to parse and use data sent from the form and to search the big_ten database.

The code above grabs the character string "st" from the form and stores it as $sStateCode, as well as the numbers "enr" and "found" from the form and stores them as $iEnr and $iFound respectively. The next three lines make the searches work for any items left blank.

Replacing $sStateCode with a string in which it is preceded and followed by % signs lets this work for the LIKE criterion. In particular, this converts a blank into "%%" (meaning match all states) and one character like "I" into "%I%" (meaning match all states with the letter I anywhere in the state abbreviation.

Finally, if $iEnr and $iFound are left blank they are converted to 0 and 5000 respectively since all schools have an enrollment greater than 0 and were founded before the year 5000. (Note that I have just created a Y5K problem!)

...uses all three search criteria and returns from the database only those entries (if any) that satisfy them all. You could, also, of course in very complex situations get entries back from the database, examine them, and then decide whether to display them or not.

The subroutine fDisplay is a little different from the one in big-ten.pl:

It tries to fetch a first set of values. If this fails, it displays on the Web page a sensitive, caring, and politically correct message stating that "your search did not produce any results".

If there is at least one set of values, it displays all rows in a do-while statement.