Chapter 37

Web Database Tools

by Robert Niles

• Databases Available
  • Oracle
  • Sybase
  • mSQL
  • Illustra
  • Microsoft SQL
  • Postgres95
  • Ingres
  • FoxPro
  • Microsoft Access
• Database Tools
  • PHP/FI
  • Cold Fusion
  • w3-mSQL
  • MsqlPerl
  • MsqlJava
  • Microsoft's dbWeb
  • WDB
  • Web/Genera
  • MORE
  • DBI
  • DBGateway
• Additional Resources on the Web

It is an appealing idea to take information that is stored in your database and allow its access to those visiting your site (for either Internet or intranet purposes). Not only can it save you the time of reentering all that data to create an HTML document, but it also allows you to use your database to create Web pages that change the moment the information in your database changes.

Not too long ago, it was quite difficult to create Web pages based on information from a database. Now, there is so much support that trying to figure out which way to go can be an intimidating task. Because of this, we'll briefly cover the most favorite databases available and the gateways used to access and place that information on the Web.

Databases Available

In this section, we'll take a quick look at the most commonly used databases on the Web and where you can look for further information and support.

Oracle

Oracle is the largest database developer in the world. Microsoft exceeds them only in the software arena. Oracle provides databases for Windows NT and various UNIX flavors. Oracle has created their own set of tools (mainly PL/SQL) which, coupled with the Oracle Webserver, allows you to create Web pages with little effort from information in the database. PL/SQL allows you to form stored procedures which help speed up the database query. The Oracle database engine is a good choice for large businesses that handle large amounts of information, but, of course, you're going to pay for that. Today's price range for Oracle 7 and the Oracle Web server together is over $5000.00.

Sybase

Sybase System 11 is a SQL database product that has many tools that can be used to produce dynamic Web pages from the information data in your database. A new product by Powersoft, the NetImpact Studio, integrates with Sybase, providing a rich set of tools to help anyone create dynamic HTML documents. The NetImpact Studio consists of an HTML Browser/Editor accompanied by a Personal Web server. These allow you to create pages using a WYSIWYG or "What You See Is What You Get" interface. The Studio also comes with a Web database, support for JavaScript (which they see as the future of CGI scripting), and support for connecting to application servers.

NetImpact can be used in conjunction with PowerBuilder, an application which is used to create Plug-ins and ActiveX components. It also can be used to complement Optima++, which creates Plug-ins and supports the creation of Java applets.

Sybase also can be used with web.sql to create CGI and NSAPI (Netscape Server Application Programming Interface) applications that access the Sybase database server using Perl. Sybase is available for Windows NT, and Unix.

mSQL

As introduced in Chapter 36 , "Custom Database Query Scripts," mSQL is a middle-sized SQL database server for UNIX, which is much more affordable than the commercial SQL servers available on the market. Written by David Hughes, it was created to allow users to experiment with SQL and SQL databases. It is free for non-commercial use (non-profit, schools, and research organizations)-although for individual and commercial use, the price is quite fair, at about $170.00.

Illustra

Illustra, which is owned by Informix, is the commercial version of the Berkeley's Postgres. Illustra uses a ORDBMS, or Object-Relational Database Management System in which queries are performed at very quick speeds. Illustra uses DataBlade modules that help perform and speed up queries. The Web DataBlade module version 2.2 was recently released and allows incorporation of your data on the Web.

Microsoft SQL

Microsoft released their own SQL database server as a part of their back office suite. Microsoft is trying heavily to compete with Oracle and Sybase. They have released the server for $999, but you also must buy the SQL Server Internet Connector, which costs $2,995. These two products allow you to provide unlimited access to the server from the Web.

Postgres95

Postgres95 is a SQL database server developed by the University of California, Berkeley. Older versions of Postgres are also available, but no longer supported.

Ingres

Ingres (Interactive Graphics Retrieval System) comes in both a commercial and public domain version. The University of California at Berkeley originally developed this retrieval system, but Berkeley no longer supports the public domain version. You can still find it on the University's Web site.

Ingres uses the QUEL query language as well as SQL. QUEL is a superset of the original SQL language, making Ingres more powerful. Ingres was developed to work with graphics in a database environment. The public domain version is available for UNIX systems.

Computer Associates owns the commercial version of Ingres. This version is quite robust and capable of managing virtually any database application. The commercial version is available for UNIX, VMS, and Windows NT.

FoxPro

Microsoft's Visual FoxPro has been a favorite for Web programmers, mostly because of its long-time standing in the database community as well as its third party support. Foxpro is an Xbase database system that is widely used for smaller business and personal database applications.

Microsoft Access

Microsoft Access is a relational database management system that is part of the Microsoft Office suite. Microsoft Access can be used to create HTML documents based on the information stored in the Access database with the help of Microsoft's Internet Assistant. Microsoft's Internet Assistant is an add-on that is available free of charge for Access users. Microsoft Access can also support ActiveX controls, which makes Access even more powerful when used with the Microsoft Internet explorer.

A Job forum page was created to allow you to see how Access can be used in conjunction with the World Wide Web.

For more information on Microsoft Access and the Job forum, see

Database Tools

Now that you have taken a look at the various databases available, it's time to take a look at the third-party tools which help you create applications that tie your databases together with the Web.

PHP/FI

PHP/FI was developed by Rasmus Lerdorf, who needed to create a script that enabled him to log visitors to his page. The script replaced a few other smaller ones that were creating a load on Lerdorf's system. This script became PHP, which is an acronym for Rasmus' Personal Home Page tools. Lerdorf later wrote a script that enabled him to embed commands within an HTML document to access a SQL database. This script acted as a forms interpreter (hence the name FI), which made it easier to create forms using a database. These two scripts have since been combined into one complete package called PHP/FI.

PHP/FI grew into a small language that enables developers to add commands within their HTML pages instead of running multiple smaller scripts to do the same thing. PHP/FI is actually a CGI program written in C that can be compiled to work on any UNIX system. The embedded commands are parsed by the PHP/FI script, which then prints the results through another HTML document. Unlike using JavaScript to access a database, PHP/FI is browser independent because the script is processed through the PHP/FI executable that is on the server.

PHP/FI can be used to integrate mSQL along with Postgres95 to create dynamic HTML documents. It's fairly easy to use and quite versatile. An example of how PHP/FI works is shown in Listings 37.1 and 37.2.

Listing 37.1  The HTML Document Contains PHP/FI Code that Stores Information in the Database

<?
echo "<HTML>";
echo "<HEAD><TITLE>Add to phonebook</TITLE></HEAD>";
echo "<BODY>";
>
<H1>Add to the phonebook</H1>
<?
$database = "myphone";

if($ADD == 1);
  msql_connect("localhost");
  $result = msql($database, "select fname,lname from phonebook where 
   fname='$fname' and lname='$lname'");
  if($fname == msql_result($result,0,"fname") && $lname == msql_
result($result,0,"lname"));
  echo "$fname $lname already exists";
  exit;
>
<?else>
<?
msql($database, "insert into phonebook (fname,lname,phone,email) 
VALUES ('$fname','$lname','$phone','$email')");
>
<?endif>
<?endif>


<FORM ACTION="/cgi-bin/php.cgi/phonebook/add.html" METHOD="POST">
<INPUT TYPE="hidden" name="ADD" value="1">
<PRE>
First name:<INPUT TYPE="text" name="fname" maxlength=255>
 Last name:<INPUT TYPE="text" name="lname" maxlength=255>
     Phone:<INPUT TYPE="text" name="phone" maxlength=11>
     Email:<INPUT TYPE="text" name="email" maxlength=255>
</PRE>
<P>
<INPUT TYPE="submit">
<HR>
<CENTER>
<A HREF="index.html">[Phonebook]</A>
</CENTER>
</FORM>
</BODY>
</HTML>

Now, the following script allows you to view the phonebook:

Listing 37.2  PHP/FI Queries the Database and Displays the Result Inside the HTML Document

<HTML>
<HEAD><TITLE>My Phonebook</TITLE></HEAD>

<BODY>
<H1>My Phonebook</H1>

<?
msql_connect("localhost");

$database="myphone";

$result = msql($database, "select * from phonebook");
$num = msql_numrows($result);

$i=0;

echo "<TABLE>";
while($i < $num);
    echo "<TR><TD>";
    echo msql_result($result,$i,"fname");
    echo " ";
    echo msql_result($result,$i,"lname");
    echo "</TD><TD>";
    echo msql_result($result,$i,"phone");
    echo "</TD><TD>";
    echo msql_result($result,$i,"email");
    echo "</TD></TR>";
    $i++
  endwhile;
>
</TABLE>
</BODY>
</HTML>

Cold Fusion

Allaire created Cold Fusion as a system that enables you to write scripts within an HTML. Cold Fusion, a database interface, processes the scripts and then returns the information within the HTML written in the script. Although Cold Fusion currently costs $495, the product is definitely worth the price. Allaire wrote Cold Fusion to work with just about every Web server available for Windows NT, and it integrates with just about every SQL engine-including those database servers available on UNIX machines (if a 32-bit ODBC driver exists).

Cold Fusion works by processing a form, created by you, that sends a request to the Web server. The server starts Cold Fusion and sends the information to Cold Fusion, which is used to call a template file. After reading the information that the visitor entered, Cold Fusion processes that information according to the template's instructions. It then returns an automatically generated HTML document to the server and then returns the document to the visitor.

For example, the following form asks the visitor to enter his or her name and telephone number. Once the visitor clicks Submit, the form is processed by Cold Fusion which calls the template, Enter.dbm.

<HTML>
<HEAD><TITLE>Phonebook</TITLE></HEAD>
<BODY>
<FORM ACTION="/cgi-bin/dbml.exe?Template=/phone/entry/enter.dbm"
METHOD="POST">
Enter your full name:<INPUT TYPE="text" NAME="name"><BR>
Enter your phone number:<INPUT TYPE="text" NAME="phone"><P>
<INPUT TYPE="submit">
</FORM>
</BODY>
</HTML>

The template contains a small script that inserts the information into the database and then displays an HTML document to the visitor. This document thanks them for taking the time to enter their name and telephone number into the database.

<DBINSERT DATASOURCE="Visitors" TABLENAME="Phone">
<HTML>
<HEAD><TITLE>Thank you!</TITLE></HEAD>
<BODY>
<H1>Thank your for your submission!<H1>
Your name and phone number has been entered into our database.
Thank you for taking the time to fill it out.
<P>
<A HREF="main.html">[Return to the main page]</A>
</BODY>
</HTML>

Although Cold Fusion is a lot more complex than this, you can get an idea of how easy it is to handle information and place that information into the database.

For more information on Cold Fusion, visit the Allaire Web site, at

w3-mSQL

w3-mSQL was created by David Hughes, the creator of mSQL, to simplify accessing an mSQL database from within your Web pages. It works as a CGI script that your Web pages go through to be parsed. The script reads your HTML document and performs any queries required and sends the result back out to the server and then to the visitor. w3-mSQL is much like PHP/FI but on a smaller scale. w3-msql makes it easy for you to create Web documents that contain information based on what is in your database.

A sample bookmarks script and database dump are included within the w3-mSQL archive.

For more information on w3-mSQL, see

MsqlPerl

MsqlPerl is a Perl interface to the mSQL database server. Written by Andreas Koenig, it utilizes the mSQL API and allows you to create CGI scripts in Perl, complete with all the SQL commands available to mSQL.

Chapter 36, "Custom Database Query Scripts," has examples on how to use MsqlPerl and details the use of MsqlPerl along with mSQL.

MsqlJava

MsqlJava is an API that allows you to create applets that can access an mSQL database server. The package has been compiled with the Java Developer's Kit version 1.0 and tested using Netscape 3.0.

Microsoft's dbWeb

Microsoft's dbWeb allows you to create Web pages on-the-fly with the use of an interactive Schema Wizard. The Schema Wizard is a GUI interface that specifies what is searched for within the database and which fields will appear within the Web page.

dbWeb allows you to publish information from a database in HTML format without knowing any HTML programming or making you learn how to use the ISAPI interface.

You can use dbWeb with the Microsoft Internet Information Server and it supports the Oracle database server, the Microsoft SQL server, Access, Visual FoxPro, and any other databases that support the 32-bit ODBC driver.

WDB

WDB is a suite of Perl scripts that helps you create applications that allow you to integrate SQL databases with the World Wide Web. WDB provides support for Sybase, Informix, and mSQL databases but has been used with other database products as well.

WDB uses what its author, Bo Frese Rasmussen, calls "form definition files," which describe how the information retrieved from the database should display to the visitor. WDL automatically creates forms on-the-fly that allow the visitor to query the database. This saves you a lot of the work to prepare a script to query a database. The user submits the query and WDB then performs a set of conversions, or links, so the visitor can perform additional queries by clicking one of the links.

Web/Genera

Web/Genera is a software toolset that is used to integrate Sybase databases with HTML documents. Web/Genera can be used to retrofit a Web front end to an existing Sybase database, or it can be used to create a new one. When using Web/Genera, you are required to write a schema for the Sybase database indicating what fields are to be displayed, what type of data they will contain, what column they'll be stored in, and how you want the output of a query formatted. Next, Web/Genera processes the specification, queries the database, and formats an HTML document. Web/Genera also supports form-based queries and whole-database formatting that turns into text and HTML.

The main component of Web/Genera is a program called symfmt, which extracts objects from Sybase databases based on your schema. Once the schema is written, compile the schema using a program called sch2sql, which creates the SQL procedures that extract the objects from the database.

Once you have compiled the schema, you can retrieve information from the database using URLs. When you click a link, the object requested is dynamically loaded from the Sybase database and formatted as HTML and then displayed to the visitor.

Web/Genera was written by Stanley Letovsky and others for Unix.

MORE

MORE is an acronym for Multimedia Oriented Repository Environment and was developed by the Repository Based Software Engineering Program (RBSE). MORE is a set of application programs that operate in conjunction with a Web server to provide access to a relational (or Oracle) database. It was designed to allow a visitor access to the database using a set of CGI scripts written in C. It was also designed so that a consistent user interface can be used to work with a large number of servers, allowing a query to check information on multiple machines. This expands the query and gathers a large amount of information.

DBI

DBI's founder, Tim Bunce, wanted to provide a consistent programming interface to a wide variety of databases using Perl. Since the beginning, others have joined in to help build DBI so that DBI can support a wide variety of databases through the use of a Database Driver, or DBD. The DBD is simply the driver that works as a translator between the database server and DBI. A programmer only has to deal with one specification and the drivers handle the rest transparently.

So far, the following databases have database drivers. Most are still in testing phases, although they are stable enough to use for experimenting.

Oracle	mSQL
Ingres	Informix
Sybase	Empress
Fulcrum	C-ISAM
DB2	Quickbase
Interbase

DBGateway

DBGateway is a 32-bit Visual Basic WinCGI application that runs on a Windows NT machine as a service that provides World Wide Web access to Microsoft Access and FoxPro databases. It is being developed as part of the Flexible Computer Integrated Manufacturing (FCIM) project. DBGateway is a gateway between your CGI applications and the Database servers. Because your CGI scripts only "talk" with the Database Gateway, you need only to be concerned with programming for the Gateway instead of each individual database server. This performs two functions-programming a query is much easier because the gateway handles the communication with the database and scripts can be easily ported to different database systems.

The gateway allows a visitor to your site to submit a form which is sent to the server. The server hands the request to the gateway which decodes the information and builds a query forming the result based on a template, or it can send the result of the query raw.

Additional Resources on the Web

Additional information on Web database gateways are found at the Web-Database Gateways page at

http://gdbdoc.gdb.org/letovsky/genera/dbgw.html

and also on Yahoo at

http://www.yahoo.com/Computers_and_Internet/World_Wide_Web/Databases_and_Searching