three approaches to oracle dynamic content with the … · web viewthe three approaches – active...

Three Approaches to Oracle® Dynamic

Content with the Dell™ PowerEdge™ 1650

Enterprise Systems Group (ESG)

Dell White PaperBy Dave Jaffe

[email protected]

June 2002

Contents

Executive Summary..............................................................................3Introduction.........................................................................................4The Application....................................................................................5

The Oracle Stored Procedure..........................................................5The Dynamic Web Pages..................................................................5

Approach 1: Application Server Page...................................................7Approach 2: JavaServer Page................................................................9Approach 3: Oracle PL/SQL Page.......................................................11Conclusions........................................................................................12Acknowledgements.............................................................................13Bibliography.......................................................................................14Appendices.........................................................................................16

Appendix A top10_tab.get_top10...................................................16Appendix B top10_rec.get_top10...................................................20Appendix C top10.asp....................................................................23Appendix D global.asa...................................................................26Appendix E top10.jsp.....................................................................27Appendix F web.xml.......................................................................29Appendix G top10.psp....................................................................30Appendix H Installing Tomcat 4.0.3 on Red Hat Linux 7.2...........32

Figure 1 Output of top10.jsp, top10.asp and top10.psp..................................6

June 2002 Dell Enterprise Systems Group

Section 1Executive Summary

To demonstrate the flexibility of the Dell™ PowerEdge™ 1650 rack-dense server, three different methods of accessing Oracle data were implemented as dynamic web pages on the PowerEdge1650. The three approaches – Active Server Pages running under Microsoft’s Internet Information Server on Microsoft® Windows® 2000 Server, JavaServer Pages running under Tomcat/Apache on Red Hat® Linux® 7.2, or Oracle® PL/SQL Pages running in either environment – all run well on the PowerEdge 1650. The flexibility and cost-effectiveness of the PowerEdge 1650 make it well suited for any kind of application.


Section 2Introduction

The Dell PowerEdge 1650 application server provides a rack-dense, cost-effective gateway to data stored on a backend Oracle database. Configured with one or two 1.4GHz Pentium III processors, dual integrated gigabit Ethernet network interface cards (standard) and up to 4 GB of memory, the 1.75-inches high (1 rack unit or 1U) PE1650 is well suited as the middle tier of a multi-tiered server stack. Available with either Microsoft Windows 2000 Server or Red Hat Linux 7.2, the PowerEdge1650 can host a multitude of mid-tier applications.

The three application interfaces demonstrated in this paper, from the Microsoft, Oracle, and open source/Java camps, represent three of the most popular methods of accessing dynamic content from Oracle databases. All three applications run similarly. Each consists of a single dynamic web page that opens a connection to the backend Oracle database, calls an Oracle stored procedure, and formats the returned data into a Hypertext Markup Language (HTML) page.

All three dynamic pages return user-selected Top 10 lists from a baseball database. In the first approach, top10.asp, a Microsoft Active Server Page running under Microsoft Internet Information Server (IIS) opens an Open Database Connectivity (ODBC) -based connection to the Oracle database. In the second approach, top10.jsp, a JavaServer Page running under Tomcat from the Jakarta project of the Apache Software Foundation, opens a Java Database Connectivity (JDBC) connection into the same Oracle database. The third approach is an all-Oracle solution. An Oracle Procedural Language/Structured Query Language (PL/SQL) page, top10.psp, running under Apache on Oracle 9i™ Application Server calls a slightly different stored procedure in the Oracle database.

Demonstrating the flexibility of the PowerEdge line of servers, all of these applications are operating system neutral. The JavaServer Page implementation in this study was on Red Hat Linux, but Tomcat runs equally well under Windows. Oracle 9i Application Server runs well under either Windows or Linux. Even Active Server Pages can run under Linux/Apache using Chili!Soft!™.ASP (http://www.chilisoft.com).

The Oracle stored procedure and the overall application are discussed in Section 3, The Application. Details of the three implementations follow in the next three sections. All source code is included in Appendices.


Section 3The ApplicationThe Oracle Stored Procedure

The Major League Baseball Top 10 queries are an outgrowth of the work done in Paper 6 in Section 8, Bibliography. Oracle setup and the baseball database organization are described in depth in that publication.

For this study, a new PL/SQL stored procedure, get_top10, was defined. In the first version, in the package top10_tab (see Appendix A, top10_tab.get_top10) a cursor variable is used with 20 different select statements to return the top 10 leaders in any of 20 different statistical categories (home runs, hits, etc.), in any year from 1901 - 2000, and in either the American League, the National league, or both. In this version, the result data (the ten leaders’ first name, last name, team city, team nickname, and the value of the statistic) are returned as five separate Oracle PL/SQL index-by tables (similar to arrays). A slightly different second version of the procedure, in the package top10_rec (see Appendix B, top10_rec.get_top10), is used by the PL/SQL version of the dynamic page and passes the data back as an array of Oracle PL/SQL records, taking advantage of the fact that both the dynamic page and the stored procedure are written in PL/SQL.

In both versions of the stored procedure there are four inputs: input_stat_type, input_year, input_league, and batch_size (usually set to 10), and one scalar output variable, found, indicating the number of rows found for the query (which is always 10 with this data).

To access Oracle on the backend database server, the Oracle9i Database client code must be installed on the PowerEdge1650 hosting the applications. This provides the ODBC or JDBC connector for ASP and JSP pages, respectively. The PL/SQL page requires that Oracle9i Application Server be installed on the PowerEdge 1650. This provides the PL/SQL Gateway using the mod_plsql module of the Oracle HTTP Server Powered by Apache.

The Dynamic Web Pages

The three dynamic pages created for this study all operate similarly. By imbedding code (VBScript, Java or PL/SQL) into HTML, each page fulfills the dual roles of presenting a form to the user and calling the Oracle stored procedure, depending on how it was called. If called without a query string (e.g. http://dell8.ascisp.com/baseball/top10.jsp), a fill-in form with three drop-down menus is created to enable the user to select the statistic,


year and league of interest. Upon the user clicking on the Submit Query button, the form calls the same web page, this time as a GET command with an attached query string (e.g. http://dell8.ascisp.com/baseball/top10.jsp?stat_type=ERA&year=1978&league=AL).

The three implementations are discussed in detail in the following sections. The output of all three dynamic pages is shown in Figure 1.

Figure 1: Output of top10.jsp, top10.asp and top10.psp


Section 4Approach 1: Application Server Page

The Microsoft Application Server Page (ASP) approach utilizes the ActiveX Data Object (ADO) ODBC component of ASP to open a connection to Oracle and accept the returned data as the rows of an ADO Recordset. The top10.asp page, along with a second short program, global.asa, are placed in a directory which is made a virtual directory, /baseball, under IIS, using the Internet Services Manager.

In the top10.asp code (see Appendix C), first the <%@ Language=VBScript %> header is used to define the code interpreter. This designates that any code included between <% … %> pairs will be interpreted as VBScript. Then the ASP Request object is used to retrieve values for the three input parameters: stat_type, year, and league. If this is the first time the page was called these three will be initialized with an empty string. If these parameters have been set an IF-THEN-ELSE IF –ELSE statement is then used to initialize the value of the parameter league_fullname based on the value of league (AL, NL, or _L for both leagues).

At this point in the code, an HTML form is generated to enable the user to select the desired statistic, year and league. A one-line Javascript function is used to generate the years 1902-2000 in the “year” drop-down menu. The HTML title is modified depending on whether this is the first time the page has been accessed or if a query has already been submitted. The action of the form, when Submit Query is pressed, is to call the same page with a GET command, with the three input variables making up the query string.

Following the HTML form, if a query has been submitted (i.e., if the value of stat_type is not an empty string), the result table is initiated with the HTML code for the Table headers, then a large chunk of VBScript opens a connection to the backend Oracle database, calls the top10_tab.get_top10 stored procedure, and places the results into HTML Table definition tags.

First, the conn Connection object is created and used to open the connection to the Oracle database server, ora6450d, using the Microsoft ODBC for Oracle driver. The userid and password are retrieved from the ASP Application object, which is initialized in the global.asa ASP program (see Appendix D), called the first time the application is loaded.

Next, the get_top10 Command object is created and linked to the conn object. The text of the get_top10 object is set to call the stored procedure top10_tab.get_top10 with the five scalar parameters (represented by question marks) and expecting a result set of five 10-row index-by tables representing the first and last name of each


player, the city and nickname of the player’s team, and the value of the player’s statistic:

call top10_tab.get_top10(?,?,?,?,?,{resultset 10, firstname_array, lastname_array, city_array, nickname_array, stat_array})

Five lines of code now define the five scalar parameters to the stored procedure. The first four (input_stat_type, input_year, input_league, and batch_size) are input parameters and the fifth (found) is an output parameter. Following that a Recordset object, rsResult, is created and its Source is set to the get_top10 Command object. The input parameters are filled in with the stat_type, year and league data returned by the HTML form.

Finally, the rsResult.Open line calls the stored procedure and rs.Result is filled in with the elements of the five returned arrays. A While loop is used to generate HTML Table tags with this data. The Recordset and Connection are closed, and control passes back to the HTML page, which is completed and sent to the user.


Section 5Approach 2: JavaServer Page

The open-source JavaServer Page and Servlet engine, Tomcat, from the Jakarta project of the Apache Software Foundation (http://jarkarta.apache.org) was used to provide a container for the JSP version of the application, top 10.jsp (see Appendix E). Red Hat Linux packages with Tomcat 4.0.3 and supporting services were downloaded from the Jakarta site and installed with the Red Hat Package Manager. A context for the baseball application is defined in the server.xml file, and a web.xml file containing the Oracle userid and password is created. Finally, Apache is configured to use the Warp connector to send requests for the /baseball virtual directory to Tomcat. Complete installation details are in Appendix H.

The functionality of top10.jsp is very similar to that of top10.asp, described in the previous section. At the top of the page, the

<%@ page language="java" import="java.sql.*, java.math.*, oracle.jdbc.*, oracle.sql.*” %>

header specifies the Java language as well as tells the page which Java packages to import. As in the ASP page, the request object is then used to retrieve the stat_type, year and league input parameters from the page if the page is being called as a result of the HTML Form being submitted. If this is the first time the page has been called the variables will have the value “null”. This is used in the next piece of code to determine whether to set league_fullname or not.

The HTML Form is then created exactly as in the ASP page. Upon submission the same Oracle stored procedure, top10_tab.get_top10, is called and the resulting data populates the elements of an HTML Table with the Top 10 leaders in the specified statistic, year and league.

The Java statements to access the stored procedure parallel the ASP code. The Oracle Java Database Connectivity (JDBC) driver is first registered and then used to open a connection, conn, to the backend database, using a userid and password obtained from the web.xml file local to the baseball application (see Appendix F). An instance of the OracleCallableStatement class (from oracle.sql), get_top10, is created, associated with conn and defined with the call

begin top10_tab.get_top10(?, ?, ?, ?, ?, ?, ?, ?, ?, ?); end;

In this call the question mark is use to designate both input and output parameters.

The setString or setInt method is then used to bind the input parameters stat_type, year, league and the batch size (10) to the first


http://jarkarta.apache.org/

four variables. The next line registers the first output variable, the number of rows found by the query (always 10), as an integer. The next five lines register the five output index-by tables containing the first name, last name, team city, team nickname and statistic value of the 10 leaders in that specified statistical category for the specified year and league. The OracleType VARCHAR or NUMBER is used to specify the type of date contained in the index-by table.

Finally the stored procedure is executed and the output data is placed into HTML Table tags. The first output parameter, rows_returned, is parsed into an integer, followed by five invocations of the getPlsqlIndexTable method to read the five index-by tables into String or BigDecimal arrays. Then the statement is closed and the HTML Table tags are written using a Java loop. Upon conclusion of the loop the page is completed and sent to the browser.


Section 6Approach 3: Oracle PL/SQL Page

Dynamic web pages implemented in the Oracle Procedural Language/Structured Query Language are the easiest way to call PL/SQL stored procedures and can take advantage of advanced PL/SQL features. In the case of top10.psp (see Appendix G), the dynamic page can handle the PL/SQL record type so it calls a different version of get_top10, in the top10_rec package. This version returns all the data in a single index-by table, each row of which is an Oracle PL/SQL record containing the player’s first and last names, team city and nickname, and statistic value.

Like all Oracle9i Application Server content (such as the Oracle Portal content described in Paper 6 in Section 8, the Bibliography), Oracle stores PL/SQL pages in the backend database along with the data. The command to load the PSP is loadpsp -replace -user baseball/(password)@ora6450d top10.psp

The Oracle PL/SQL Gateway (implemented by the Apache mod_plsql module) creates virtual directories under /pls/(user). This results in a URL such as http://dell8.ascisp.com/pls/baseball/top10 for the top10.psp page.

As with the Active Server Page and the JavaServer Page implementations of the Top 10 dynamic web page, top10.psp consists of HTML interspersed with code inside <% … %> delimiters, in this case, PL/SQL code. As before, the first line of the page, <%@ page language="PL/SQL" %>, specifies the language. The input parameters stat_type, year and league are read from the query string or default to NULL if this is the first time the page is loaded.

The HTML Form is then created with the help of a bit of Javascript as before. The HTML Table to contain the Top 10 list is then initialized. Then, a short PL/SQL procedure (between DECLARE and END;) is used to call top10_rec.get_top10 with the input parameters stat_type, year, league, and requested batch size (10). There are two returned parameters: the number of rows returned, n_ret (always 10), and result_array, an array of records. The elements of the records (e.g. result_array(i).firstname) are placed into HTML Table tags inside a PL/SQL FOR loop, and the whole page is sent to the browser.


Section 7Conclusions

Three different methods of accessing Oracle data were implemented as dynamic web pages on the PowerEdge1650, Dell’s newest 1U, 2P rack-dense server. The three approaches – Active Server Pages, JavaServer Pages or Oracle PL/SQL Pages – all run well on the PowerEdge 1650, and all run under Microsoft Windows 2000 Server or Red Hat Linux 7.2, giving the user the ultimate in flexibility in choosing a programming platform for web access to Oracle content.

THIS WHITE PAPER IS FOR INFORMATIONAL PURPOSES ONLY, AND MAY CONTAIN TYPOGRAPHICAL ERRORS AND TECHNICAL INACCURACIES. THE CONTENT IS PROVIDED AS IS, WITHOUT EXPRESS OR IMPLIED WARRANTIES OF ANY KIND.

Dell, PowerEdge, and PowerVault are trademarks of Dell Computer Corporation. Oracle is a registered trademark and Oracle9i is a trademark of Oracle Corporation. Microsoft and Windows are registered trademarks of Microsoft Corporation. Red Hat is a registered trademark of Red Hat Inc. Linux is a registered trademark of Linus Torvalds.

Other trademarks and trade names may be used in this document to refer to either the entities claiming the marks and names or their products. Dell disclaims proprietary interest in the marks and names of others.

©Copyright 2002 Dell Computer Corporation. All rights reserved. Reproduction in any manner whatsoever without the express written permission of Dell Computer Corporation is strictly forbidden. For more information, contact Dell.

Information in this document is subject to change without notice.


Section 7Acknowledgements

The author would like to thank Todd Muirhead for valuable discussions about the functionality of the dynamic web pages and Sean Lahman for use of his baseball data.


Section 8Bibliography

1) Migrating Sun-Based ISPs to Dell Web Server Appliances. Part 1: Dell PowerApp.web 100 With RedHat Linux

Power Solutions: http://www.dell.com/us/en/biz/topics/power_ps4q00-jaffe.htm

Tech Report (approved for external distribution):http://inside.us.dell.com/products/systems/appliances/pappfamilywp_toc.htm

2) Migrating Sun-Based ISPs to Dell Web Server Appliances. Part 2: Dell PowerApp.web 120 Microsoft Windows Powered

Power Solutions: http://www.dell.com/us/en/biz/topics/power_ps1q01-jaffe.htm

Tech Report (approved for external distribution):http://inside.us.dell.com/products/systems/appliances/pappfamilywp_toc.htm

3) Four Approaches to Dynamic Content With the Dell PowerApp Web Server Power Solutions:

http://www.dell.com/us/en/esg/topics/power_ps2q01-dynamic.htm Tech Report (approved for external distribution):

http://inside.us.dell.com/products/systems/appliances/pappfamilywp_toc.htm

4) Migrating Sun-Based BEA WebLogic Application Servers to Dell PowerEdge Servers. Part 1: Dell PowerEdge 1550 with Microsoft Windows 2000 Server

Power Solutions: http://www.dell.com/us/en/esg/topics/power_ps2q01-jaffe.htm

Tech Report:http://www.dell.com/us/en/esg/topics/products_bea_papers_pedge_software_bea_papers.htm

5) Migrating Sun-Based BEA WebLogic Application Servers to Dell PowerEdge Servers. Part 2: Dell PowerEdge 1550 with Red Hat Linux

Power Solutions: http://www.dell.com/us/en/esg/topics/power_ps3q01-jaffe.htm

Tech Report:http://www.dell.com/us/en/esg/topics/products_bea_papers_pedge_software_bea_papers.htm

6) Migrating Sun-Based Oracle Databases to Dell PowerEdge Servers.Part 1: Dell PowerEdge 6450 and 8450 with Microsoft Windows 2000

Advanced Server Power Solutions:

http://www.dell.com/us/en/esg/topics/power_ps2q02-jaffe.htm Tech Report:


http://www.dell.com/us/en/esg/topics/power_ps2q02-jaffe.htm

http://www.dell.com/us/en/esg/topics/products_bea_papers_pedge_software_bea_papers.htm








http://www.dell.com/us/en/esg/topics/power_ps2q01-dynamic.htm



http://www.dell.com/us/en/biz/topics/power_ps1q01-jaffe.htm



http://www.dell.com/us/en/biz/topics/power_ps4q00-jaffe.htm

http://www.dell.com/us/en/esg/topics/products_oracle_papers_pedge_software_oracle_papers.htm

7) Migrating Sun-Based Oracle Databases to Dell PowerEdge Servers.Part 2: Dell PowerEdge 6450 and 8450 with Red Hat Linux Advanced Server

2.1 Power Solutions:

To be published in August 2002 issue Tech Report:







Appendices

Appendices

Appendix A top10_tab.get_top10

CREATE OR REPLACE PACKAGE top10_tab AS

TYPE ResultRecTyp IS RECORD ( firstname VARCHAR2(25), lastname VARCHAR2(25), city VARCHAR2(15), nickname VARCHAR2(20), stat NUMBER); TYPE ResultCurTyp IS REF CURSOR RETURN ResultRecTyp; TYPE StringArrayTyp IS TABLE OF VARCHAR2(25) INDEX BY BINARY_INTEGER; TYPE NumArrayTyp IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE ResultRecArrayTyp IS TABLE OF ResultRecTyp INDEX BY BINARY_INTEGER;

PROCEDURE get_top10( input_stat_type IN VARCHAR2, input_year IN INTEGER, input_league IN VARCHAR2, batch_size IN INTEGER, found OUT INTEGER, firstname_array OUT StringArrayTyp, lastname_array OUT StringArrayTyp, city_array OUT StringArrayTyp, nickname_array OUT StringArrayTyp, stat_array OUT NumArrayTyp);

END top10_tab;/

CREATE OR REPLACE PACKAGE BODY top10_tab AS

PROCEDURE get_top10( input_stat_type IN VARCHAR2, input_year IN INTEGER, input_league IN VARCHAR2, batch_size IN INTEGER, found OUT INTEGER, firstname_array OUT StringArrayTyp, lastname_array OUT StringArrayTyp, city_array OUT StringArrayTyp, nickname_array OUT StringArrayTyp, stat_array OUT NumArrayTyp) IS

result_array ResultRecArrayTyp;result_cv ResultCurTyp;

BEGIN IF NOT result_cv%ISOPEN THEN CASE input_stat_type WHEN 'HR' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, HR from MASTER M, BATTING B, TEAMMASTER T where M.LAHMANID=B.LAHMANID and B.TEAM=T.TEAM and YEAR=input_year


and LG LIKE input_league order by HR desc nulls last, LASTNAME, FIRSTNAME; WHEN 'BA' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, BA from MASTER M, BATTING B, TEAMMASTER T where M.LAHMANID=B.LAHMANID and B.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league and (AB+BB) > 501 -- to approximate 3.1 Plate Appearances per team games requirement order by BA desc nulls last, LASTNAME, FIRSTNAME; WHEN 'RBI' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, RBI from MASTER M, BATTING B, TEAMMASTER T where M.LAHMANID=B.LAHMANID and B.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by RBI desc nulls last, LASTNAME, FIRSTNAME; WHEN 'H' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, H from MASTER M, BATTING B, TEAMMASTER T where M.LAHMANID=B.LAHMANID and B.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by H desc nulls last, LASTNAME, FIRSTNAME; WHEN 'R' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, R from MASTER M, BATTING B, TEAMMASTER T where M.LAHMANID=B.LAHMANID and B.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by R desc nulls last, LASTNAME, FIRSTNAME; WHEN 'DOUBLES' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, DOUBLES from MASTER M, BATTING B, TEAMMASTER T where M.LAHMANID=B.LAHMANID and B.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by DOUBLES desc nulls last, LASTNAME, FIRSTNAME; WHEN 'TRIPLES' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, TRIPLES from MASTER M, BATTING B, TEAMMASTER T where M.LAHMANID=B.LAHMANID and B.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by TRIPLES desc nulls last, LASTNAME, FIRSTNAME; WHEN 'SB' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, SB from MASTER M, BATTING B, TEAMMASTER T where M.LAHMANID=B.LAHMANID and B.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by SB desc nulls last, LASTNAME, FIRSTNAME; WHEN 'CS' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, CS from MASTER M, BATTING B, TEAMMASTER T where M.LAHMANID=B.LAHMANID and B.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by CS desc nulls last, LASTNAME, FIRSTNAME; WHEN 'SOB' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, SO from MASTER M, BATTING B, TEAMMASTER T where M.LAHMANID=B.LAHMANID and B.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by SO desc nulls last, LASTNAME, FIRSTNAME; WHEN 'BB' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, BB from MASTER M, BATTING B, TEAMMASTER T where M.LAHMANID=B.LAHMANID and B.TEAM=T.TEAM and YEAR=input_year


and LG LIKE input_league order by BB desc nulls last, LASTNAME, FIRSTNAME; WHEN 'AB' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, AB from MASTER M, BATTING B, TEAMMASTER T where M.LAHMANID=B.LAHMANID and B.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by AB desc nulls last, LASTNAME, FIRSTNAME; WHEN 'ERA' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, ERA from MASTER M, PITCHING P, TEAMMASTER T where M.LAHMANID=P.LAHMANID and P.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league and IP > 161 order by ERA asc nulls last, LASTNAME, FIRSTNAME; WHEN 'W' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, W from MASTER M, PITCHING P, TEAMMASTER T where M.LAHMANID=P.LAHMANID and P.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by W desc nulls last, LASTNAME, FIRSTNAME; WHEN 'SOP' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, SO from MASTER M, PITCHING P, TEAMMASTER T where M.LAHMANID=P.LAHMANID and P.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by SO desc nulls last, LASTNAME, FIRSTNAME; WHEN 'SV' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, SV from MASTER M, PITCHING P, TEAMMASTER T where M.LAHMANID=P.LAHMANID and P.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by SV desc nulls last, LASTNAME, FIRSTNAME; WHEN 'PO' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, PO from MASTER M, FIELDING F, TEAMMASTER T where M.LAHMANID=F.LAHMANID and F.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by PO desc nulls last, LASTNAME, FIRSTNAME; WHEN 'A' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, A from MASTER M, FIELDING F, TEAMMASTER T where M.LAHMANID=F.LAHMANID and F.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by A desc nulls last, LASTNAME, FIRSTNAME; WHEN 'DP' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, DP from MASTER M, FIELDING F, TEAMMASTER T where M.LAHMANID=F.LAHMANID and F.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by DP desc nulls last, LASTNAME, FIRSTNAME; WHEN 'E' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, E from MASTER M, FIELDING F, TEAMMASTER T where M.LAHMANID=F.LAHMANID and F.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by E desc nulls last, LASTNAME, FIRSTNAME; END CASE; END IF;

found := 0;


FOR i IN 1..batch_size LOOP FETCH result_cv INTO result_array(i); firstname_array(i) := result_array(i).firstname; lastname_array(i) := result_array(i).lastname; city_array(i) := result_array(i).city; nickname_array(i) := result_array(i).nickname; stat_array(i) := result_array(i).stat; IF result_cv%NOTFOUND THEN -- if no row was found CLOSE result_cv; EXIT; ELSE found := found + 1; END IF; END LOOP;END;END top10_tab;/


Appendix B top10_rec.get_top10

CREATE OR REPLACE PACKAGE top10_rec AS

TYPE ResultRecTyp IS RECORD ( firstname VARCHAR2(25), lastname VARCHAR2(25), city VARCHAR2(15), nickname VARCHAR2(20), stat NUMBER); TYPE ResultCurTyp IS REF CURSOR RETURN ResultRecTyp; TYPE ResultRecArrayTyp IS TABLE OF ResultRecTyp INDEX BY BINARY_INTEGER;

PROCEDURE get_top10( input_stat_type IN VARCHAR2, input_year IN INTEGER, input_league IN VARCHAR2, batch_size IN INTEGER, found OUT INTEGER, result_array OUT ResultRecArrayTyp);

END top10_rec;/

CREATE OR REPLACE PACKAGE BODY top10_rec AS

PROCEDURE get_top10( input_stat_type IN VARCHAR2, input_year IN INTEGER, input_league IN VARCHAR2, batch_size IN INTEGER, found OUT INTEGER, result_array OUT ResultRecArrayTyp) IS

result_cv ResultCurTyp;

BEGIN IF NOT result_cv%ISOPEN THEN CASE input_stat_type WHEN 'HR' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, HR from MASTER M, BATTING B, TEAMMASTER T where M.LAHMANID=B.LAHMANID and B.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by HR desc nulls last, LASTNAME, FIRSTNAME; WHEN 'BA' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, BA from MASTER M, BATTING B, TEAMMASTER T where M.LAHMANID=B.LAHMANID and B.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league and (AB+BB) > 501 -- to approximate 3.1 Plate Appearances per team games requirement order by BA desc nulls last, LASTNAME, FIRSTNAME; WHEN 'RBI' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, RBI from MASTER M, BATTING B, TEAMMASTER T where M.LAHMANID=B.LAHMANID and B.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by RBI desc nulls last, LASTNAME, FIRSTNAME; WHEN 'H' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, H from MASTER M, BATTING B, TEAMMASTER T where M.LAHMANID=B.LAHMANID and B.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by H desc nulls last, LASTNAME, FIRSTNAME;


WHEN 'R' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, R from MASTER M, BATTING B, TEAMMASTER T where M.LAHMANID=B.LAHMANID and B.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by R desc nulls last, LASTNAME, FIRSTNAME; WHEN 'DOUBLES' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, DOUBLES from MASTER M, BATTING B, TEAMMASTER T where M.LAHMANID=B.LAHMANID and B.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by DOUBLES desc nulls last, LASTNAME, FIRSTNAME; WHEN 'TRIPLES' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, TRIPLES from MASTER M, BATTING B, TEAMMASTER T where M.LAHMANID=B.LAHMANID and B.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by TRIPLES desc nulls last, LASTNAME, FIRSTNAME; WHEN 'SB' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, SB from MASTER M, BATTING B, TEAMMASTER T where M.LAHMANID=B.LAHMANID and B.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by SB desc nulls last, LASTNAME, FIRSTNAME; WHEN 'CS' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, CS from MASTER M, BATTING B, TEAMMASTER T where M.LAHMANID=B.LAHMANID and B.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by CS desc nulls last, LASTNAME, FIRSTNAME; WHEN 'SOB' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, SO from MASTER M, BATTING B, TEAMMASTER T where M.LAHMANID=B.LAHMANID and B.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by SO desc nulls last, LASTNAME, FIRSTNAME; WHEN 'BB' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, BB from MASTER M, BATTING B, TEAMMASTER T where M.LAHMANID=B.LAHMANID and B.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by BB desc nulls last, LASTNAME, FIRSTNAME; WHEN 'AB' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, AB from MASTER M, BATTING B, TEAMMASTER T where M.LAHMANID=B.LAHMANID and B.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by AB desc nulls last, LASTNAME, FIRSTNAME; WHEN 'ERA' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, ERA from MASTER M, PITCHING P, TEAMMASTER T where M.LAHMANID=P.LAHMANID and P.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league and IP > 161 order by ERA asc nulls last, LASTNAME, FIRSTNAME; WHEN 'W' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, W from MASTER M, PITCHING P, TEAMMASTER T where M.LAHMANID=P.LAHMANID and P.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by W desc nulls last, LASTNAME, FIRSTNAME;


WHEN 'SOP' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, SO from MASTER M, PITCHING P, TEAMMASTER T where M.LAHMANID=P.LAHMANID and P.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by SO desc nulls last, LASTNAME, FIRSTNAME; WHEN 'SV' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, SV from MASTER M, PITCHING P, TEAMMASTER T where M.LAHMANID=P.LAHMANID and P.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by SV desc nulls last, LASTNAME, FIRSTNAME; WHEN 'PO' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, PO from MASTER M, FIELDING F, TEAMMASTER T where M.LAHMANID=F.LAHMANID and F.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by PO desc nulls last, LASTNAME, FIRSTNAME; WHEN 'A' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, A from MASTER M, FIELDING F, TEAMMASTER T where M.LAHMANID=F.LAHMANID and F.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by A desc nulls last, LASTNAME, FIRSTNAME; WHEN 'DP' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, DP from MASTER M, FIELDING F, TEAMMASTER T where M.LAHMANID=F.LAHMANID and F.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by DP desc nulls last, LASTNAME, FIRSTNAME; WHEN 'E' THEN OPEN result_cv FOR select firstname, lastname, city, nickname, E from MASTER M, FIELDING F, TEAMMASTER T where M.LAHMANID=F.LAHMANID and F.TEAM=T.TEAM and YEAR=input_year and LG LIKE input_league order by E desc nulls last, LASTNAME, FIRSTNAME; END CASE; END IF;

found := 0;

FOR i IN 1..batch_size LOOP FETCH result_cv INTO result_array(i); IF result_cv%NOTFOUND THEN -- if no row was found CLOSE result_cv; EXIT; ELSE found := found + 1; END IF; END LOOP;END;END top10_rec;/


Appendix C top10.asp

<%@ Language=VBScript %>

<% Dim stat_type, year, league, league_fullname stat_type = Request("stat_type") year = Request("year") league = Request("league") league_fullname = "" If stat_type <> "" Then If league = "AL" Then league_fullname = "American League" ElseIf league = "NL" Then league_fullname = "National League" Else league_fullname = "Major Leagues" End if End if%>

<SCRIPT LANGUAGE="JavaScript1.1">function add_year_options() { for (i=1; i<100; i++) document.forms[0].elements[1].options[i]= new Option(1901+i, 1901+i); } </SCRIPT>

<HTML>

<HEAD><%If stat_type = "" Then%> <TITLE>Dell Baseball</TITLE><%Else %> <TITLE>Top 10 <%=stat_type %> Leaders in <%=league_fullname%> in <%=year %> </TITLE><%End if%> </HEAD>

<BODY onLoad="add_year_options(); return true">

<FONT FACE="Arial" COLOR="#0000FF">

<H1 ALIGN=CENTER>Dell Baseball</H1>

<H2>Select a Statistic, Year and League</H2>

<FORM ACTION="/baseball/top10.asp" METHOD="GET" ><SELECT NAME="stat_type" SIZE=1> <OPTION VALUE="HR">Home Run Leaders</OPTION> <OPTION VALUE="BA">Batting Average Leaders</OPTION> <OPTION VALUE="RBI">RBI Leaders</OPTION> <OPTION VALUE="H">Hits Leaders</OPTION> <OPTION VALUE="R">Runs Leaders</OPTION> <OPTION VALUE="DOUBLES">Doubles Leaders</OPTION> <OPTION VALUE="TRIPLES">Triples Leaders</OPTION>

<OPTION VALUE="SB">Stolen Base Leaders</OPTION> <OPTION VALUE="CS">Caught Stealing Leaders</OPTION> <OPTION VALUE="SOB">Strikeout (Batting) Leaders</OPTION> <OPTION VALUE="BB">Base on Balls Leaders</OPTION> <OPTION VALUE="AB">At Bat Leaders</OPTION> <OPTION VALUE="ERA">ERA Leaders</OPTION> <OPTION VALUE="W">Win Leaders</OPTION> <OPTION VALUE="SOP">Strikeout (Pitching) Leaders</OPTION> <OPTION VALUE="SV">Save Leaders</OPTION> <OPTION VALUE="PO">Putout Leaders</OPTION> <OPTION VALUE="A">Assist Leaders</OPTION> <OPTION VALUE="DP">Double Play Leaders</OPTION> <OPTION VALUE="E">Error Leaders</OPTION></SELECT><SELECT NAME="year" SIZE=1> <OPTION VALUE="1901">1901</OPTION> </SELECT><SELECT NAME="league" SIZE=1> <OPTION VALUE="_L">Major Leagues</OPTION> <OPTION VALUE="AL">American League</OPTION> <OPTION VALUE="NL">National League</OPTION></SELECT><INPUT TYPE="submit" VALUE="Submit Query"></FORM>

<%If stat_type <> "" Then%> <HR>

<H2> Top 10 <%=stat_type%> Leaders in <%=league_fullname%> in <%=year%></H2>

<TABLE border=2> <TR> <TH>Rank</TH> <TH>Player</TH> <TH>Team</TH>

<TH>Result</TH> </TR>

<% 'From C:\Program Files\Common Files\System\ado\adovbs.inc adCmdUnknown = 0 adCmdText = 1 adCmdTable = 2 adCmdText = 1 adParamInput = 1 adParamOutput = 2 adInteger = 3 adChar = 129 adUseClient = 3 adOpenStatic = 3

Dim conn Dim get_top10 Dim rsResult

Set conn = Server.CreateObject("ADODB.Connection") conn.CursorLocation = adUseClient conn.Open "DRIVER={Microsoft ODBC for Oracle}; SERVER=ora6450d; UID=" + Application("user") + "; PWD=" + Application("password") Set get_top10 = Server.CreateObject("ADODB.Command") Set get_top10.ActiveConnection = conn get_top10.CommandText = "{call top10_tab.get_top10(?,?,?,?,?,{resultset 10, firstname_array, lastname_array, city_array, nickname_array, stat_array})}" get_top10.CommandType = adCmdText get_top10.Parameters.Append get_top10.CreateParameter("input_stat_type", adChar, adParamInput, 10) get_top10.Parameters.Append get_top10.CreateParameter("input_year", adInteger, adParamInput) get_top10.Parameters.Append get_top10.CreateParameter("input_league", adChar, adParamInput, 10)

get_top10.Parameters.Append get_top10.CreateParameter("batch_size", adInteger, adParamInput) get_top10.Parameters.Append get_top10.CreateParameter("found", adInteger, adParamOutput)

Set rsResult = Server.CreateObject("ADODB.Recordset") rsResult.CursorType = adOpenStatic Set rsResult.Source = get_top10 get_top10("input_stat_type") = stat_type get_top10("input_year") = year get_top10("input_league") = league get_top10("batch_size") = 10 rsResult.Open i=1 While Not rsResult.EOF%> <TR> <TD><%=i%></TD> <TD><%=rsResult(0) + " " + rsResult(1)%></TD> <TD><%=rsResult(2) + " " + rsResult(3)%></TD> <TD><%=rsResult(4)%></TD> </TR><% i=i+1 rsResult.MoveNext Wend rsResult.Close conn.Close Set get_top10 = nothing Set rsResult = nothing Set conn = nothing%>

</TABLE>

<%End if%>

<HR>

<P ALIGN=CENTER>Copyright © 2002 Dell</P>

<HR>

</FONT></BODY>

</HTML>


Appendix D global.asa

<SCRIPT LANGUAGE=VBScript RUNAT=Server>Sub Application_OnStart Application("user") = "baseball" Application("password") = "(password)"End Sub</SCRIPT>


Appendix E top10.jsp



<%@ page language="java" import="java.sql.*, java.math.*, oracle.jdbc.*, oracle.sql.*" %>

<% String stat_type = request.getParameter("stat_type"); String year_str = request.getParameter("year"); String league = request.getParameter("league"); String league_fullname = ""; if (stat_type != null) { if (league.equals("AL")) {league_fullname = "American League";} else if (league.equals("NL")) {league_fullname = "National League";} else {league_fullname = "Major Leagues";} }%>


<HTML>

<HEAD><% if (stat_type == null) { %> <TITLE>Dell Baseball</TITLE> <% } else { %> <TITLE>Top 10 <%=stat_type %> Leaders in <%=league_fullname%> in <%=year_str %> </TITLE> <% } %> </HEAD>





<FORM ACTION="/baseball/top10.jsp" METHOD="GET" ><SELECT NAME="stat_type" SIZE=1> <OPTION VALUE="HR">Home Run Leaders</OPTION> <OPTION VALUE="BA">Batting Average Leaders</OPTION> <OPTION VALUE="RBI">RBI Leaders</OPTION> <OPTION VALUE="H">Hits Leaders</OPTION> <OPTION VALUE="R">Runs Leaders</OPTION> <OPTION VALUE="DOUBLES">Doubles Leaders</OPTION> <OPTION VALUE="TRIPLES">Triples Leaders</OPTION> <OPTION VALUE="SB">Stolen Base Leaders</OPTION> <OPTION VALUE="CS">Caught Stealing Leaders</OPTION> <OPTION VALUE="SOB">Strikeout (Batting) Leaders</OPTION> <OPTION VALUE="BB">Base on Balls Leaders</OPTION> <OPTION VALUE="AB">At Bat Leaders</OPTION> <OPTION VALUE="ERA">ERA Leaders</OPTION> <OPTION VALUE="W">Win Leaders</OPTION> <OPTION VALUE="SOP">Strikeout (Pitching) Leaders</OPTION> <OPTION VALUE="SV">Save Leaders</OPTION> <OPTION VALUE="PO">Putout Leaders</OPTION>

<OPTION VALUE="A">Assist Leaders</OPTION> <OPTION VALUE="DP">Double Play Leaders</OPTION> <OPTION VALUE="E">Error Leaders</OPTION></SELECT><SELECT NAME="year" SIZE=1> <OPTION VALUE="1901">1901</OPTION> </SELECT><SELECT NAME="league" SIZE=1> <OPTION VALUE="_L">Major Leagues</OPTION> <OPTION VALUE="AL">American League</OPTION> <OPTION VALUE="NL">National League</OPTION></SELECT><INPUT TYPE="submit" VALUE="Submit Query"></FORM>

<%if (stat_type != null) {%> <HR>

<H2> Top 10 <%=stat_type%> Leaders in <%=league_fullname%> in <%=year_str%></H2>



<% DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); String user = getServletContext().getInitParameter("user"); String password = getServletContext().getInitParameter("password"); Connection conn = DriverManager.getConnection("jdbc:oracle:oci:"+user+"/"+password+"@ora6450d"); OracleCallableStatement get_top10 = (OracleCallableStatement) conn.prepareCall("begin top10_tab.get_top10(?, ?, ?, ?, ?, ?, ?, ?, ?, ?); end;"); int year = Integer.parseInt(year_str); get_top10.setString(1, stat_type); get_top10.setInt(2, year); get_top10.setString(3, league); get_top10.setInt(4, 10); get_top10.registerOutParameter(5, Types.INTEGER); get_top10.registerIndexTableOutParameter(6, 10, OracleTypes.VARCHAR, 0); get_top10.registerIndexTableOutParameter(7, 10, OracleTypes.VARCHAR, 0); get_top10.registerIndexTableOutParameter(8, 10, OracleTypes.VARCHAR, 0); get_top10.registerIndexTableOutParameter(9, 10, OracleTypes.VARCHAR, 0); get_top10.registerIndexTableOutParameter(10,10, OracleTypes.NUMBER, 0); get_top10.execute(); int rows_returned = Integer.parseInt(get_top10.getString(5)); String[] firstname = (String[]) get_top10.getPlsqlIndexTable(6); String[] lastname = (String[]) get_top10.getPlsqlIndexTable(7); String[] city = (String[]) get_top10.getPlsqlIndexTable(8); String[] nickname = (String[]) get_top10.getPlsqlIndexTable(9); BigDecimal[] stat = (BigDecimal[]) get_top10.getPlsqlIndexTable(10); get_top10.close();

for (int i=0; i<rows_returned; i++) {%> <TR> <TD><%=i+1%></TD> <TD><%=firstname[i] + " " + lastname[i]%></TD> <TD><%=city[i] + " " + nickname[i]%></TD> <TD><%=stat[i]%></TD> </TR><% }

}%>

</TABLE>

<HR>


<HR>

</FONT></BODY>

</HTML>


Appendix F web.xml

(place in directory ../baseball/WEB-INF)

<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN" "http://java.sun.com/dtd/web-app_2_3.dtd">

<web-app> <context-param> <param-name>user</param-name><param-value>baseball</param-value> </context-param> <context-param> <param-name>password</param-name><param-value>(password)</param-value> </context-param></web-app>


Appendix G top10.psp



<%@ page language="PL/SQL" %><%@ plsql parameter="stat_type" type="VARCHAR2" default="NULL" %><%@ plsql parameter="year" type="NUMBER" default="NULL" %><%@ plsql parameter="league" type="VARCHAR2" default="NULL" %>

<% IF stat_type IS NOT NULL THEN %><%! league_fullname VARCHAR2(20); %><%league_fullname := case league when 'AL' then 'American League' when 'NL' then 'National League' else 'Major Leagues' end;%><% END IF; %>


<HTML>

<HEAD> <% IF stat_type IS NULL THEN %> <TITLE>Dell Baseball</TITLE> <% ELSE %> <TITLE>Top 10 <%=stat_type%> Leaders in <%=league_fullname%> in <%= TO_CHAR(year) %> </TITLE> <% END IF; %></HEAD>





<FORM ACTION="/pls/baseball/top10" METHOD="GET" ><SELECT NAME="stat_type" SIZE=1> <OPTION VALUE="HR">Home Run Leaders</OPTION> <OPTION VALUE="BA">Batting Average Leaders</OPTION> <OPTION VALUE="RBI">RBI Leaders</OPTION> <OPTION VALUE="H">Hits Leaders</OPTION> <OPTION VALUE="R">Runs Leaders</OPTION> <OPTION VALUE="DOUBLES">Doubles Leaders</OPTION> <OPTION VALUE="TRIPLES">Triples Leaders</OPTION> <OPTION VALUE="SB">Stolen Base Leaders</OPTION> <OPTION VALUE="CS">Caught Stealing Leaders</OPTION> <OPTION VALUE="SOB">Strikeout (Batting) Leaders</OPTION> <OPTION VALUE="BB">Base on Balls Leaders</OPTION> <OPTION VALUE="AB">At Bat Leaders</OPTION> <OPTION VALUE="ERA">ERA Leaders</OPTION> <OPTION VALUE="W">Win Leaders</OPTION> <OPTION VALUE="SOP">Strikeout (Pitching) Leaders</OPTION> <OPTION VALUE="SV">Save Leaders</OPTION>

<OPTION VALUE="PO">Putout Leaders</OPTION> <OPTION VALUE="A">Assist Leaders</OPTION> <OPTION VALUE="DP">Double Play Leaders</OPTION> <OPTION VALUE="E">Error Leaders</OPTION></SELECT><SELECT NAME="year" SIZE=1> <OPTION VALUE="1901">1901</OPTION> </SELECT><SELECT NAME="league" SIZE=1> <OPTION VALUE="_L">Major Leagues</OPTION> <OPTION VALUE="AL">American League</OPTION> <OPTION VALUE="NL">National League</OPTION></SELECT><INPUT TYPE="submit" VALUE="Submit Query"></FORM>

<% IF stat_type IS NOT NULL THEN %>

<HR>

<H2>Top 10 <%=stat_type%> Leaders in <%=league_fullname%> in <%= TO_CHAR(year) %></H2>



<%DECLAREresult_array top10_rec.ResultRecArrayTyp;n_ret NUMBER;

BEGIN top10_rec.get_top10(stat_type, year, league, 10, n_ret, result_array); FOR i IN 1..n_ret LOOP%> <TR> <TD><%= i %></TD> <TD><%= result_array(i).firstname||' '||result_array(i).lastname %></TD> <TD><%= result_array(i).city||' '||result_array(i).nickname %></TD> <TD><%= result_array(i).stat %></TD> </TR><% END LOOP;END;%>

</TABLE>

<% END IF; %>

<HR>


<HR>

</FONT></BODY>

</HTML>

Appendix H Installing Tomcat 4.0.3 on Red Hat Linux 7.2

- Download RPM packages to /tomcat4:o From http://jakarta.apache.org/builds/jakarta-tomcat-4.0/release/v4.0.3/rpms

regexp-1.2-1.noarch.rpm servletapi4-4.0.3-1.noarch.rpm xerces-j-1.4.4-2.noarch.rpm tomcat4-4.0.3-1.noarch.rpm tomcat4-webapps-4.0.3-1.noarch.rpm tomcat-connectors-1.0-1.4.0.2.src.rpm

o From http://jakarta.apache.org/builds/jakarta-tomcat-4.0/release/v4.0.3/bin/linux/i386/ mod_webapp.so

- As root install RPMs with rpm –ivh in the order listedo User tomcat4 will be created

As root, change tomcat4’s passwdo Tomcat4 home:

As root: chown tomcat4.tomcat4 /var/tomcat4o In /var/tomcat4/conf

Create tomcat4_start: /etc/rc.d/init.d/tomcat4 start Create tomcat4_stop: /etc/rc.d/init.d/tomcat4 stop (only root can start and stop tomcat)

o In /etc/rc.d/rc3.d: Enter: ln –s ../init.d/httpd S85httpd; ln –s ../init.d/tomcat4 S95tomcat4 Enter: ln –s ../init.d/httpd K15httpd; ln –s ../init.d/tomcat4 K05tomcat4

o Add following environment variables to user tomcat4:JAVA_HOME=/usr/java/jdk1.3.1_02ORACLE_HOME=/u01/app/oracle/product/9.0.1PATH=$PATH:/usr/local/sbin:/usr/sbin:/sbin:/usr/local/bin:$JAVA_HOME/bin:$J2EE_HOME/bin:$HOME/bin:$ORACLE_HOME/bin:.CLASSPATH=$JAVA_HOME/lib/tools.jar:$ORACLE_HOME/jdbc/lib/classes12.jar:.LD_LIBRARY_PATH=/u01/app/oracle/product/9.0.1/lib

- As this point tomcat4 should serve web pages on port 8180 (Apache is installed but not running). Test tomcat4 by surfing http://dell8.ascisp.com:8180o Should see Tomcat Version 4.0.3 home page /var/tomcat4/webapps/ROOT/index.html

- To deploy a JavaServer Page at /var/tomcat4/webapps/baseball:o add to /var/tomcat4/conf/server.xml:

<Context path="/baseball" docBase="baseball" debug="0" reloadable="true" crossContext="true"> <Logger className="org.apache.catalina.logger.FileLogger" prefix="localhost_baseball_log." suffix=".txt" timestamp="true"/></Context>

o Stop/start tomcat4 every time you change a config fileo To create parameter file, add web.xml to directory

/var/tomcat4/webapps/baseball/WEB-INF:<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN" "http://java.sun.com/dtd/web-app_2_3.dtd"><web-app> <context-param> <param-name>user</param-name><param-value>baseball</param-value> </context-param> <context-param> <param-name>password</param-name><param-value>(password)</param-value> </context-param></web-app>

o Read parameters into app with String password = getServletContext().getInitParameter("password");

o Test JSP with http://dell8.ascisp.com:8180/baseball/top10.jsp- To use Warp connector to connect Apache to Tomcat4:

o Copy mod_webapp.so to /etc/httpd/moduleso Add following lines to /etc/httpd/conf/httpd.conf:

ServerName dell8.ascisp.comLoadModule webapp_module modules/mod_webapp.soAddModule mod_webapp.cWebAppConnection conn warp localhost:8008WebAppDeploy baseball conn /baseball

o Restart apacheo You should now be able to access apps as

http://dell8.ascisp.com/baseball/top10.jsp


three approaches to oracle dynamic content with the … · web viewthe three approaches – active...

Documents