Is all that glitters gold? Maybe! Database connectivity to your World Wide Web server is an idea that shines, but you can run into difficulties if you intend to use the available advanced options, especially if you aren't fluent in database programming. But don't worry. You can create simple online access to information-and more complex data-interactivity-with a little effort and perhaps some help from your database administrator.
When do you need database connectivity? If you have information to distribute to sources such as clients or prospective customers outside your company, connecting your corporate database to the Web can be a golden solution. For example, if you want to let customers search through and order from your catalogue of 10,000 electronics parts, using a database to automate the process is far easier than designing 10,000 custom pages. Besides, in Hypertext Markup Language (HTML) or Common Gateway Interface (CGI) programming, manually creating that many pages is daunting, to say the least. You also want to let your customers insert their own information into your database.
Database connectivity opens access to data across the Web with a minimum of programming. By standard methods, if you want to present information besides buttons and pointers on a page, you must manually create the page and tell the Web server where to put the information, which information to use, and how you want the information displayed. Even automated word-processor conversion programs can be a pain for lengthy and complicated documents, and if you want custom designs for things such as catalogs, you have to program every line. Database connectivity packages eliminate tedious programming by automating HTML page generation and dynamically creating pages according to the data being viewed. What's more, the same techniques of data entry and retrieval will let you support online conferencing and automate activity tracking on your site. As a result, you can see who's doing what and where.
Two of the leading database-access tools on the Web are dbWeb from Aspect Software Engineering and Cold Fusion from Allaire. We won't do a head-to-head comparison of the two here, although both more or less do the same thing, because they are fundamentally different programs. dbWeb is ideally suited for automated data display and retrieval, and Cold Fusion is best as a tool for data entry, custom display programming, and advanced data retrieval.
You can easily go crazy with all the advanced features available in both Cold Fusion and dbWeb, so don't overdo it. We've seen several examples on the Web where going feature-crazy drags down system performance tremendously, so please exhibit some self-restraint when using these packages.
dbWeb 1.0
dbWeb supports HyperText Transfer Protocol (HTTP) servers,
including Alibaba, EMWAC HTTPS, Netscape Communications Server,
Process Software's Purveyor, O'Reilly's Website, and others conforming
to HTML standards. It also supports several databases, including
Microsoft SQL Server 4.2, Sybase, Oracle, Microsoft Access, and
others supporting 32-bit Open Database Connectivity (ODBC).
dbWeb installation and setup is straightforward: You can load it from floppy or from an archive file that you can find at Aspect's Web site. The key that Aspect gives you will let you unlock dbWeb with either a standard license or for a 30-day trial period. The program prompts you for information about your Web server and automatically loads Microsoft's ODBC Setup utility (32-bit, version 2.5). You must configure dbWeb yourself according to your database needs by installing the proper drivers. You also have a dbWeb Service that is set to manual by default, but you'll probably want to change it to automatic to save time if you have to reboot your system.
Configuration
Once you have dbWeb up and running, you can launch the dbWeb Administrator.
It will greet you with
one of the package's best features: its clean and easy-to-use
graphical interface, which you can see in screen 1. This program's
forte is data presentation, which makes it an excellent Web data
visualization tool. With it, you can make your data available
on the Web without any CGI or HTML programming in hardly any time
at all. If you want to customize your site, setting up your data
will take a little longer. In that case, you'll need to design
your own, more static HTML pages.
Basically, you tell dbWeb where the data is by defining an ODBC data source and an automated wizard steps you through the setup routine. You can use predefined setups for viewing data, called schemas, that tell dbWeb how to organize and present information in HTML pages, or you can design your own setups with the schema editor. Most of dbWeb's magic occurs when it generates custom HTML pages on the fly.
Schemas are the gateways between the user and the data. With them, you tell dbWeb the name of your data source and which columns of your data you want to display. You can select whether to display your data as a tabular listing, free form, in a single record, or in some other fashion. You can also select what relationships you'll use. A relationship, or Drilldown SmartLink as dbWeb calls it, lets you be more specific about the data you retrieve and drill down from general topics to exact information.
For example, if you start with a general topic about all the consulting firms in Colorado, you can drill down to those with more than 100 people, then down again to the managers at those firms. All this data can be in your central database, or you can actually drill across to other database files, other schemas, or other servers on your network, or even across the Web to a specific Universal Resource Locator (URL).
Once you format the schema, you're ready to begin querying your data. When you access your database home page, dbWeb will intervene and present the screen that you designed in the schema. You can enter search criteria and dbWeb will return a custom page with the information you requested and links to more specific data. All this occurs without any CGI or HTML programming. You can navigate the pages as if they had been designed for each entry, even though they're completely dynamic.
Features
The Schema Editor
allows you a great deal of latitude in presenting and entering
data, but recognize that in dbWeb this flexibility presents a
security risk. If you let some users insert, update, and delete
information from the database, your data is unfortunately open
to anyone who wants to access it. To ensure some security, you
can disable data actions and allow only queries, but then you
lose a level of interactivity. Another solution is to set up password
protection for data entry with a custom HTML page. This action
separates data entry from retrieval, so you can maintain some
security.
dbWeb supports most basic query operations available in standard databases, including Query by Example (QBE), joins, tables (for multiple tables within a single data source), and computed data (deriving new columns from existing data). You can enhance your data presentation with special fonts, layouts, and graphics, and multi-record forms by designing a custom Database Format File (DBX), which lets you further alter the look and feel of your QBE entries and results (e.g., tables and free form and tabular listings).
Looking Good
dbWeb is an outstanding visualization
tool. It lets you put your database online quickly with a decent-looking
interface, minimum hassle, and no programming. The documentation
is excellent-you can find it online in Microsoft Word format-and
the program provides enough sample files to show you its features.
We had some problems with initial setup and with loading our own
data, but Aspect's technical-support people stepped us through.
The program does, however, have room for improvement. Some data
security features and multimedia handlers would be nice so that
you could present graphics and other data with your text, including
pictures in catalogs or pointers to downloadable files. You could
custom-code such features into your pages, but that action would
defeat the purpose of this nicely interactive and automated program.
This package is great if you hope to increase your productivity
over the Web by making information available to clients and customers,
especially if you don't want to get bogged down by heavy-duty
CGI and HTML programming.
Cold FusionPro 1.5 Beta 4
Cold Fusion
Pro Beta 4.0 (CFP) installs like most other programs these days,
using the standard interface that the Install-It and Install-Shield
programs made popular. If you are not familiar with these, they
simply lead you through a series of straightforward questions
so that you can supply directory information and other necessary
configuration parameters.
The installation tries to detect which HTTP server(s) you have installed. We installed CFP on Netscape Commerce Server 1.1, Netscape Communications Server 1.1, and Microsoft Internet Server Beta 2.
On the Netscape installations, CFP failed to recognize the server, and we had to supply the information manually. CFP recognized the Microsoft Internet Server without a problem, and the Internet Server configuration supplied the appropriate information.
Configuration
Once you install the software,
you need to configure the ODBC drivers. CFP gives you the latest
32-bit ODBC drivers (2.5 as of release Beta 4.0). If you have
a previous version of CFP, all your published ODBC datasources
are updated to System datasources. This lets you have a single
data source name (DSN) to work with all users of a given machine,
rather than having each user set up his or her own ODBC datasources.
CFP lets you test your ODBC configuration before
you exit the configuration program (a nice touch!).
CFP supports a variety of HTTP servers, including O'Reilly Website 1.0b, EMWAC HTTPS 0.96, Process Software's Purveyor 1.0, Netscape Communications Server 1.1, and Microsoft Internet Server. As for databases, CFP supports MS SQL Server 6.0, MS Access, MS FoxPro, Borland Paradox, Oracle, Dbase, and other 32-bit ODBC-compliant DBMSs that comply with Level 1 of the ODBC API and support core SQL grammar.
Features
When
you have the ODBC driver and CFP configured, it's time to serve
some data. CFP doesn't provide graphical data layout tools as
dbWeb does. Instead CFP focuses on reliability and control. If
you are familiar with CGI or have written a script or two, you
know that managing all the scripts and the way they interact can
be more trouble than they're worth. CFP recognizes this and offers
one serious, industrial strength, turbo-charged, longhooded CGI
script that is suited to a variety of data input/retrieval tasks.
By having one script service all the requests, CFP approaches
a level of reliability that can only be described as bulletproof.
Full SQL Server logs or some other configuration problem generally
causes the occasional errors you find. We have tested CFP for
months and are currently using it live on our Web site.
We have had few problems that we can directly attribute to CFP.
That said, CFP is not perfect. We did encounter some problems with the Netscape Communications Server configuration on a particularly busy system. The machine exhibited slow-downs that we traced to a named pipes problem. However, the tech support at Allaire was superior: They installed several tracking programs to determine the exact problem with Netscape. When we wrote this article, they were still working to solve the problem.
Also, in all that test time, CFP never crashed on us. It also ran well on a machine that was not nearly as busy, and we currently have CFP running on Microsoft Internet Server with high volume and no problems.
Another minor difficulty with CFP is that if you are not familiar with SQL syntax, you face a steep learning curve. If, on the other hand, you understand select statements and groupby statements, you'll appreciate CFP's power. You have complete control over the what, where, and why of data display on your Web site. If all you want is to collect information, understanding SQL is helpful but not necessary.
Data Input and Output
CFP uses
standard HTML forms to insert and update records in a database
table. The supplied validation rules are extensive, so you can
be in control of how your forms are filled out. You can require
an entry to a given field to be a float, integer, date, or in
a particular numeric range. You can also timestamp input records
and log Internet Protocol (IP) addresses, client names, and even
the browser type that people use to access your Web site, all
without writing a scrap of code.
When it comes to displaying data, CFP is in a class by itself. If letting your clients simply query your database and display the results is all you want, CFP can do it with ease.
However, this capability is only a little of what you can do. How about conditional branching on the fly for customizing data sent to the user? How about queries designed to dynamically create custom menus or fill list boxes and document lists? (For an example of list boxes and document lists, go to DropZone.) Thanks to CFP's complete control over output formatting, you can do such things as create a list of hot links to other Web sites based on a query result. We can't say enough about this flexibility: Every time we think of something new to try with CFP, it easily accommodates us.
But wait-there's more! CFP supports stored procedures and lets you easily pass data to them. You can also enable the debugger, which displays more information than most people care to know.
The interesting aspect is that the information appears with the query results on the subsequent Web page. All the information you need is on the same screen. Pretty slick, right?
Support Lines
Allaire provides
excellent support for Cold Fusion Pro. If you are on the Web (and
if you aren't, you're probably thinking about it), Allaire provides
a terrific Web site devoted to CFP. Documents, discussion groups,
and copious amounts of how-to's and working examples populate
their site. If that's not enough, Allaire also has excellent telephone
support and a complete set of examples in the CFP distribution.
In addition, the manual gives step-by-step instructions for a
variety of applications.
From data collection to data display, CFP does it all. If you have visited our Web site, you'll notice that we have an automated subscription form, a searchable NT-specific Web site list, a searchable NT specific software listing, and a searchable NT-related press-release database, just to name a few spots. All these are done with CFP and stored in a variety of SQL server databases. We are now free to query the data with any ODBC-compliant client (e.g., VFP,VB 4.0, Access) or serve the information on the Internet either via the Web, email, or both.
Testing Platforms
Database servers: (Microsoft SQL Server 6.0)
NEC RISCServer 2200 with 64MB of RAM and a 2GB SCSI-2 disk
Telos Pentium-133 with 64MB of RAM and a 2GB SCSI-2 disk.
HTTP servers:
Telos 486/66 with 32MB of RAM running Netscape Communications Server version 1.1
Telos Pentium-120 with 64MB of RAM and a 1.3GB SCSI-2 disk running Microsoft Internet Server
Telos Pentium-133 with 64MB of RAM and a 2GB SCSI-2 disk running Netscape Commerce Server version 1.1
