Distributing Database Information on an Intra/Internet Prepared for the Co-operative Education Option in the ACST Program. Langara College by Kris Erickson September 12, 1997 TRIUMF Vancouver, B. C. Table of Contents I. SUMMARY...............................................................................................................................................3 II. INTRODUCTION...................................................................................................................................4 III. HISTORY AND BACKGROUND OF THE WEB AND HTTP.........................................................5 IV. DYNAMIC WEB PAGES ......................................................................................................................7 1. Figure 1.0.............................................................................................................................................8 V. SERVER SIDE PROCESSING...............................................................................................................9 A. IN BRIEF SERVER SIDES' ADVANTAGES ARE: ..........................................................................................10 B. SERVER SIDES' DISADVANTAGES: ..........................................................................................................10 VI. CLIENT SIDE PROCESSING ............................................................................................................11 A. IN BRIEF JAVA'S ADVANTAGES ARE:.......................................................................................................12 B. JAVA'S DISADVANTAGES ARE: ................................................................................................................12 VII. CONCLUSIONS.................................................................................................................................13 1. figure 1.1 ...........................................................................................................................................15 2. figure 1.2 ...........................................................................................................................................16 3. figure 1.3 ...........................................................................................................................................17 4. figure 1.4 ...........................................................................................................................................17 VIII. APPENDEX A -GLOSSARY...........................................................................................................17 I. Summary Database applications are used throughout TRIUMF for a variety of reasons and purposes. They are used in the software development process, by the hardware personal, by technical personal, and even for reasons as diverse as creating the minutes for weekly meetings. These applications have migrated from text based applications (with ORACLE forms 3) to X-windows based applications (with ORACLE forms 4). They currently all run under VMS, the primary operating system of the controls software/hardware division. For reasons explained in the paper, a replacement for the current X-windows based Oracle forms database applications was required. It was decided that the best approach would be though TCP/IP connections via TRIUMF's own Internet, and from outside TRIUMF via the Internet. Once the decision to move to an Intra/Internet method of delivery was decided upon, a method of delivering this data had to be determined. This origins of this paper was a report on the different potential methods of transmitting data from an ORACLE 7.1 database over an Intra/Internet. Two basic methods where examined, JAVA based applications and CGI (ISAPI, NSAPI) based applications. To understand the key differences between the two methods requires an understanding of not only JAVA and CGI, but also the HTTP transfer protocol and how the World Wide Web operates. Early on in the process it was decided that the VMS platform simply didn't have the software available to develop complex Web server applications, so it was decided that the Web Server would run on a dedicated Windows NT Server connected to the database via SQL*Net. Several options for delivering the data where examined, two where demo'd and eventually the adopted method turned out to be based on CGI (ISAPI, actually) supported through Borland's Intrabuilder. While the system is still in its infancy, testing of initial applications that have been developed has been quite successful and development much easier than the time consuming process of writing complex Perl, TCL or even Java programs. II. Introduction TRIUMF (TRi University Meson Facility) is a physics research center based at the University of British Columbia. It specializes in particle research through the use of a cyclotron, but is also a site for medical research and treatment and many other forms of scientific research. The experiments and the cyclotron produce massive amounts of information, and the components of the cyclotron, beamlines and associated equipment (over 25,000 objects) must be carefully cataloged and information on them must be available to operators, technicians, and many other people on site. For this purpose several database applications have been developed over the years. Initially they were with simple textfile based applications, that eventually became too complex to manage in FORTRAN (the language most of the controls systems at TRIUMF were written in1). These applications where migrated to a text based database interface (Oracle Forms 3.0 which ran with Oracle 6 as the database engine). Eventually these forms where converted to a GUI interface which ran as an X-Windows /application (Oracle Forms 4.5 with Oracle 7 as the database engine). While this allowed anyone on site with an X-Windows terminal to access the forms it presented several problems. First, although Oracle supports VMS as both a database engine platform and a development platform, it is apparent that they spent little time debugging the development platform (developer/designer 2000) as both the designer and the applications themselves crash frequently. Secondly, the forms applications where extremely slow to run (some times taking as long as 10 seconds to refresh) and the development tools where intolerably slow2. Thirdly, although the forms would run over a TCP/IP direct dial connection to TRIUMF (apparently this was mostly unusable due to five to ten minute screen 1 Although there is a gradual migration towards C, almost all of the legacy code at TRIUMF is in FORTRAN. (An improvement from five years ago when much of the code was still in PDP-11 assembler). 2 After tracing a widget a programmer might wait for up to four seconds for the rubber bands to appear, and another five to ten seconds for the widget itself to appear. This slowness was probably due to the fact that Oracles port of Developer 2000 was a direct dump of the Unix port with little tweaking. One cannot really blame Oracle for this too much, since they probably have few users purchasing Developer 2000 under VMS. updates3, however) the number of direct-dial connections to TRIUMF is very limited and the preferred method of connection would be through the Internet. Fourthly, a lot of on-site users didn't have access to an X-Windows terminal but a PC running some form of Windows4 or a Macintosh. When it became apparent that the World Wide Web had reached a stage where it was capable of delivering dynamic Web pages containing database information (as it has been able to do for a few years now), it was decided that this would be the delivery vehicle of choice for database applications at TRIUMF. It was readily obvious that the VMS platform lagged behind UNIX and Windows NT as a Web Server platform (Netscape offered no servers on the VMS platform, and even Netscape Navigator was ported by Digital, not Netscape and was about a year behind Unix/Windows versions.) Windows NT quickly became the cost effective choice for the Web Server, as there was already a PC on site that could be converted into a dedicated Web Server. Now the choice of what distribution method was going to be used had to be determined. III. History and background of the Web and HTTP The advent of the World Wide Web in the early nineties caused an explosion of information to be published on the 'Web.' However, until recently the WWW provided only the ability to deliver 'static' text to web browsers. While the ability to quickly distribute linked text and images proved useful for the passing of information, it was extremely limited in the applications it could perform. However, with the advent of CGI5 (Common Gateway Interface), Java, 3 Once again there is a suspicion that this was due to Oracle's untweaked X-Windows code, as standard X-Windows applications have no problems with a dial-in connection. 4 Actually there are X-Windows emulators for Windows 95, but they are difficult to configure and require a knowledge of Unix/VMS to get them up and running properly. 5 In this report I am a little fast and loose with the term CGI. CGI is, in fact, a standard that is used mostly on Unix systems. WinCGI, NSAPI, and ISAPI are the equivalents in the NT world. NSAPI(Netscape Application Programming Interface) being created by Netscape and ISAPI(Internet Server Application Programming Interface) by Microsoft. They both are programs (well API's) that Netscape and Microsoft have used to speed up CGI like program access. They both have less overhead (they load in the server's process space and do not spawn additional processes), however ISAPI is only available on NT (where it's a .dll) whereas NSAPI runs on NT Javascript and other technologies the ability to create dynamic web pages have lead to the development of 'useful' web pages that could do more than deliver 'static' information. To understand how these feats are accomplished, an basic understanding of how the Internet works is required. A Brief Introduction to the Workings of the World Wide Web The Internet is a collection of protocols for the transfer of information. There are several protocols which are used frequently today (HTTP, FTP, NNTP, SMTP, POP-3, etc.) and many other protocols which have fallen out of favor. The most commonly used protocol is HTTP (Hyper-Text Transfer Protocol) which is the back bone of the Word Wide Web. HTTP is a stateless protocol using port 80 on a server for the transfer of textual information which transmits MIME (Multipurpose Internet Mail Extensions) encoded documents. MIME is the way that all multimedia (meaning text, images, sound, video, etc.) is encoded for many of the protocols6. Since HTTP is a text protocol, it would not be possible to send non-textual information without encoding it into the 7-bit structure that HTTP expects7, MIME8 is the way that this encoding is done. As stated earlier, HTTP is a stateless protocol (unlike a protocol like FTP which keep track of who is connected to it from packet to packet). By stateless it is meant that the protocol itself has no recollection from request to request (this does not mean that the server cannot keep track of requests via CGI or some other means9, it just means that the Protocol does not keep track of requests). Each time you click on a hyper-text link on a HTML document over the Internet the server sees that as a separate request for information, blissfully unaware and Unix(it's a .dll on NT a shared object on a Unix system). So if I talk about CGI programs, I meant an application (API) that allows the interface between between the HTTP server and a program which runs on the server that can access variables passed by the HTTP server program. 6 OK, it's mostly for the mail (POP-3, SMTP) and the world wide web (HTTP). 7 7-bits rather than 8 is due to historical reasons. 8 Basically MIME sends a header containing the MIME-version, Content-Type, and the Content-Transfer-Encoding (usually the radix 64 encoding protocol). A more in depth discussion of MIME would be interesting and useful however space does not permit. It is defined in RFC(Request for Comments) 1563 which can be found at: http://sunsite.cnlab-switch.ch/ftp/doc/standard/rfc/15xx/1563 9 However, this definitely one of the more difficult acts that would have to be performed in a CGI program that was trying to keep track of users accessing the database. whether it has sent information to that same user before or not. The main advantage of this statelessness is that the server can quickly handle thousands of requests for information without tying up its port10 (unlike FTP transfers, where the number of Ports can quickly become used up). The HTTP protocol can perform several actions depending on the Method it uses. The method is sent as a header at the beginning of an HTTP request. The methods which are most commonly used are GET (the most common), HEAD, POST, and TEXTSEARCH. GET, obviously enough, is a request to get information from the server. HEAD grabs the information between and in an HTML document (used by Web Robots to determine a sites content, and browsers to determine if new information has been posted on a site). POST allows the user to send information to the WEB site (for use by CGI scripts, this functionality is supported to a degree also by the GET method), and TEXTSEARCH allows for the searching of a site. IV. Dynamic Web Pages If the client wishes to view static information HTML is a powerful way of delivering documents (the World Wide Web was invented by Physicists at CERN who wanted to rapidly distribute papers...), however, it becomes much more difficult to present Web pages that change due to the requests of a user. These pages are usually referred to as dynamic web pages, whereas web pages that do not change except when the HTML itself is edited are called static web pages. Approaches to Dynamic web pages can be broken into two major categories: server-side and client-side. Dynamic web-pages that use a serversiid approach require that programs be run on the server-side of the HTTP transfer to dynamically (on the fly, as it were) generate the web page and then send it to the HTTP client. They receive their input from the client in the means 10 Although most Web servers will allow any port to be used as the HTTP port, a browser has to be specially directed to that port by indicating the port number at the end of the HTTP request. of arguments (parameters) that is passed by the browser by means of the HTML elements (which are part of a form), thus a forms capable browser is required to interact with these web pages. It is the CGI (or ISAPI, NSAPI) that takes those arguments from web server and passes them on to the program that handles the request and returns an HTML response to the Web Server which in term passes the HTML on to the browser. figure 1.0 The process can be seen in the diagram in Figure 1.0. In step 1 the browser sends a HTTP request to the HTTP Server. Step 2 the HTTP Server sets some environment variables, and passes along arguments (via the CGI, ISAPI, NSAPI API) so that CGI program can function. Step 3 the CGI program returns a mimeencooded1 HTML document to the server which passes the document along to the Client browser. Client-side dynamic web pages are generated using Java, Javascript, browser Plug-in's, and an ever changing array of distributed object techniques (ActiveX, DCOM, DSOM, CORBA, etc.). Where server side dynamic web pages are still limited in that they can only display what HTML can produce, Client Side Not something that user probably wants to have to remember. Which is why port 80 is the defacto standard for the HTTP port. 11 Since the output is text no encoding is necessary, so all the program has to do is insert the MIME header. web pages (Java in particular) are have no such limitations. They can usually only receive data from the host site, but the way in which that data is transferred is up to the programmer (i.e. it is not limited to the four or five input methods that HTML forms are limited to). The way Java and other plug-in's work is much more complicated, and less relevant than the way Server Side Processing works. It is just important to note that Java, for example, can request information be sent from the server in a constant stream and does not require HTTP requests get information from the server (a Java program can use any of the Internet service protocols it wants, as long as it doesn't interfere with the security arrangements of the Virtual Machine it is running on12). V. Server Side Processing There are advantages and disadvantages to both these systems of distributing information, and the use of either one does not preclude use of the other. Since server-side applications have been around longer, it is perhaps best to begin our examination with them. Server-side processing was originally limited to CGI, but that field has expanded a little more now. CGI is not a language in itself, but is a way of interfacing programs with an IS13 (information server). Basically what happens is the output of a program (which can be written in any number of languages) is piped to the IS (Information Server) which delivers the output as an HTTP packet(s) to the requesting client. The program can be written in practically any language, but tends to be written in one of the modern interpretive languages like Perl, tcl, or any number of UNIX scripting languages (Bourne, C-shell) because of the text processing capabilities of those languages. While the output is simply piped to the IS, it must contain the standard MIME formatting that the browser will recognize (i.e. it must have header specifying content-type, and it should be formatted in HTML). There are many advantages 12 Java security is huge topic, and not an area that this paper has room to delve into. Suffice to say, that a Java applet is usually limited to communicating with the Server that spawned it. 13 The IS is the same as HTTP server, or Web Server. The terms are generally interchangeable. to server-side processing, and the modern paradigm of shifting towards a thin client, process laden server (which appears to be the exact opposite of the other paradigm shift towards Java) supports the server side model. A. In brief Server Sides' advantages are: 1) Almost all modern browsers support forms (Netscape from 1.0 has supported them) thus guaranteeing universal access to the data. Since the type of browser you are using is readily available to the Web Server you can adjust the "level" of HTML for that browser. Also HTML is designed so that browsers that do not understand certain tag's just ignore them, so the worst case scenario is that the user ends up with an 'unattractive' screen (or at least not what the web designer intended) display. 2) Most people who are familiar with computers, have become familiar with Web Browsers. This means that interface of the form will be something that the user will be familiar with. 3) No processing is done by the client, all it has to be able to do is run the browser. This allows the web pages to be seen on low end computers (or in networking jargon, very thin clients). 4) The CGI or ISAPI programs can have as little or much access to the resources of the server that the Network administrator allows. This allows for a connection to a database, processing of the database information, and then the passing of that information to the Web client. There is also the ability to access disk files, printers, etc. 5) There are a lot of high end, powerful, robust easy to use, tools to create server side web sites (for example, Intrabuilder). 6) The server is aware if anything goes wrong with the execution of the script. B. Server Sides' Disadvantages: 1. Output Limited by HTML, controls limited to the standard forms controls (see above). 2. Scripts programmed in the standard CGI languages (Perl, tcl) are interpreted and thus are slow and require the loading of a Perl/tcl interpreter for script that is running (not true of programs that use ISAPI or NSAPI). 3. May be slower due to the fact that the entire screen has to be updated with each delivery of new data. VI. Client Side Processing Client side processing is dominated by Java. Java is an object oriented programming language designed to be platform independent. It achieves this platform independence by being a semi-interpreted language. Instead of compiling into object (machine) code, when a Java program is compiled it is compiled into what is called byte-code. Byte-code is a set of instructions that can be followed by the Java Virtual Machine. It is similar to an assembly language in that is very close to machine language, however it is not an native machine language. While the program is running it is constantly being interpreted. It is in this byte code for two reasons, first so that it can be run on any computer that has a Java Virtual Machine written for it, and second so that it can be sure that no dangerous (insecure) actions are being performed by the program that might damage the host computer or its data. The problem with this interpretive process is that Java usually used in a GUI environment (via the AWT, Abstract Widget Toolkit) and a graphical environment usually requires a lot of computationally intensive actions. Thus Java frequently seems to be very slow -however with the advent of JIT (just in time) compilers Java's speed has improved considerably. The other problem with Java is the problem of standards. Although SUN created and controls all lot of the aspects of Java, different browser makers create different versions of the Java Virtual Machine in their browsers. Microsoft, for example, has it's own proprietary version of Java (which will run standard Java, but also has a set of extensions that are not included in standard Java). Netscape's latest browser still hasn't implemented the major changes to Java that were introduced with version 1.1 (Java is now at version 1.13), which include a completely new event model and the JavaBeans specification. A. In brief Java's advantages are: 1. A lot of control over the look and feel of what is going on. Java is not limited to HTML (but is limited to the AWT). Is not stuck with standard HTML FORM controls for input. It can do lots of things that just can't be done with HTML. 2. Program runs separately from any HTTP activity (except requests of information from the server). Therefor multiple pages can be displayed (if the applet has all the information it needs) without going back to the server for more information. 3. Lot's of Java tools are pouring onto the market, making 'codeless' programming almost a reality. Java's evolution is occurring very quickly and the language is blossoming into a robust language which has fully embraced the modern programming paradigms of OOP and RAD (with the addition of Javabeans). 4. All processing is done by the client so the server can be less powerful. B. Java's disadvantages are: 1. Although it is supposedly the "write once, run everywhere" language, recent radical changes (new Event module, Javabeans, Microsoft extensions to Java) bring that into question. Also different platforms are supporting different levels of Java. Virtual machines that are 1.0 compliant will not run 1.1 code (although 1.1 VM's will run 1.0 code). Currently Netscape supports Java 1.0 and some aspects of Java 1.1 with Netscape Navigator 4.0 (available in full release on Windows 95, preview 6 on Unix, VMS is currently Version 3.0 beta 7 -which has very limited and buggy Java support). Netscape claims full Java 1.1 will be available some time in the fall. Microsoft Internet Explorer 4.0 supposedly supports Java 1.1, however at the current moment IE 4.0 is in Preview 2, and Microsoft does not recommend installing it on any machines other than spare ones as it can crash the operating system requiring a reinsstal of Windows95 or NT14. Sun's Hot Java (available on Windows95 and NT and SunOS Unix machines) is 1.12 compliant since it is written in Java and should run in any version of Java. 2. If an applet crashes the server will not know about it. 3. It can be very slow at times (depending on which version of Java you use) Sometimes it can take a full minute to get an application running after it has been downloaded. Long download times for applets over the Internet (not a problem on Intranets). 4. Potential security risks. VII. Conclusions After discussing the options with the stakeholders, and those who would be in charge of implementing the new database applications it was decided that two options would be tested: Oracle Web Forms and Borland's Intrabuilder. These two options where operated in the two different realms, Oracle Web Forms was a Java application (client-side application) and Borland's Intrabuilder uses a CGI style gateway to generate HTML forms from database information on the fly. The first product demo'd was the Oracle Web Forms. The product took existing Forms 4.5 applications and re-compiled them into Java bytecode which could transferred over an Internet. This was an appealing solution since it would require no re-writing of the existing applications. However once exploration of the application began, several problems immediately presented themselves. First, there was no browser which currently supported the Java 1.12 14 I speak from experience here. Had to do a complete re-install of Windows NT. Although of heard of worse horror stories where the whole hard drive was lost. implementation (which was required for the Java Beans, and the security model that Web Forms used.) Second, although the security system that Oracle had decided upon using was going to be supported by Sun and Javasoft it was not going to be supported in the Java virtual machines produced by either Netscape or Internet Explorer (there goes the universal browser option). So the only way that the forms could currently run (and it was looking like the only way they would ever be able to run) was using the HotJava browser from Sun. And unfortunately VMS was not one of the platforms where there was any stated intention of porting JDK (the Java Development Kit) or HotJava. Thirdly, even over the internal TRIUMF network (which is capable of delivering 500 kps), it took between ten and fifteen minutes for the applet to appear after being requested. Fourthly, the Web Forms application had a price tag of $35,000 US. With this in mind the approach decided upon was Borland's Intrabuilder. Intrabuilder is a Borland product which is acts as CGI (or ISAPI, or NSAPI) between the database and the browser client. When the user connects to a Intrabuilder form they are given a distinct session number (which Intrabuilder hides in the HTML form as a form element, for example ). With this unique session number Intrabuilder gets around the stateless nature of the HTTP protocol by keeping track of the user, and their actions. This is done on the Web Server machine by what are called Intrabuilder Agents. These agents are sort of a "super CGI" program which monitors and looks after the forms. They communicate with both the Web Server and the database. The following diagram might better explain the three tiered client-server process. figure 1.1 First, the browser sends a HTTP request to the Web Server. The Web Server (in our case, the Microsoft Internet Information Server), then decodes the request, and passes it on to the ISAPI program15. The ISAPI program sets up some environment variables, and passes the those variables as well as any information contained in the HTML form to the Intrabuilder Agents (or Intrabuilder Server). Secondly, the Intrabuilder Agents look at the request and determine what data will be required from the database. It then sends out a request to the database (which in our case is Oracle 7.1 VMS) for the information that it needs (which it stores in an internal cursor on the NT Web Server, so that the information will be cached and readily accessible). Thirdly the database engine decodes the SQL and returns the formation in TNS format (Oracle's own proprietary protocol system based on TCP/IP) to the Web Server. Fourthly the Web Server then generates the HTML required to deliver the "form" on the fly, using the information that it has received from the database and a template for the form that is stored on the NT server in a .jfm file (standing for Java Form). The JFM file simply describes the form that is to be created in Javascript, the language on Intrabuilder. That HTML code is sent back to the browser, which in turns displays the information. 15 Program isn't really the right word, but I find it useful as an analogy. The Intrabuilder program is actually a two part program, one the Intrabuilder servers (described above) and two the Intrabuilder Designer. The designer itself is a two-way RAD tool (see figure below). figure 1.2 It allows for the visual creation of forms in a Delphi like environment (if you are unfamiliar with Delphi, think Visual Basic), but the forms themselves are stored in a Javascript format, thus allowing them to be edited with a simple text editor. An example of a very simple form follows below: class testForm extends Form { with (this) { height = 20; left = 21; top = 0; width = 60; title = ""; } with (this.html1 = new HTML(this)){ height = 1; left = 24; top = 2; width = 10; color = "black"; text = "Hello World"; } } figure 1.3 Although this form does nothing (it actually create the follow HTML...)

Hello World

figure 1.4 But you can begin to see how the Javascript works. It creates a new form called testForm which is inherited from a base object form. It sets up several properties in that form. It then creates an HTML object called html1 (variables don't have to be previously declared in Javascript, otherwise a var html1 would be required before it's instantiation) and then sets up various properties of html1. This isn't intended on being a primer on Intrabuilder, but to point out that it can be a very powerful tool. Easy to use for beginners (the RAD part), yet it can create some very powerful Database applications. It has been found to be the perfect product for the TRIUMF environment as it's design process and the Intrabuilder servers are on the cutting edge of technology, while the output it delivers is easily handled by even the VMS version of Netscape. VIII. Appendix A -Glossary ActiveX -Microsoft's repackaging of OLE into a Internet Object CGI -Common Gateway Interface. Allows programs to interact with the HTTP server. CORBA -Common Object Request Broker Architecture. A specification which provides the standard interface definition between OMG-compliant objects. DCOM -Distributed Component Object Model. A specification which provides the standard interface definition between objects. Delphi -Borland's RAD object Pascal IDE/compiler. FTP -File Transfer Protocol. A protocol used to transfer files over the Internet. HTML -Hyper Text Markup Language. The most commonly used document language, used by various browsers to describe documents on the World Wide Web. HTTP -Hyper Text Transfer Protocol. A protocol used by the World Wide Web to transfer HTML IS -Internet or Information Server. HTTP Server, Web Server. Used to store and send documents on the world wide web. ISAPI -Internet Server Application Programming Interface. Created by Microsoft. Another form of CGI. JAVA -Just Another Vague Acronym. Programming language. JavaBeans -The Java component model. Allows reusable components (widgets) in Java AWT programs. Javascript -Netscape's interpreted Java-like scripting language. JDK -Java Development Kit. Comes with an applet viewer and the Java compiler. MIME -Multipurpose Internet Mail Extensions. Used to send multimedia documents over the Internet. NNTP -Network News Transfer Protocol. Used for the distribution, inquiry, retrieval and posting of Usenet news articles NSAPI -Netscape Application Programming Interface. Created by Netscape. Another form of CGI. POP-3 -Version 3 of the Post Office Protocol. Used to receive Internet mail. RAD -Rapid Application Development. Term for a software product designed to give faster development and better results and to take maximum advantage of recent advances in development software. SMTP -Simple Mail Transfer Protocol. Used to send mail. It is a server to server protocol, so other protocols are used to access the messages. two-way RAD -RAD tool which allows editing of a text file which directly relates to the display of a form. VMS -DEC's proprietary operating system originally produced for its VAX minicomputer. VMS runs not only on Vax's but on Alpha processors as well. X-windows -A specification for device-independent windowing operations on bitmap terminals. X uses a client-server protocol, the X protocol. The server is the computer or X terminal with the screen, keyboard, mouse and server program and the clients are application programs. Clients may run on the same computer as the server or on a different computer, communicating over Ethernet via TCP/IP protocols.