On-line database interfaces for the Web

Document Sample
On-line database interfaces for the Web Powered By Docstoc
					On-line database interfaces for the Web


Access to databases on the Web has not received a lot attention compared with the amount of
HTML documents available on the Web. Most Web servers today offer HTML documents written
either directly using the HTML syntax or some kind of HTML assistant tool. However most of
corporate information is stored in databases. In order to support access to this data, one has to
build interfaces between the Web technology and database technology such that end-users can
browse databases through the convenient Web interfaces.

New tools are already appearing on the market which provide extensions to HTML like embedded
SQL, macro languages and special variables. New programming languages like Java and VRML
are introduced and could be combined with database access.

This session presents different ways to implement database applications with a Web interface.
How to retrieve and update data and how to authenticate database access will be explained.
Examples of general tools like interactive SQL, Query by forms, application generators and the
development of these tools will be described.

The Web can also be used internally in a company as an interface to databases. Database related
Web features for the external and internal Web will be listed and discussed.


Dr. Steffen Weigert has been with the ITU (International Telecommunication Union) since 1981
and has worked with database management systems like IDMS and INGRES. He holds a Ph.D. in
Computer Science and a Master’s degree in Economics from Heidelberg University. He teaches
Computer Science as Associate Professor at the Webster University in Geneva. Before joining ITU
he worked for the Information Services Department of the German Cancer Research Center.

                                                                                              Page 1
1.     Introduction

When preparing World TELECOM 95 it was decided to put information about the exhibition, the
forum and the press on a World Wide Web server. The information was partly available in word
processing files but most of the data was stored in the TELECOM database. In order to provide this
information on the Web the word processing files were converted to HTML files. Several programs
were written which enabled direct access to the TELECOM database from the Web. Before writing
theses programs a method had to be found to accept user input from the Web, construct a query,
consult the database and format dynamically the data in the way that it could be displayed on the

2.     How does the Web work?

The World Wide Web (WWW or Web) consists of Web servers like Cern, NCSA, Netscape,…
and WEB clients (browsers) like Netscape, Mosaic, Explorer,…The users of the Web are sending
URLs (Universal Resource Locators like from their browsers to the
Web servers. Most requests are documents written in HTML (HyperText Markup Language).

Figure 1 shows a HTML request:

       Access to HTM L files

        WEB Client                       WEB Server                    Document
                                  1                             2
                                           Document                   forum.html

    Forum 96                      4
 Technology Summit

1           Request forum.html from the server
2           Web server recognizes suffix html and requests from the file system the
            document forum.html
3           The document is handed over to the server
4           The HTML document is send to the Web client and displayed

This method works fine when the data is prepared in HTML format.

                                                                                            Page 2
Information in databases is not formatted in HTML and can therefore not be handled like HTML
documents. Special programs have to be executed on the Web server to provide database access.
All Web servers provide the Common Gateway Interface (CGI) which is a standard for interfacing
application programs with Web servers. The application programs can be written in any
programming language like C, C++, PERL, with a command language like UNIX shell or with
utilities like UNIX awk. The application programs are invoked from the Web browser through HTML
forms or HTML links.

Figure 2 shows a program request:
       WEB CGI

         WEB Client                    WEB Server                  CGI script
                                 1                          2                                         stand.list

                                                            5      4         3

       Exhibition 96             6                                      Database
    Standname = A ...

1       Request to the server
        Server recognizes a cgi program request
2       Execute program stand.list
3       Program stand.list sends SQL to the database
4       Data is returned from the database to the program stand.list
        The program stand.list formats the data using HTML
5       HTML document is sent back to the server
6       HTML document is sent back to the Web browser

The protocol between the Web client and the Web server is HTTP (HyperText Transfer Protocol).
This protocol is a stateless protocol which means that no information is kept on the Web server,
parameters have always to be sent with the URL, one URL can invoke several database
transactions but one transaction can not span URLs. As the programs have to follow the rules of
the HTTP they must be written in a different way then normal computer programs.

The following lines of programming code are producing a list of exhibitors with exhibitor name
beginning with the character L. The program is coded with the UNIX command language.
Characters between < and > are HTML tags. The code starts with sending the type information
text/html to tell to the Web browser that a HTML document has to be displayed. After two header
lines, one for the title bar and one for the document, the SQL Select query is send to the database
to retrieve the data. The formatting of the data is done by nawk, a formatting utility of UNIX. Table
column headers are displayed once, then for each row of data the three fields exhibitor, stand
number and country are formatted and the number of exhibitors is displayed at the end of the
HTML page.

                                                                                                 Page 3
Program code:


# Output header information
cat << EndHTMLHeader
Content-Type: text/html

<TITLE>List of Exhibitors: TELECOM 96</TITLE>
<H1>List of Exhibitors: TELECOM 96</H1>

# Extract data from database
export II_SYSTEM=/usr/ingres
/usr/local/ingres/bin/sqlexp ingprod::tisdb -t -quel << EndSQL |
SELECT standname, standno, shortenglishname
FROM   www_stand_96 WHERE standname LIKE 'L*' ORDER BY standname;

# Output formatting
nawk ' BEGIN { FS = "\t"
  printf "<TR><TH>Exibitor</TH><TH>Stand No</TH><TH>Country</TH></TR>"
{ printf "<TR><TD>%s</TD><TD>%s</TD><TD>%s</TD></TR>", $1,$2,$3 }
END { printf "</TABLE><H3>%s Stands found for Exhibitor like
L*.</H3></HTML>",FNR }'

Figure 3 shows the result of the UNIX script:

                                                                    Page 4
3.    Building the TELECOM Web

The programming example in chapter 2 showed that with a few lines of code an on-line database
access is possible on the Web and a nice document can be presented to the user.

With the replacement of the hard coded whereclause of the database query and some additional
formatting options to add hypertext links to the documents the TELECOM Web with on-line
database access has been built.

The general structure of the Web pages is defined as follows:
One page to formulate the query, followed by the display of the result as a list with further hypertext
links to a page with detailed information.
Both pages, list page and detail page contain many hypertext links which again provide more
information in the two formats (list, detail).

Figure 4 shows the query page for exhibitor information:

The query page contains an HTML form with a search button to invoke the application progam and
HTML links with the letters of the alphabet to invoke the same application program with different
parameters. Already a very simple Web page provides a variety of ways to formulate a query to the
database, from a simple click of a button or HTML link to typing some characters, selecting an
option and specifying additional criteria.

Clicking only the search button displays a complete list of exhibitors.

Searching for four different database fields is possible by typing the starting characters, marking the
field and clicking on the search button to execute the query. This query can be combined with
information about the exhibitor’s home page URL or virtual exhibition pages.

In addition a quick search by the alphabetical index of exhibitors is foreseen by just clicking the

                                                                                                  Page 5
Figure 5 shows the result of a database query as a list:

Please note that a sorting feature is a built-in feature of the list page: Clicking one of the column
headers will rerun the database query and sort the result by the column field.
A click on the exhibitor’s name will display detailed information about the exhibitor.

Figure 6 shows detailed information about an exhibitor:

The exhibitor’s details page allows further links to the floor plan via the stand number, to the virtual
stand, to the exhibitor’s home page, and to the forum speakers of the exhibitor.

                                                                                                   Page 6
After having built the programs for querying the database it is easy to add other types of user
interfaces like an image map.

Figure 7 shows an image map to retrieve exhibitor information of a selected European country:

Clicking one of the countries will invoke an application program to retrieve statistics of the country’s
exhibitors from the database and allows further navigation to the already mentioned list and details
of exhibitors (Figure 5, Figure 6).

                                                                                                  Page 7
4.    Intranet - A solution for the distribution of corporate information

Intranets are internal corporate Web sites protected by firewalls from external Internet access.
Corporations have found them to be ideal platforms for getting information to their employees.
Because Intranets allow corporations to communicate up-to-date information to their own
employees easily, securely and economically, they are as important as external Internet Web sites.

In addition to the user interfaces built for the external Web other types of interfaces can be added
when setting up an Intranet.

4.1. Query interface to search for information about an exhibition

The following example shows a generalized query interface where experienced end-users can
formulate simple to complex queries by selecting different fields, a variety of operators and sorting

Figure 8 shows a generalized query interface:

The programming for this query interface is done in a similar way as shown in the programming
example of chapter 2. The only difference is the dynamically built whereclause and sort option of
the SQL statement depending on the parameters selected by the user.

                                                                                                 Page 8
4.2. QBF - Query By Forms

QBF (Query By Forms) is even more general for searching the database. Data of any table or view,
if access granted, can be retrieved.

The features of QBF include:

Flexible whereclause with operators like:
 =, <>, >, >=, <, <=, like, [not] between x and y, [not] in (x,...,z), and, or,...

Display of your selection can be customized:
 Select fields for display
 Customize headings of fields
 Modify outputlength of fields
 Set max. numbers of rows to select

Figure 9 shows the QBF search screen:

The setting of a maximum numbers of rows to retrieve from the database is very important. Even if
the database server could handle a big amount of data it is very bad for the network traffic. Also the
Web browser would have difficulties to store the data and the user would anyway not like to read
through many pages.

                                                                                                Page 9
The data can be displayed in 4 predefined formats:
 SimpleFields - one field with header per line
 TableField - all fields of one row in one line
 HTML Table - as TableField but using HTML Table option
 Form - using HTML Form option

Figure 10 shows data displayed as TableField:

In addition to the 4 predefined formats you are able to interactively modify the layout by rearranging
the order of the columns and by suppressing columns temporarily (option Modify Display).

4.3. Interactive SQL

SQL (Structured Query Language) is the standard query language for relational databases. SQL is
not foreseen for end-users but is very important for programmers who build database applications.
Providing a Web page where experienced users can interactively execute their SQL commands is
very useful for database application development and database maintenance.

To avoid the misuse of interactive SQL on an intranet proper authentication should be established.

                                                                                                Page 10
The following screen has two text areas, the left part for typing your SQL command and the right
part for looking up the SQL command syntax.

Figure 11: SQL interface

Figure 12: Output of the SQL Select command

                                                                                            Page 11
4.4. Web application generator

After having developed customized Web applications for TELECOM 95 and general applications
like Query By Forms and Interactive SQL, it has been a challenge to build a Web application
The generator accepts any table or view of a database and generates programming code for several
different Web search pages, the SQL to retrieve the data and predefined output formats to display
the data on the Web. The generated application can be customized by adding menus and
additional output formats.

Figure 13 shows the result of a generated report after some customization:

The main objectives of the Web application generator are:
 Building a prototype application directly on the Web without knowing the details of programming
 Learning from the generated code how to program Web database applications
 Acquiring experience how to customize existing applications

                                                                                           Page 12
4.5. Database update and authentication

Applications like the maintenance of a country database can be implemented on an internal Web
with multiple windows as graphical interface, retrieval function for any Web user and authentication
for database update, insert and delete.
Multiple windows are available through the HTML frame tag. Authentication can be triggered by
forcing the user to enter user name and password. Access rights are checked on 3 levels: operating
system in order to execute the update program, database to connect and table to access the data.
Only if all three checks can be passed successfully the user is allowed to update the data.

Figure 14: Example of multiple windows and authentication:

5.   Conclusions

The World Wide Web has become the de facto standard of accessing information on the Internet.
The same technology can be used to develop applications for internal Web sites with the
advantage to be immediately available on the Internet if needed.
The Web technology is based on open standards like CGI which enables application programmers
to build computer platform independent Information-On-Demand services.
By connecting Internet or Intranet Web servers to databases many organizations are able to supply
dynamically created information in response to user requests. These Web pages are unique for
each user.
Database software vendors are currently trying to integrate their database products into Web
servers and hopefully they will respect or add open standards, otherwise the biggest advantage of
the Internet which is computer platform independence will suffer.

                                                                                             Page 13

Shared By: