by Melissa Niles
Almost every organization, no matter the organization's size, uses a database for one reason or another. An untold amount of information is stored in a database, whether that information is about people (employment information, personal information, performance issues) or news-making events (bad weather, deaths, birth, marriages, or social events). That information may be your company's inventory of products, how your company is doing in sales, or who your most prominent customers are. Whatever the case, individuals and organizations alike want or need to store information--most of which is kept in some sort of database. Now comes the World Wide Web and, as you can tell, just about every company wants some sort of presence on the Web. You can easily see this by all the www-dot-whatever-dot-coms placed in just about every commercial on television!
If your organization, or the organization you are developing a Web presence for, has information in a database it would like to provide on the World Wide Web, it can be done one of two ways. The first is to simply take a look at the information in the databases and place relevant information in an HTML document. If any of the information changes they not only have to edit the contents of the database, they also have to edit the HTML document.
The second way is to simply develop applications that query the database and generate a document, returning the latest information available. If database information changes, the Web documents automatically reflects those changes.
A multitude of databases can be used with your Web application to provide dynamic information to site visitors. What you end up using depends mostly on what your organization is currently using, or what fits into your organization's budget. This section lists some of the more common database solutions that are widely used on the Web.
Oracle is the largest database developer in the world, providing databases for Windows NT and various UNIX flavors. Oracle has created its own set of tools (mainly PL/SQL, in conjunction with the Oracle Web Agent). These tools, coupled with the Oracle Webserver, allow you to create Web pages with little effort using information stored in the database. PL/SQL allows you to form stored procedures that help speed 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 $5,000.
ON THE WEB: http://www.oracle.com/products/tools/WDS/ Visit Oracle's Web page for more product information and how you can use Oracle with the World Wide Web.
Sybase System 11 is a SQL database product that has tools for a dynamic Web page production. A product by Powersoft, the NetImpact Studio, integrates with Sybase to provide 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. They allow you to create pages using a WYSIWYG interface. The Studio also comes with a Web database, support for JavaScript, and support for connecting to application servers.
NetImpact can be used in conjunction with PowerBuilder, an application used to create plug-ins and ActiveX components. It also can be used to complement Optima++, which is used to create plug-ins and supports Java applet creation.
Sybase can also 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.
ON THE WEB: http://www.sybase.com/ For more information on Sybase, see their Web page.
mSQL is a middle-sized SQL database server for UNIX that 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. Version 1.0.16 is free for non-commercial use, but you have to pay for individual and commercial use. The price is quite fair--about $170. Version 2.0 has just been released with new tools and even more powerful queries.
For additional information on mSQL, along with documentation and a vast array of user- contributed software, see http://Hughes.com.au/ or http://AusWeb.com.au/computer/Hughes/.
Illustra, owned by Informix, is the commercial version of the Berkeley's Postgres. Available for both Windows NT and UNIX, Illustra uses an ORDBMS, or Object-Relational Database Management System. By using ORDBMS, queries are performed at very quick speeds. Illustra also uses DataBlade modules that help perform and speed queries. The Web Datablade module version 2.2 allows the incorporation of your data on the Web with reduced effort. For more information see http://www.informix.com/, which contains detailed information on Illustra, along with additional information on how you can use Illustra with your Web-based applications.
Microsoft released its own SQL database server as a part of its Windows NT Back Office Suite. Microsoft is trying to compete with Oracle and Sybase. It has released the server for about $1,000 (at the time of this writing), but you must also buy the SQL Server Internet Connector, which costs about $3,000. These two products allow you to provide unlimited access to the server from the Web.
For additional information on Microsoft's SQL Server and how you can use Microsoft's SQL Server in conjunction with the World Wide Web, see http://www.microsoft.com/sql/.
Postgres95 is a SQL database server developed by the University of California at Berkeley for use on UNIX systems. Older versions of Postgres are also available but no longer supported. The site, at http://s2k-ftp.CS.Berkeley.EDU:8000/postgres/, provides additional information about Postgres95, along with the source code, which is available for download.
Ingres (Interactive Graphics Retrieval System) comes in both a commercial and public domain version. Berkeley originally developed Ingres to work with graphics in a database environment, but the school 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, making Ingres even more powerful. The public domain version is available for UNIX systems at ftp://s2k-ftp.cs.berkeley.edu/pub/ingres/.
Computer Associates owns the commercial version of Ingres, called OpenIngres. This version is quite robust and capable of managing virtually any database application. The commercial version is available for UNIX, VMS, and Windows NT. For more information about the commercial version, visit http://www.cai.com/products/ingr.htm.
For more information about both the commercial and public domain versions of Ingres, visit the North American Ingres Users Association at http://www.naiua.org/.
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. Foxpro is also available for most Windows platforms.
For more information on FoxPro, see http://www.microsoft.com/catalog/products/visfoxp/ and visit Neil's Foxpro database page at http://adams.patriot.net/~johnson/neil/ fox.html.
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 or with the use of Microsoft's Active Server Pages (ASP). Microsoft's Internet Assistant is an add-on available free of charge for Access users. Using Microsoft's ASP technology requires the use of MS Information Server with ASP installed. Microsoft Access can also support ActiveX controls, which make 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 http://www.microsoft.com/Access/Internet/JobForum/.
If for some reason the database your organization uses isn't listed here, don't worry. Most likely someone out there has created an interface in which you can use your database on the World Wide Web. The best way to find out is to perform a search using one of the many search engines available. For a starting point, take a look at http://www.yahoo.com/Computers_and_Internet/World_Wide_Web/Databases_and_Searching.
Choosing a database to suit your organization's needs is difficult, and should be carefully planned. It's quite difficult to tell you which database would best suit your needs without spending a bit of time with a company and seeing how that company operates. The person who would know which database is best for your organization is you. Even so, Table 37.1 might help you narrow down your choices.
| Database | Platforms | Suggested Use |
| Oracle | UNIX, NT | Large business |
| Sybase | UNIX, NT | Large business |
| mSQL | UNIX | Personal, small business |
| Illustra | UNIX, NT | Medium to large business |
| MS SQL | NT | Medium to large business |
| Postgres95 | UNIX | Personal and small to medium business |
| Ingres | UNIX, NT | Small to large business |
| Foxpro | Windows Macintosh | Small to medium business |
| MS Access | Windows | Personal and small to medium business |
Just as there are multiple databases available, there are also multiple methods of integrating your database with the World Wide Web. What tools you should use depends heavily on what platform your database resides, your knowledge of programming, and your programming language skills. In the following sections, you take a look at a few of the most common tools that make accessing databases easy for Web developers.
PHP/FI was developed by Rasmus Lerdorf, who needed to create a script that enabled him to log visitors on to his page. The script replaced a few smaller ones that were creating a load on Lerdorf's system. This script became PHP, which is an initialization 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 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. You can visit http://www.vex.net/php/ for more information on PHP/FI, along with examples of how PHP/FI can be used.
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 information within the HTML text in the script. Cold Fusion currently costs anywhere from $89 to $995, depending on your needs. The product is definitely worth the price. Allaire wrote Cold Fusion to work with just about every Web server available for Windows NT and integrates with just about every SQL engine--including those database servers available on UNIX machines (if a 32-bit ODBC driver exists). A version for Sun Solaris has also been recently released.
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 the visitor entered to Cold Fusion engine, which is used to call a template file. After reading the information the visitor entered, Cold Fusion processes that information according to the template's instructions. Next, it returns an automatically generated HTML document to the visitor. For more information on Cold Fusion visit the Allaire Web site at http://www.allaire.com/.
w3-mSQL was created by David Hughes, the creator of mSQL, to simplify accessing an mSQL database from within your Web pages. w3-mSQL works as a CGI script which is used to parse your Web pages. The script reads your HTML document, performs any queries required, and sends the result to the server and then on to your site's visitor. w3-mSQL is much like a smaller-scale PHP/FI; it 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 is included within the w3-mSQL archive. For more information see http://hughes.com.au/software/w3-msql.htm.
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. You can download MsqlPerl at ftp://Bond.edu.au/pub/Minerva/msql/Contrib/.
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. Additional information on MsqlJava can be found on the following Web site. You can also download the latest version and view the online documentation, as well as see examples of MsqlJava in action at http://mama.minmet.uq.oz.au/msqljava/.
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 author Bo Frese Rasmussen calls "form definition files," which describes how the information retrieved from the database should be displayed on the visitor's web browser. WDL automatically creates forms on-the-fly that allow the visitor to query the database. This saves you a lot of the work preparing a script to query a database. The user submits the query and WDB performs a set of conversions, or links, so the visitor can perform additional queries by clicking one of the links. Visit the WDB home page for further information on WDB: http://arch-http.hq.eso.org/wdb/html/wdb.html.
Web/Genera is a software toolset 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 contain, what column they are stored in, and how you want the output of a query formatted. Next, Web/Genera processes the specifications, 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.
Web/Genera's main component is a program called symfmt, which extracts objects from Sybase databases based on your schema. After the schema is written, compile the schema by using a program, called sch2sql, which creates the SQL procedures that extract the objects from the database.
After 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, formatted as HTML, and then displayed to the visitor.
Web/Genera was written by Stanley Letovsky and others for UNIX. The Web/Genera site contains additional information. Along with downloading the latest version, this site talks about Web/Genera's history and how it can be used today. You can find the Web site at http://gdbdoc.gdb.org/letovsky/genera/.
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 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. Visit the MORE Web site for additional information on both MORE and RBSE at http://rbse.jsc.nasa.gov:81/DEMO/.
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 it 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 use the appropriate method to access any given database.
The following databases are a few that 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 | Postgres |
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 communicate with the Database Gateway, you only need to be concerned with programming for the gateway instead of each individual database server. This provides two advantages--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 that 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 query's result raw.
Visit http://fcim1.csdc.com/ to view the DBGateway's user manual, view the online FAQ, and see how DBGateway has been used.
Visual InterDev is a visual interface in which you can create web applications that easily integrate with various databases. Visual InterDev is a graphical environment that allows you to create Active Server Pages (ASP). It comes with a full set of tools to add a whole range of HTML tags and attributes while allowing you to do so with VBScript or Jscript. For more information, see http://www.microsoft.com/vinterdev/.
As you see, there are many options available, both in which database you might consider and what tools are available. Even so, most of these options are expensive and some of the databases and tools discussed might not be practical for small businesses.
For the smaller organization you can still provide dynamic content using less expensive options. Two of these are flat file and dbm databases, which cost no more than the convenience of having a machine that already provides Web services. Whether you have your own Web server or are hosting your pages on an ISP's Web server, flat file and dbm databases are an option to consider. If the databases are relatively small, no visitor will be the wiser.
Other than being cheap, flat file databases are just about the easiest you can create. Beyond the necessary programming language, there is nothing else needed to create a small ASCII text database.
A flat file database consists mainly of lines of text where each line is its own entry. There is no special technique to indexing the database. Because of this, flat file databases are usually relatively small. The larger the database, the longer it takes to perform queries to the database.
A Simple Flat File Example With any database there are three important factors in designing Web-based database applications. First, you need to be able to read from the database. Second, you need to search the database. Last, if you want visitors to order products, or even if you want to easily manage the information in your database, you want to be able to write to the database. With these factors in mind, you want to design a simple phonebook database which allows you to write, read, and search a database for information. With the ability to perform these three functions, you can easily design your own database to suit your specific needs, or simply change these examples, customizing each one. With minor changes, these scripts can be used for inventory management, storing personnel information, or whatever suits your fancy.
Designing the HTML Pages For your phonebook, the first thing that you need is an HTML page, which allows someone to enter information into, or read from, the database. You must first decide what you want the visitor to enter. In this example, the HTML document consists of three forms. The first form allows the visitor to enter information into the phonebook database. The second form allows the visitor to display the contents of the database, and the third form allows the visitor to perform a keyword search on the database.
You can expand on this later, but right now you simply want the visitor to be able to enter a first name, a last name, and a telephone number, all of which are stored in your flat file database.
The first form assigns the input from the visitor into three names: fname, lname, and phone. A hidden input type (see Listing 37.1), named act (for action), is created, which tells your script which action it is expected to perform.
<HTML> <HEAD><TITLE>Flat file Phonebook</TITLE></HEAD> <BODY> <H1>Your Flat file Phonebook</H1> <HR> <H2>Insert Information</H2> <FORM ACTION="/cgi-bin/pbook.pl" METHOD="POST"> <PRE> First Name: <INPUT TYPE="text" NAME="fname"> Last Name: <INPUT TYPE="text" NAME="lname"> Phone Number: <INPUT TYPE="text" NAME="phone"> </PRE> <INPUT TYPE="hidden" NAME="act" VALUE="add"> <INPUT TYPE="submit" value="Add to Phonebook"> </FORM> <HR><P> <H2>Display Information</H2> <FORM ACTION="/cgi-bin/pbook.bat" METHOD="POST"> <INPUT TYPE="hidden" NAME="act" VALUE="display"> Click on <INPUT TYPE="submit" value="Display"> to view all entries in the phonebook </FORM> <HR><P> <H2>Search the Phonebook</H2> <FORM ACTION="/cgi-bin/pbook.bat" METHOD="POST"> Enter a keyword to search for: <INPUT TYPE="text" NAME="keyword"> <INPUT TYPE="hidden" NAME="act" VALUE="search"> <INPUT TYPE="submit" VALUE="Start Search"> </FORM> </BODY> </HTML>
Writing to a Flat File Database The first section of your script (see Listing 37.2) separates the information coming from the form that your visitor filled out. After that, the script checks to see if the action requested was to add information to the database. If so, the database file is opened and the information from the visitor is placed in the database file on a single line.
if ($ENV{`REQUEST_METHOD'} eq `POST')
{
read(STDIN, $buffer, $ENV{`CONTENT_LENGTH'});
@pairs = split(/&/, $buffer);
foreach $pair (@pairs)
{
($name, $value) = split(/=/, $pair);
$value =~ tr/+/ /;
$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
$contents{$name} = $value;
}
}
print "Content-type: text/html\n\n";
$phonebook = "phonebk.txt";
if ($contents{`act'} eq "add") {
open(BOOK, ">>$phonebook") || do {&no_open;};
print BOOK "$contents{`fname'}:$contents{`lname'}:$contents{`phone'}\n";
close(BOOK);
print <<"HTML";
<HTML>
<HEAD><TITLE>Information added</TITLE></HEAD>
<BODY>
<H1>Information added</H1>
The information entered has been added to the phonebook.
<HR>
<CENTER>
<A HREF="/pbook.html">[Return to the Phonebook]</A>
</CENTER>
</BODY>
</HTML>
HTML
exit;
}
sub no_open {
print <<"HTML";
<HTML>
<HEAD><TITLE>Error!</TITLE></HEAD>
<BODY>
<H1> Error! Could not open the database!</H1>
<CENTER>
<A HREF="/pbook.htm">[Return to the Phonebook]</A>
</CENTER>
</BODY>
</HTML>
HTML
exit;
}
At this time, the information entered by the visitor has been placed in the database. If you were to take a look at the text file holding the information provided by the visitor, it would look something like:
John:Doe:555-5555
This format is called colon delimited, meaning that each field is separated by a colon. Any character can be used to delimit each field in a flat file database, and it is best to use a character that is not present in a field. Using the colon, for example, would not be wise if you wanted to include an URL in the database. In that case, a comma might be more suitable.
Reading from a Flat File Database If the visitor clicked Display, the information from the database is retrieved and simply appears to the visitor in a table. By using a table, the contents of the phonebook can easily be formatted into something that is easy to view. As you can see in Listing 37.3, you check to see if the value of act is equal to display; if so, a page is created and the contents of your database appear, where each line of information is broken into its respective part. To accomplish this, use Perl's split function. The value of $line is split and assigned to the array entry. By splitting each line, you can control how you want the information to appear to the visitor (see Figure 37.1).
FIG. 37.1
You can control how the information is displayed by separating the contents of the
database.
if ($contents{`act'} eq "display") {
...
open (BOOK, $phonebook) || do {&no_open;};
until (eof(BOOK))
{
$line = <BOOK>;
@entry = split(/:/, $line);
print "<TR><TD>$entry[0] $entry[1]</TD><TD> $entry[2]</TD></TR>";
}
close(BOOK);
...
Once the information from the database is displayed, finish the HTML document and exit the script.
Searching a Flat File Database Last, you want to see if the visitor requested to perform a keyword search (see Listing 37.4). If so, you need to open the database and check each line against the keyword entered by the visitor. First, the database is opened and the top portion of the results page is created. Next, you have a counter, which is initially set to zero (more on this in a moment). Now, each line is read and checked against the value contained in the variable $contents{`keyword'}. If so, the count is incremented and the result printed as part of the created Web page (see Figure 37.2). Use the same technique here that you did earlier--split each line to be printed into an array.
FIG. 37.2
By providing a method in which the visitor can search through a database, you remove
the need to weed through excess information.
Once you exit the loop, you check the count. If the count is equal to zero, you know there were no entries in the database that matched the keyword search, and you inform the visitor that their search produced no results. Listing 37.4 gives you an idea of how this can be accomplished.
if ($contents{`act'} eq "search") {
open (BOOK, "$phonebook") || do {&no_open;};
$count=0;
until (eof (BOOK))
{
$line = <BOOK>;
chop($line);
if ($line =~ /$contents{`keyword'}/gi)
{
$count++;
@entry = split(/:/, $line);
print "<TR><TD>$entry[0] $entry[1]</TD><TD> $entry[2]</TD></TR>";
}
}
if ($count==0)
{
print "No Matches";
}
close(BOOK);
Once the script has checked each line against the keyword, the database is closed; the script finishes the Web page and exits the script.
For the script in its entirety, see the CD-ROM accompanying this book. A version for those using Perl on a Windows NT or Windows 95 platform is available as well.
Most UNIX systems have some sort of dbm database. dbm is a set of library routines that manage data files consisting of key and value pairs. The dbm routines control how users enter and retrieve information from the database. Although not the most powerful mechanism for storing information, using dbm is a faster method of retrieving information than using a flat file. Because most UNIX sites use one of the dbm libraries, the tools you need to store your information to a dbm database are readily available.
There are almost as many flavors of dbm libraries as there are UNIX systems. Although most of these libraries are incompatible with one another, all basically work the same way. You'll first explore each of the dbm flavors to give you a good understanding of their differences. Afterward, you are shown how to create an address book script, which should give you a better idea of how dbm databases work.
The following list describes the most common dbm libraries available and explains some of the differences between each of the dbm libraries.
If for some reason you can't find a particular dbm database on your system, do a search on the Web for dbm databases. With the exception of dbm, the other interfaces are freely available. You will likely want at least one of the dbm interfaces on your favorite FTP site.
dbm databases are relatively easy to use. Unfortunately, the use of dbm is poorly documented. Even so, you can find some additional information at http://www.polaris.net/docs/gdbm/.
A dbm Example ow that the preceding section has briefly introduced the various dbm interfaces, this section demonstrates how you can use them. As mentioned earlier, dbm databases can decrease the amount of time it takes to insert and retrieve information to and from a database. When your database starts growing, and using one of the larger database engines is still overkill (especially on your pocketbook), you might want to look into using dbm.
Inserting Information into a dbm Database dbm databases have two fields: a key and the data (sometimes called the value). The key is simply a string that points to the data. The string within the key must be unique within a database. Note the following example:
| Key | Data |
| Robert Niles | rniles@selah.net |
| Jason Lenny | jason@yakima.net |
| Ken Davis | kdavis@your.com |
| John Doe | jdoe@imtired.selah.net |
| Santa Claus | sclaus@north.pole.com |
| Bert | rniles@selah.net |
The main components for using dbm are the Perl module, DB_FILE.PM, and the file control module, FCNTL.PM (see Listing 37.5). DB_FILE.PM acts as an interface to the dbm routines. The FCNTL.PM module provides functions that enable you to control access to the database so that two or more people cannot write to the database at the same time.
#!/usr/bin/perl
use DB_File;
use Fcntl;
if ($ENV{`REQUEST_METHOD'} eq `POST')
{
read(STDIN, $buffer, $ENV{`CONTENT_LENGTH'});
@pairs = split(/&/, $buffer);
foreach $pair (@pairs)
{
($name, $value) = split(/=/, $pair);
$value =~ tr/+/ /;
$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C",
hex($1))/eg;
$form{$name} = $value;
}
}
$file="addresses";
To connect to the database, you use the tie() function. The syntax for tie() is as follows:
tie(%hashname, DB_File, filename, flags, mode)
You can assign any hashname, although the name must start with the percent sign (%). Because you are using the DB_FILE.PM module, you must specify the filename. The filename identifies the file in which you are storing the data. You next specify the flags, which vary depending on which dbm routines you are using. In this example, you use the flags that enable you to read from and write to files (O_RDWR) or, if the file doesn't exist, to create files (O_CREAT). The mode sets the database file's permissions. The mode 0660 specifies that the owner can read from and write to the database file, and that the group can only read from the file.
$database=tie(%db, `DB_File', $file, O_RDWR|O_CREAT, 0660);
This next, very simple line, is what magically adds the information to the database. You use the hash variable to enter the information into the database. The syntax for this line is as follows:
$hashname{key}=value
The following line places $form{`name'} into the database as the key, and assigns $form{`email'} to that key as its value:
$db{$form{`name'}}=$form{`email'};
Now you use the untie() function, which releases control of the database, and then undef(), which undefines the variable $database:
untie(%db); undef($database);
Although a dbm database can contain only the key and a pair, you can trick the database into adding more fields. For example, if you want to include a telephone number, you have to add it to the value; by inserting some sort of separator, you can later identify the two separate entries. The following example uses the join() function, which joins separate strings together with a colon:
$db{$form{`name'}}=join(":",$form{`email'},$form{`phone'});
For example, if $form{`email'} contains the string jdoe@selah.net, and $form{`phone'} contains the string 555-5555, the join() function produces the following string:
jdoe@selah.net:555-5555
Because e-mail addresses and telephone numbers do not include colons, the colon is probably the best choice for separating each entry. Of course, what you use to delimit each entry depends on what kind of data you need to store.
What happens if a user tries to enter a name already stored in a key? As your script currently stands, it would simply continue as if nothing happened. However, because each key must be unique, the script does not add the information to the database. To tell the visitor that the entry failed, you have to add a line that checks whether the name entered matches an existing key.
&error if $db{"$form{`name'}"};
Your error subroutine tells the visitor what happened and gives the visitor a chance to try again:
sub error {
print <<"HTML";
<HTML>
<HEAD><TITLE>Error!</TITLE></HEAD>
<BODY>
<H1>Error! -Name exists</H1>
I'm sorry, but the name you entered already exists.
If you wish to try again, click
<A HREF="/dbbook.html">here</A>.
</BODY>
</HTML>
HTML
exit;
}
Retrieving Information from a dbm Database ow that you have entered information into the database, you want to enable your site's visitors to retrieve the information. Listing 37.6 starts this process by accessing the database and generates the top portion of the Web page.
#!/usr/bin/perl # dbbook.pl use DB_File; use Fcntl; print "Content-type: text/html\n\n"; $file="addresses"; $database=tie(%db, `DB_File', $file, O_READ, 0660) || die "can't"; print <<"HTML"; <HTML> <HEAD><TITLE>Simple dbm address book</TITLE></HEAD> <BODY> <CENTER> <H1>A Simple Address Book</H1> <TABLE BORDER=1> HTML
Because you've entered the phone number and e-mail address into the value separated by a colon, you now must separate the phone number and e-mail address. The easiest way to do so is with the split() function. After you split this information, you pass the contents to the array @part:
while (($key,$value)= each(%db)) {
@part = split(/:/,$value);
You can use an if statement to check whether the visitor entered an e-mail address. If the visitor did so, you print a link, using the HREF anchor. Otherwise, you simply print the key, then use the array to print the e-mail address and phone number:
if ($part[0]) {
print "<TR><TD><A HREF=\"mailto:$part[0]\">$key</A></TD>";
}
else {
print "<TR><TD>$key</TD>";
}
print "<TD>$part[0]</TD><TD>$part[1]</TD></TR>\n";
}
Finally, you finish the Web page, closing the table, body, and HTML file. You need to use untie() to release control of the database, undefine the $database variable, and exit the script.
print <<"HTML"; </TABLE> <P> <A HREF="/dbbook.html">[Add to address book]</A> </BODY> </HTML> HTML untie(%db); undef($database); exit;
Searching a dbm Database You have learned how to enter information into the database and also how to retrieve information from the database. What would happen if the database starts to become extremely large? If you have 100 entries in the database, it could make for an extremely large Web page if you were to display everything within the database. Also, looking for a single name in the database could be a pain. To solve this problem, you can enable the visitor to search through the database (see Listing 37.7). For example, a visitor could enter the last name Doe, thus narrowing down the number of names the visitor has to weed through.
#!/usr/bin/perl
# dbbooksearch.pl
use DB_File;
use Fcntl;
if ($ENV{`REQUEST_METHOD'} eq `POST')
{
read(STDIN, $buffer, $ENV{`CONTENT_LENGTH'});
@pairs = split(/&/, $buffer);
foreach $pair (@pairs)
{
($name, $value) = split(/=/, $pair);
$value =~ tr/+/ /;
$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C",
hex($1))/eg;
$form{$name} = $value;
}
}
print "Content-type: text/html\n\n";
$file="addresses";
$database=tie(%db, `DB_File', $file, O_READ, 0660)
|| die "can't";
print <<"HTML";
<HTML>
<HEAD><TITLE>Simple dbm address book results</TITLE></HEAD>
<BODY>
<CENTER>
<H1>A Simple Address Book Results</H1>
<TABLE BORDER=1>
HTML
As each key/value pair loads from the database, each key is checked to see whether the key matches the string that the visitor entered:
while (($key,$value)= each(%db)) {
if ($key =~ /$form{`name'}/i) {
NOTE: The /i switch, using Perl, allows for case-insensitive matching. Therefore, if the user enters Bert, bErT, or bert, the entry would match Bert, bert, Robert, or Bertman.
If you find a match, you print it.
@part = split(/:/,$value);
if ($part[0]) {
print "<TR><TD><A HREF=\"mailto:$part[0]\">$key</A></TD>";
}
else {
print "<TR><TD>$key</TD>";
}
print "<TD>$part[0]</TD><TD>$part[1]</TD></TR>\n";
}
}
Then you complete the Web document, untie the database, undefine the variable, and exit the script.
print <<"HTML"; </TABLE> <P> <A HREF="/dbbook.html">[Add to address book]</A> </BODY> </HTML> HTML untie(%db); undef($database); exit;
You could also search the value. Such a search gives you even more flexibility on what information is returned.
The script in its entirety is located on the CD-ROM that accompanies this book. As well, there is a version for Windows NT and Windows 95 (pbook.zip) on the CD-ROM, which performs the same functions using the sdbm database that is distributed with Perl.
Most SQL database servers consist of a set of programs that manage large amounts of data. These programs offer a rich set of query commands that help manage the power behind the SQL server. The programs also control the storage, retrieval, and organization of the information within the database. Thus, you can change, update, and remove the information within the database--after the support programs or scripts are in place.
A relational database doesn't link records together physically, like a dbm database does with the key and value pair. Instead, a relational database simply provides a field that can match information and returns the results as though the information were organized that way.
Relational databases store information in tables. A table is like a miniature database stored within a main database that groups the information together. For instance, you can have one database that contains one or more tables.
Each table consists of columns and rows. The columns identify the data by name, as in the following example:
| Name Home Phone E | |
| Fred Barns 555 | 5555 fbarns@somewhere.net |
| Melissa Devons 555 | 5556 missy@thisplace.com |
| John Doe 555 | 5557 jdoe@whatcha.want.com |
The name, phone number, and e-mail address are the columns, and each entry set is the row.
Although this book's purpose isn't to explain how relational databases work, you need a basic understanding of the concept. Suppose you stored the preceding information in a table called Personal. You can join information from two or more tables within your query. Suppose that the following table is called Work:
| Name | Work Phone | Department |
| Fred Barns | 555-5558 | sysadmin |
| Melissa Devons | 555-5559 | programmer |
| John Doe | 555-5560 | janitor |
Within your query, you can have something like the following:
select * from personal,work where personal.name=work.name
This query would print information about individuals from both tables. Keep in mind that this example is a very simple one. Queries from relational databases can get extremely complex. In fact, there are professionals whose sole job is to manage multiple databases with multiple tables, and ensure that when this information is queried from a database the results are intelligible.
SQL is a query language that has become standardized. The simple language contains commands that query a database for information. The SQL language was written to be easily understandable. If you were to read a query, the query's syntax would sound much the same as if you were explaining the query to another person. Examine the following example:
select personal.name,personal.home_phone,personal.email, work.work_phone, work.department from personal,work where personal.name=work.name
Even the following example would work on most systems:
select * from personal,work where personal.name=work.name
where the asterisk is a wild card that matches everything.
To insert information into a database, use a query as follows:
insert into table (name, home_phone, email) VALUES (`rniles', `555-5555','rniles@selah.net')
You can easily determine what these queries do just by examining them.
Active Server Pages (ASPs) is Microsoft's answer to CGI scripting. Using Microsoft's ASP technology, you can create scripts using Visual Basic Script, Java, Jscript, or even PerlScript without a need to compile your code beforehand.
ASP technology works only with Microsoft's Internet Information Server (IIS) and is part of Microsoft's ActiveX technology. Each script as it is requested by the visitor is compiled and executed on the server at runtime. This creates a very flexible atmosphere in which you can easily develop and test scripts.
One of the most useful functions is the ability to access information from a database. Along with ASP technology, Microsoft provides a component called the Active Database Object (ADO). The ADO provides you with access to any OLE/DB or ODBC-compatible data source for use within your scripts.
If you're at all familiar with how Microsoft's Internet Database Connector (IDC) and the HTML extension (HTX) works, creating ASP scripts will be a piece of cake. ASP scripts work much the same way, where all scripting is contained within a document and inside the <% ... %> tags. For example, take a look at the following code:
<%
if err.number > 0 then
response.write("An error occured while adding this name")
end if
%>
Within the tags we have a short snippit of VBScript which checks a condition, and if an error occurs, the script sends a message to the visitor, informing them of this error. We close the condition and then close the tag.
Outside of the <% ... %> tag, you can write any HTML code you prefer without doing anything special. For example:
<H1>Add to Phone book</H1>
<%
if err.number > 0 then
response.write("An error occured while adding this name")
end if
%>
</BODY>
</HTML>
Only the information within the <% ... %> tag is compiled and executed by the server! If you look at Listing 37.8, we have provided a short VBScript which takes information from the visitor and enters that information into a database. If no information was entered, an HTML document is displayed, in which they can fill out each available form field.
<%
datasource = "PBOOK"
tblNames = "NAMES"
tblAddr = "ADDRESSES"
if request("ACT")="add" and request("OK") <> "" then
on error resume next
sql ="INSERT INTO NAMES (FNAME,LNAME) VALUES (`" + request ("FNAME") + "`,'" + request ("LNAME") + "`)"
set conn = Server.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.RecordSet")
conn.Open datasource
rs.Open sql, conn
if err.number =0 then response.redirect("pbook.asp")
quit
end if
if request("CANCEL") <> "" then
response.redirect("pbook.asp")
end if
%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="InCommand Interactive">
<TITLE>Template</TITLE>
</HEAD>
<BODY>
<H1>Add Job</H1>
<%
if err.number > 0 then
response.write("An error occured while adding this name")
end if
%>
<FORM METHOD="POST" ACTION="addname.asp">
<INPUT TYPE="HIDDEN" NAME="ACT" VALUE="add">
<TABLE>
<TR BGCOLOR="#CCCCCC"><TD ID=fname>First Name #: </TD><TD><INPUT NAME="FNAME" VALUE="<%=request("FNAME")%>"></TD></TR>
<TR><TD ID=fname1 VALIGN=TOP>Last Name: </TD><TD><INPUT NAME="LNAME" VALUE="<%=request("LNAME")%>"></TD></TR>
<TR><TD></TD><TD><INPUT TYPE="SUBMIT" NAME="OK" VALUE="OK"> <INPUT TYPE="SUBMIT" NAME="CANCEL" VALUE="Cancel"></TD></TR>
</TABLE>
</FORM>
<HR>
<A HREF="main.asp">Main Menu</A>
</BODY>
</HTML>
Even though Listing 37.8 is a simple example, you should have a good idea of how you can create your own ASP scripts. If you have ever felt that VBScript is your cup of tea, but were concerned about whether your scripts will run on all web browsers, ASP relieves you of this concern since everything is compiled on the server. The only thing the browser sees is a document full of HTML.
Oracle, the industry's database heavyweight, provides a collection of tools that allow an administrator to easily provide web content using information within an Oracle database. Easily might be a relative term here, since all the lingo Oracle provides makes you feel like you're back in school learning long division. Even so, once the generic terms provided by Oracle are understood, connecting to a database through the Web and displaying the results of a query to those visiting your site should seem like a breeze.
Oracle utilizes what is called a Web Listener. Simply put, a Web Listener is a Web server that recognizes various methods in which the Web Listener may be accessed. To find out whether a request will be for an HTML document or a CGI script, Oracle's Web Listener detects what kind of document the visitor is requesting. The document might be a simple HTML document, or a script used to access the database.
For example, if the Web Listener detects the string owa and is properly placed with the HTTP request, the Web server knows that it's supposed to activate the Oracle Web Agent (often referred to as OWA--more on this in a moment), and process the request using information stored in the Database Connection Descriptor (DCD). The DCD tells the Web Agent both the database access privileges the PL/SQL agent has when executing a request, and the schema used for accessing the database.
As stated, the terminology can be a bit confusing. In order to clear things up a bit, take a look at this example URL: http://www.justanexample.com/owabin/owa/sample_empinfo.
This example URL can be broken into three parts, which are important to the Web Listener.
The first section, http://www.foobar.com/owa-bin, defines the path to the Oracle Web Agent. The owa portion tells the Web Listener that the Oracle Web Agent will be used. The last section, sample_empinfo, contains information on connecting to the database using PL/SQL. This URL can even be used within a HTML form. For example:
<FORM ACTION="http://www.foobar.com/owa-bin/owa/sample_empinfo" METHOD="POST">
PL/SQL is the language for connecting to an Oracle database. PL/SQL is a programming language that contains a superset of the structured query language (SQL) and is used to access the Oracle database. Where SQL is a language to simply query a database, PL/SQL allows you to create functions, use flow control and loops, assign variables, constants, datatypes, and various other statements that help you program applications that submit data to a database, and allows you to format the output of a query.
Another feature of PL/SQL is that you are allowed to store compiled PL/SQL code directly in the database. By doing so, you can call programs you created directly from the database which can be shared by other individuals (even at the same time!), removing the need for having multiple applications that do the same thing.
Unfortunately, PL/SQL is not an industry standard. PL/SQL can currently only be used with Oracle. This creates a problem with portability of code where you might want to have one interface that can be used to access various databases.
If you have installed the PL/SQL Web Toolkit with Oracle, you can use PL/SQL to format the output of queries into HTML format. The toolkit provides a full range of commands that are converted to HTML tags that include information from a query. For example, if you had an employee database which contained the name, ID number of an individual, a phone number, and the e-mail address of each of these employees, the following DCD would provide a HTML document like that in Figure 37.3.
FIG. 37.3
Using Oracle's Web Toolkit enables the administrator to create HTML documents created
based on information in an Oracle database.
The following PL/SQL query provides the visitor with this information.
Create or replace procedure emplist is employees boolean; begin employees := htp.tablePrint(`emp', `BORDER=1'); end;
Complete coverage of Oracle's Web tools would require a book on its own; details on PL/SQL alone would likely fill an entire book. Unfortunately, this book can't go too deeply into every aspect of Oracle's abilities. The information here should whet your appetite, though, and if you have more questions about Oracle, visit the following Web sites. Together with the information contained herein, you should be able to decide if Oracle is the best solution for you.
Thomas Dunbar has taken the time to provide information on how PL/SQL works with the World Wide Web. This page contains a couple of examples that should give you a better understanding of how PL/SQL and Oracle work. You can visit his page at http://gserver.grads. vt.edu/. For more information on PL/SQL itself, see http://www.inf.bme.hu/~gaszi/plsql/plsql_manual.html.
© Copyright, Macmillan Computer Publishing. All rights reserved.