Research Administration Office

University of California



June 23, 1995


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.

Necessary Ingredients

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

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 (; 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 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 RAO Home Page

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:

<TITLE>Research Administration Office</TITLE>
<A NAME="content"></A>
<IMG SRC = "1ogo.gif">
<A HREF = "cgmanual/mantoc.html">Contract and Grant Manual </A>
<A HREF = "cgmemos/memotoc.html">Contract and Grant Memos (Active Only) </A>
<A HREF = "far/fartoc.html">FAR and FAR Supplement Clauses </A>
<A HREF = "gopher://">FDP (and NSF) Terms and Conditions </A>
<A HREF = "gopher://gopher.nihgov:70/11/gopherlib/data/phsgps">PHS Grants Policy Statement</A>
<A HREF = "circulars/circtoc.html" >OMB Circulars </A>
<A HREF = "waivers/waivers.html" >Indirect Cost Waivers [password required!] </A>
<A HREF = "idc/idctoc.html" >Indirect Cost Issues </A>
<A HREF = "" >Office of the President Home Page </A>
<A HREF = ""> </A>

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".

Linking to OP RAO Databases

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, and when it becomes a Web server it thus has the URL (uniform resource 1ocator) of:

Now we have three machines connected together:

1. The UCOP UNIX machine at, containing the RAO home page;

2. The RAO Web server at, 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
  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:")
 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
 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)
 32            'form sql statement, beginning with selection of fields
 33            sqlq = "SELECT far.clause, far.alternate,, 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
 38              Select Case cgi_formtuples(0).value
 40                Case Is = "starts with"
 41                  sqlq = sqlq & " like ('" & UCase(cgi_formtuples(1).value) & "*')"
 43                Case Is = "contains"
 44                  sqlq = sqlq & " like ('*" & UCase(cgi_formtuples(1).value) & "*')"
 46                Case Is = "is exactly"
 47                  sqlq = sqlq & " = ('" & UCase(cgi_formtuples(1).value) & "')"
 49              End Select
 51            Else
 52              'just select all titles
 53              sqlq = sqlq & " like ('*')"
 54            End If
 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
 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
 67                Case Is = "starts with"
 68                  sqlq = sqlq & " like ('" & UCase(cgi_formtuples(4).value) & "*')"
 70                Case Is = "contains"
 71                  sqlq = sqlq & " like ('*" & UCase(cgi_formtuples(4).value) & "*')"
 73                Case Is = "is exactly"
 74                  sqlq = sqlq & " = ('" & UCase(cgi_formtuples(4).value) & "')"
 76              End Select
 78            Else
 79              'just select all clause numbers
 80              sqlq = sqlq & " like ('*')"
 81            End If
 83            'specify order
 84            sqlq = sqlq & " order by far.clause asc"
 86            Set ds = db.CreateDynaset(sqlq)
 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)
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
124    End Select
126    '
127    ' Return to complete HTTP.
128    '
129    Send ("<HR>")
130    Send ("</BODY></HTML>")
132    '****** RETURN, DON'T STOP! ******
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:

<TITLE>FAR Clause Locator</TITLE>
<H1>Search for FAR Clause(s)</H1>
<P><A HREF = "../rao-home.html">Return to Home Page</A></P>

<P>Search for title that <SELECT NAME--"firstHow">
<OPTION SELECTED>starts with
<OPTION>is exactly
the following: <INPUT TYPE="text" NAME="match-title"> [default is 'ANY']</P>

<SELECT NAME="boolean">

<P>the clause number <SELECT NAME="secondHow">
<OPTION SELECTED>starts with
<OPTION>is exactly
the following: <INPUT TYPE="text" NAME="match-clause"> [default is 'ANY']</P>

<P><INPUT TYPE="submit" VALUE=" Submit"></P>
<P>This is an experimental query page. If you have suggestions for improvements, please send a message to <A HREF="">Bill Se!lers</A> at</P>

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


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.


Bill Sellers


Subject Index: 10

Organization Index: U-115

David F. Mears


Research Administration Office