Untitled presentation CPS 616 January-April 1996 Technologies for the Information Age: Oracle RDBMS and Web Integration Web/Oracle Module 3: Integration of WWW and Oracle RDBMS Instructor: Geoffrey Fox Version 5 March 1997 teamed with Gang Cheng, Chao-Wei Ou Syracuse University 111 College Place Syracuse New York 13244-4100 Abstract of Web/Oracle Module 3: Integration of WWW and Oracle RDBMS The Strengths, Weaknesses and Synergy of Web and Database Technologies Architectures of Web and Oracle RDBMS Integration A Technical Overview of The Oracle-Web Integration using wowstub and PL/SQL Examples of a Web-based Search Interface for the Phone List Database Key points of Web Technology Characteristics Current main components: HTTP; HTML; CGI; Fillout Form Client-server communication model (Flat hierarchical UNIX) File system as the major file (data) management system main WWW components (before Java, VRML, LiveMedia etc.): HTTP: the transport protocol - performs the request and retrieve functions to display objects HTML: the makeup language - language used to author Web pages URL - Uniform Resource Locator - addressing system for Web documents CGI: an interface to extend HTTP and HTML- allows the Web server to execute a program to produce dynamic HTML documents Fill-Out Form: a set of HTML constructs to allow clients passing run-time input data to server through GUI interface Weaknesses in Web Technology Tedious to manage and update (HTML) files on the server side difficult to keep pages of data updated automatically --- modification usually done by a human using word processor or editing tools A plain UNIX file system as ÒdatabaseÓ only supports primitive functions such as open,read/write and close More powerful operations on files are required for an information system which are supposed to provide information instead of raw data. e.g. need: low-level data manipulation operators such as those provided in RDBMS: select, insert, update, commit, rollback,etc Meaningful information needs to be generated on-the-fly from raw data Key points in database technology - I Characteristics Relational tables in RDBMS as the major data model for tightly controlled information management, retrieval and processing Client-server as the major communication model Main components: search engine; SQL; procedure languages with embedded SQL; GUI application interfaces to DBMS; main RDBMS components a search engine: the database management system SQL: the query language embedded SQL interfaces: interfaces to extend the SQL non-procedure programming in high-level programming languages database tools: various (GUI) application interfaces to the DBMS client-server as the major communication model customized proprietary network protocols built on TCP/IP as the major transport protocol focus on file management and I/O processing for a powerful text-based searching and data manipulation system Key points in database technology - II Strengths Database server stores, manipulates and manages data in a powerful, high functionality information system Supports design of data system with complex relationships Supports complex, precise, customized access requests Easy to automate most data update processes Information retrieved is as current as the database Weaknesses Network access not universal or portable: employs proprietary network protocols & requires client license for each end user Plain-text -- no direct support for multimedia Isolated from links to information outside database system Nonstandard browsers (most are line-based) or proprietary forms The Synergy of Web Networking and Database Technologies - I Database techniques used in Web technology data storage and data caching index searching data processing Networking techniques used in database technology distributed database two-phase commit data replication client/server model Web-Database Synergy - II Web server integrated with database is enhanced with: Powerful backend text searching engine for complex queries Representation and organization of (often complex) logical relationships among many information entities Optimizable search performance in large information systems Real time creation of up-to-date HTML documents Database server linked to web server is enhanced with: Ability to share data globally for maximum information dissemination Ability to add data globally for remote collaborations Transparent Web browser interface for remote database server access Universal Web interface supports hypertext-added data retrieval Integrated multi-media information system By integrating a database server into Web technology, a web server can be enhanced with: A powerful backend text searching engine supporting complex queries an efficient, reliable and sophisticated data management system supporting representation and organization of logical relationships among information entities A dynamic data processing engine, provided with opportunity for optimized searching performance in large information systems A time-dimension added HTML programming engine which displays latest available data. By integrating Web into database technology, a database server can be enhanced with: A transparent networking interface for remote database server access A universal GUI-based search interface for hypertext-added data retrieval A truly globally-shared data system for maximum information dissemination Note Oracle used to hold Òmeta-dataÓ and text indices to our video on demand servers video stored in Òflat filesÓ An integrated multi-media information system Importance for Some Key Communities Education Supports school access to organized data in many disciplines Supports multi-school projects doing data collection and sharing Multimedia links provide organization of visual, audio material Research Provides universal access to major research databases Examples, protein & other biomolecular database (data + applications) Provides universal interfaces for data entry with data validation done by database procedures Can link research databases with online publications or abstracts Industry Can enhance marketing websites Improves support for distributed dealerships and/or customer helplines Provides framework for internal enterprise systems, groupware Networking and Client-Server Archtecture in WWW - I Basic features in HTTP protocol -- a client-server model: Single transaction per connection: Stateless nature of the connection: end-userÕs previous connection run-time input will not be seen by current connection client & server open the connection ---> client sends request messages to server ---> server locates requested information and sends response requested back to client ----> client & server close the connection (note each URL accessed represents a separate connection process) Networking and Client-Server Archtecture in WWW - II The Web is Stateless each GET or POST request from a Web browser to the Web server is independent of the other unlike client-server database connections, no concept of a session between a Web browser and Web server no persistence of data between successive GET or POST requests from the same Web browser Fill-out forms are used for the server to accept clients run-time input from a HTML page Two Different Strategies for WWW Client-Server Interactions - I Pre-edited HTML files (links) --- static and plain ASCII read-only files, identified by a file path; exist on disk; no need to process when used by the server (server performance is only determined by disk -> core memory I/O transfer) Two Different Strategies for WWW Client-Server Interactions - II CGI scripts --- programs (shell,perl,programming languages) to Generate data (usually in HTML format) to be returned to the server (which will then be transferred to the requesting client in HTTP protocol); the resultant data are dynamically generated on the fly; pipes are used to feed the output (via STDOUT) to server; server performance is only determined by CPU processing) CGI is Interface for running external programs on a Web server Web server is configured to realize that any request for a file in a specific directory or with a specific filename extension are to be executed instead of served Allows Web servers to create dynamic documents which only get created when they are requested by a browser Basic WWW DB Integration Model Web Server and DB client must be on the same machine DB client and DB server can be on different machine and a proprietary networking protocol (e.g., SQL*NET for Oracle7) is used Inter-process Comunication (IPC) is used for the Web server to communicate with DB client on the same machine Oracle-Web Integration Architecture The Oracle-Web Integration: A Technical Overview Integrates Oracle7 databases with the Web Manage Web information effectively Provides transaction logic and integrity to Web applications Uses PL/SQL stored procedures to build dynamic HTML documents on the fly Bring all the benefits of Oracle7 to the Web, including portability, scalability, manageability, and gateway technology Oracle Web Agent Facilitates the creation of dynamic HTML documents via PL/SQL Provides an easy way to publish existing Oracle data on the World Wide Web Web data can be stored in an Oracle DB which will make it much simpler to manage and manipulate Provides utilities which take care of the repetitive tasks associated with building a CGI application Oracle Web Agent Components An OCI (Oracle Call Interface) program called wowstub (or owa in Oracle WebServer) which serves as the actual CGI program Two PL/SQL packages (HTP and HTF) that contain utilities to automatically generate HTML tags (optional to Oracle WebServer) A PL/SQL package called owa which contains utilities used by the OCI program to set up the CGI environment variables, etc. A utility to administer the Web Agent via HTML forms Oracle Approach to Web Information - I Data and SQL Engine use tables, instead of static files, to store data build a schema on data tables to define data and the relationships of different information entities (the relational model) Web pages can be created, managed and delivered in more powerful way SQL operators for data and file processing: Select data from tables to provide accurate and up-to-date information, and data can be entered, updated through a web page to keep a consistent information base Oracle Approach to Web Information - II Procedural and programmable mechanism Use PL/SQL for data manipulation and processing. PL/SQL: OracleÕs procedural SQL application development language support stored procedures, database triggers, optimized concurrent data access, and executable programs as meta-data (i.e., programs are stored together with and attached to the data objects to be manipulated on) a programming language between C and SQL: loops, decisions, modules and function calls, subroutines, etc. database server is both a SQL engine and a computing engine programmable HTML pages - program dynamic html pages on the fly Why Use PL/SQL ? Performance: tends to execute faster than regular SQL code becuse it doesnÕt need to be parsed and interpreted before executing Portable across all Oracle platforms: resides completely inside the database thus is 100% portable to any platform that Oracle7 runs on Modularity: has procedure,package etc mechanisms for software development in modules Why not? Not portable to other databases! Oracle Web Agent: Dynamic Creation of HTML Web pages created on the fly to include data dynamically obtained from Oracle7 Oracle7 stored Web related information Web pages are therefore always up to date Anatomy of a Web Agent URL Typical URL - http://osprey7.npac.syr.edu:1997/cgi-bin/database/plsql/wow /wow_pkg.get_person_by_name http - specifies protocol to use osprey7.npac.syr.edu:1997 - specifies hostname cgi-bin/database/plsql/wow - specific the OCI program that needs to be executed wow_pkg - specifies the PL/SQL package that wow will invoke get_person_by_name - specifies the PL/SQL procedure that wow will invoke The WOW OCI program (wowstub) Extract the PL/SQL package and procedure name embedded in URL and log on to the DB Set up the parameters passed from the Web server so that they are accessible to the PL/SQL procedure Execute the PL/SQL procedure which extracts data from DB and formats it into an HTML document stored in a PL/SQL table Take the HTML document and write it to standard output WebServer DeveloperÕs Toolkit utility packages that simplify the generation of HTML documents from within PL/SQL (HTP and HTF packages) each HTML tag has a corresponding function and procedure the procedure generates a line of output in a PL/SQL table when the entire document is done, the OCI program (wowstub) takes the contents of the PL/SQL table and writes it to standard output Examples of the Use of HTP and HTF - I HyperText Procedures (HTP) A HTP procedure generates a line in an HTML document that contains the HTML tag that corresponds to its name For example, htp.url(Ôhttp://www.npac.syr.eduÕ, ÕNPAC Home PageÕ) actually prints the text: NPAC Home Page Examples of the Use of HTP and HTF - II HTF (HyperText Functions) is the corresponding package which contains PL/SQL functions for generating HTML tags i.e. it returns HTML string whereas HTP writes HTML Analogous to printf(HTP) versus sprintf(HTF) HTF functions are used only when the programmer needs to nest calls For example, htp.url(Ôhttp://www.npac.syr.eduÕ, htf.italic(ÕNPAC Home PageÕ)) prints out the text: NPAC Home Page Details about syntax and use of each procedure and function in HTP and HTF can be found from the handout An Web/Oracle Example - Query A PersonÕs Phone Number by Last or First Name From a Web Page A PL/SQL package called ÒexampleÓ defined by a Oracle user Ôcps616Õ with password ÔxyzÕ. One procedure Òget_phoneno_by_nameÓ is defined in the package. Assume a host machine runs a Web server (http://myhost) and a Oracle RDBMS server (ORACLE_SID = myoracle_server, ORACLE_HOME= /oracle/oracle_home) The user Ôcps616Õ has a CGI path as http://myhost/cgi-bin/cps616 in which a CGI script named ÔwowÕ is set up as on following foil: Web/Oracle Example Overall Set Up - CGI Script - II #!/bin/csh # Specify which Oracle server to use setenv ORACLE_HOME /oracle/oracle_home setenv ORACLE_SID osprey7 # specify Oracle user and password(xyz) setenv WOW_UID Ôcps616/xyzÕ # point to where the Web Agent executable is located ./wowstub $1 exit The PL/SQL Package For Phone Example - I CREATE PACKAGE EXAMPLE is procedure get_phoneno_by_name(name IN VARCHAR2); -- only one procedure END EXAMPLE; CREATE PACKAGE BODY EXAMPLE is CREATE PROCEDURE get_phoneno_by_name(name IN VARCHAR2) IS CURSOR person_cur(cname IN VARCHAR2) IS SELECT last_name,first_name,phone_no,phone_type from person_info_table,phone_list_table WHERE (person_info_table.person_id = phone_list_table.person_id) AND (last_name LIKE (Ô%Õ || LOWER(cname) || Ô%Õ ) OR first_name LIKE (Ô%Õ || LOWER(cname) || Ô%Õ)); lname person_info_table.last_name%TYPE; -- a variable to hold last name fname person_info_table.first_name%TYPE; -- a variable to hold first name phone phone_list_table.phone_no%TYPE; -- a variable to hold phone no. ptype phone_list_table.phone_type%TYPE; -- a variable to hold phone type The PL/SQL Package For Phone Example - II BEGIN htp.htitle(ÔQuery ResultsÕ); htp.ulistOpen; OPEN person_cur(name); -- open the cursor LOOP; -- Fetch each row matching the query into variables repeatedly FETCH person_cur INTO lname,fname,phone,ptype; EXIT WHEN person_cur%NOTFOUND; -- check end of result buffer /* print out the query result */ htp.p(htp.item||ÔThe Ô || ptype ||Õ phone no. of Ô || fname || Õ Ô || lname || Õ: Ô || phone); END LOOP; CLOSE person_cur; -- close the cursor after it is done htp.ulistClose; END get_person_by_name; END EXAMPLE; The Web Page - Search Interface - I A HTML form page to accept user search input and invoke the CGI script on the web server to access the database
A Web/Oracle Phonebook Example
Enter Last or First Name:
The Web Page - Search Interface - II Query results are displayed in another Web page as (if user typed in ÔgeoffreyÕ)
Query Results

Query Results

More Examples of PL/SQL Procedures for Web/Oracle Application Details of the phone-list example can be found from the handout or at http://osprey7.npac.syr.edu:1997/database Further application examples can be found at a searchable USENET newsgroups archive - http://asknpac.syr.edu a resource for chemists: http://asknpac.syr.edu/chem/ Collection of several other databases - http://kayak.npac.syr.edu:1963