LETTER
Information
June 23, 1995
CONTRACT AND GRANT OFFICERS
Subject: Linking Documents and Databases on the Worldwide Web
As promised at the last Contract and Grant Conference (6/20/95), here is further information on the OP RAO home page and how we link it to RAO databases.
HTTPd (hypertext transfer protocol daemon) package, for creating a Web server under Windows. Available from http://hoohoo.ncsa.uiuc.edufmdex.html.
Netscape, Mosaic, or other Web browser. The latest version of Netscape is available from http://download1.netscape.com/.
HTML (hypertext markup language) editor. Word for Windows has an HTML add-in, called Internet Assistant, that is available free via the Microsoft FTP site (ftp://gowinnt.microsoft.com/); look in /deskapps/word/winword-public/ia/wordia.exe and don't forget to change to binary before downloading.
HTML reference. These are available free on-line (for example, at http://nicodemus.mc.duke.edu/Documentation/html2.0.html) or in book form at just about any bookstore.
Microsoft Visual Basic for Windows.
Microsoft Access or other SQL (structured query language) database software for Windows.
FTP (file transfer protocol) software (ours came with our e-mail software, Eudora).
A dedicated PC used as the local Web server.
The official Office of the President home page is located on a UNIX machine on the fourth floor of the Kaiser building in Oakland. OP RAO is located on the 12th floor, but we have been allocated a subdirectory on it called /raohome and can move our files back and forth easily using FTP. We can also create sub-subdirectories as needed. The only document in the /raohome subdirectory is our home page, which looks like this:
<HTML> <HEAD> <TITLE>Research Administration Office</TITLE> </HEAD> <BODY> <P> <A NAME="content"></A> <IMG SRC = "1ogo.gif"> <HR> <P> <A HREF = "cgmanual/mantoc.html">Contract and Grant Manual </A> <P> <A HREF = "cgmemos/memotoc.html">Contract and Grant Memos (Active Only) </A> <P> <A HREF = "far/fartoc.html">FAR and FAR Supplement Clauses </A> <P> <A HREF = "gopher://x.nsf.gov:70/11/NSF/grantcond">FDP (and NSF) Terms and Conditions </A> <P> <A HREF = "gopher://gopher.nihgov:70/11/gopherlib/data/phsgps">PHS Grants Policy Statement</A> <P> <A HREF = "circulars/circtoc.html" >OMB Circulars </A> <P> <A HREF = "waivers/waivers.html" >Indirect Cost Waivers [password required!] </A> <P> <A HREF = "idc/idctoc.html" >Indirect Cost Issues </A> <P> <A HREF = "http://www.ucop.edu" >Office of the President Home Page </A> <P> <HR> <A HREF = "mailto:william.sellers@ucop.edu">wiiliam.sellers@ucop.edu </A> <P> </BODY> </HTML>
This is an extremely simple document that does not need much explanation, assuming you are familiar with HTML. The only tricky part (at least for me) was that linking to sub-subdirectories did not work if the sub-subdirectory name were preceded with a slash. Thus, for example, a document called waivers.html in /raohome/waivers is referred to as "waivers/waivers.html" and NOT (as might be expected by one familiar with DOS) as "/waivers/waivers.html".
The OP RAO waiver, FAR clause, and C&G Memo databases are kept on a LAN server here on the 12th floor. This LAN server appears to our machines as just another physical drive, p: and we have direct access to the databases, for either look-ups or maintenance work, through Microsoft Access which is loaded on all our machines. The LAN server, however, is not connected to the Web, and so up until recently there has been no way to provide real-time access to our databases to outside users. HTTPd solves this problem.
The HTTPd package contains a set of files that enables a Windows PC directly connected to the Internet to become a Web server. Basically it's just a Windows program that runs in the background. Very minimal tinkering is needed to get it up and running. We installed HTTPd on a "surplus" PC that is logged onto the Internet AND is also connected to our LAN server. This machine is the hardware link between the outside world and our own internal network. The machine has the address seagull.ucop.edu, and when it becomes a Web server it thus has the URL (uniform resource 1ocator) of: http://seagull.ucop.edu.
Now we have three machines connected together:
1. The UCOP UNIX machine at http://www.ucop.edu, containing the RAO home page;
2. The RAO Web server at http://seagull.ucop.edu, acting as the link; and
3. The RAO LAN server at p:, containing the RAO databases.
In addition to the hardware link, we need a software link. This is provided by a Visual Basic program that makes use of what is called the Common Gateway Interface (CGI) that comes with HTTPd. The HTTPd package contains some nice examples of how to process requests, and we merely adapted one of those examples. The key part of the Visual Basic code for, say, providing access to the RAO FAR clause database is as follows:
1 Sub CGI_Main ()
2 Dim sel As String
3 Dim buf As String
4 Dim db As database, ds As dynaset, sqlq As String
5
6 sel = LCase$(Mid$(CGI_LogicalPath, 2)) ' Skip leading "/"
7 Select Case sel
8 '
9 ' If no selector, return the usage document
10 '
11 Case ""
12 Send ("Location: http://www.ucop.edu/raohome/far/far.html")
13 Send ("")
14 Exit Sub ' Finished: BACK TO CGI DRIVER/MAIN!!!
15 '
16 ' "Form" means show the form stuff decoded
17 '
18 Case "form"
19 StartDocument (sel)
20 If CGI_NumFormTuples > 0 Then
21 Send ("<UL>")
22 Set db = OpenDatabase("p:\bfnapps\far\prod\far.mdb", False, True, "")
23 'open non-exclusive, read-only
24
25 'form a select statement on the basis that
26 'cgi_formtuples(0) is the firstHow (starts with, contains, is exactly)
27 'cgi_formtuples(1) is the match-title (for entering title)
28 'cgi_formtuples(2) is the boolean
29 'cgi_formtuples(3) is the secondHow (starts with, contains, is exactly)
30 'cgi_formtuples(4) is the match-clause (for entering clause number)
31
32 'form sql statement, beginning with selection of fields
33 sqlq = "SELECT far.clause, far.alternate, far.date, far.title, far.prescribed, far.reference,
34 far.flowdown, far.op_check, far.comment, far.cl_or_prov, far.fac from far where ucase(far.title)"
35 'select for match-title if user entered a value
36 If Len(Trim(cgi_formtuples(1).value)) > 0 Then
37
38 Select Case cgi_formtuples(0).value
39
40 Case Is = "starts with"
41 sqlq = sqlq & " like ('" & UCase(cgi_formtuples(1).value) & "*')"
42
43 Case Is = "contains"
44 sqlq = sqlq & " like ('*" & UCase(cgi_formtuples(1).value) & "*')"
45
46 Case Is = "is exactly"
47 sqlq = sqlq & " = ('" & UCase(cgi_formtuples(1).value) & "')"
48
49 End Select
50
51 Else
52 'just select all titles
53 sqlq = sqlq & " like ('*')"
54 End If
55
56 'take account of boolean
57 If cgi_formtuples(2).value = "and" Then
58 sqlq = sqlq & " and ucase(far.clause)"
59 Else
60 sqlq = sqlq & " or ucase(far.clause)"
61 End If
62
63 'select for clause number if user entered one
64 If Len(Trim(cgi_formtuples(4).value)) > 0 Then
65 Select Case cgi_formtuples(3).value
66
67 Case Is = "starts with"
68 sqlq = sqlq & " like ('" & UCase(cgi_formtuples(4).value) & "*')"
69
70 Case Is = "contains"
71 sqlq = sqlq & " like ('*" & UCase(cgi_formtuples(4).value) & "*')"
72
73 Case Is = "is exactly"
74 sqlq = sqlq & " = ('" & UCase(cgi_formtuples(4).value) & "')"
75
76 End Select
77
78 Else
79 'just select all clause numbers
80 sqlq = sqlq & " like ('*')"
81 End If
82
83 'specify order
84 sqlq = sqlq & " order by far.clause asc"
85
86 Set ds = db.CreateDynaset(sqlq)
87
88 'ds(0) is first field (clause)
89 'ds(1) is second field (alternate)
90 'ds(2) is third field (date)
91 'ds(3) is fourth field (title)
92 'ds(4) is fifth field (prescribed)
93 'ds(5) is sixth field (reference)
94 'ds(6) is seventh field (flowdown)
95 'ds(7) is eighth field (op_check)
96 'ds(8) is ninth field (comment)
97 'ds(9) is tenth field (cl_or_prov)
98 'ds(10) is eleventh field (fac)
99
100 If ds.RecordCount = 0 Then
101 Send ("[Sorry, no records found.]<P>")
102 Else
103 For i = 1 To ds.RecordCount
104 Send ("Clause No.: " & ds(0) & " Alternate: " & ds(1) & "<P>")
105 Send ("<I>" & ds(3) & "</I> [" & ds(2) & "]<P>")
106 Send ("Prescribed at: " & ds(4) & "<P>")
107 Send ("Reference: " & ds(5) & "<P>")
108 Send ("Flowdown? " & ds(6) & "<P>")
109 Send ("OP Check: " & ds(7) & "<P>")
110 Send ("Comment: " & ds(8) & "<P>")
111 Send ("Clause (C) or Provision (P): " & ds(9) & "<P>")
112 Send ("FAC: " & ds(10) & "<P>")
113 send ("<HR>")
114 ds.MoveNext
115 Next i
116 End If
117 ds.Close
118 db.Close
119 Send ("</UL>")
120 Else
121 Send ("(none)")
122 End If
123
124 End Select
125
126 '
127 ' Return to complete HTTP.
128 '
129 Send ("<HR>")
130 Send ("</BODY></HTML>")
131
132 '****** RETURN, DON'T STOP! ******
133
134 End Sub
We'll examine this code in a minute. Right now, be aware that this program is compiled into an .exe file that resides on the machine that is also running HTTPd. Its function is to process requests that come to it from the UCOP UNIX machine, get the information needed from its "p:" drive (i.e. the databases on the RAO LAN), and return the data in HTML format to the UNIX machine.
The user requests come from an HTML document that uses form tags. An example is the page that is used to enter a query for the FAR clause database:
<HTML> <TITLE>FAR Clause Locator</TITLE> <H1>Search for FAR Clause(s)</H1> <P><A HREF = "../rao-home.html">Return to Home Page</A></P> <HR> <FORM METHOD="POST" ACTION="http://seagull.ucop.edu/cgi-win/far.exe/Form"> <P>Search for title that <SELECT NAME--"firstHow"> <OPTION SELECTED>starts with <OPTION>contains <OPTION>is exactly </SELECT> the following: <INPUT TYPE="text" NAME="match-title"> [default is 'ANY']</P> <SELECT NAME="boolean"> <OPTION SELECTED>and <OPTION>or </SELECT> <P>the clause number <SELECT NAME="secondHow"> <OPTION SELECTED>starts with <OPTION>contains <OPTION>is exactly </SELECT> the following: <INPUT TYPE="text" NAME="match-clause"> [default is 'ANY']</P> <P><INPUT TYPE="submit" VALUE=" Submit"></P> </FORM> <HR> <P>This is an experimental query page. If you have suggestions for improvements, please send a message to <A HREF="mailto:william.sellers@ucop.edu">Bill Se!lers</A> at william.sellers@ucop.edu.</P> </HTML>
This page is linked, via an intermediate document, to the RAO home page. It is located on the UCOP UNIX machine in the /raohome/far sub-subdirectory. You might want to look at this page with your Web browser to compare it with the source HTML document above. When the user presses the "Submit" button on the page, six variable are passed from the UNIX machine on the 4th floor to our Web server on the 12th floor. This is because of the line
<FORM METHOD="POST" ACTION="http://seagull.ucop.edu/cgi-win/far.exe/Form">
which tells the browser where to send requests.
The first variable is found within the first set of SELECT tags, and is named "firstHow". The variable has one of three values: "starts with", "contains", and "is exactly". The second variable is generated by the INPUT tag that is named "match-title." The third variable is found within the second set of SELECT tags, and is named "boolean" It has one of two values: "and" or "or". The fourth variable is found within the third set of SELECT tags, and is named "secondHow", with values of "starts with", "contains", or "is exactly". The fifth variable is generated by the INPUT tag that is named "match-clause". And the sixth variable is generated by the INPUT tag that is has the value "Submit".
When these variables (which, incidentally are passed as an environment string to the receiving machine) reach the RAO Web server, they are interpreted by the Visual Basic program. The first five are fed into local variables called cgi-formtuples(0) through cgi-formtuples(4). The program then forms a SQL SELECT statement on the basis of the values in these variables [lines 33-34]. If at least one matching record is found, the program takes the information in the fields selected and puts it into another set of local variables, called ds(0) through ds(10) [see lines 88-98].
Now the RAO Web server is ready to send its answer back to the UNIX machine. It does so by constructing an HTML document on the fly, and sending each record separated by a horizontal rule [see lines 104-113].
In this example, what the user gets back is a list of records. But because these are within an HTML document, we can also send back the ingredients to form links to other documents. This is what we have done in the case of C&G Memos. What gets returned to the user is the C&G Memo number(s) matching the query specifications. Because we have matched each memo file name with the memo number, we can create links based on the memo number. For example, the text of memo number 95-10 would be contained in a file called 95-10.html, located on the UNIX machine in /raohome/cgmemos. The link to this memo would be: <A HREF = "95-10.html">...</A>. Thus it is easy to create a link (on the fly) to memo number 95-10 just based on the memo number (which is a field in the database).
Hope you have found this informative. Please contact Bill Sellers if you have any further questions.
Refer:
Bill Sellers
510-987-9847
william.sellers@ucop.edu
Subject Index: 10
Organization Index: U-115
David F. Mears
Director
Research Administration Office