Dr. Dobb's Journal January 1997
In a relatively short time, the World Wide Web has grown from a static information-delivery system to a full-blown interactive medium with enough potential to make IS shops reconsider their software-development strategies. At the same time, tools are beginning to surface that will marry this massive information system with the information source that drives businesses -- databases.
Companies always have needed to provide customers and employees with current, live corporate data. These databases typically are accessed through proprietary software written for viewing, manipulating, and updating data. Companies also have developed a number of strategies for distributing information to clients, including dial-up access to internal systems and proprietary applications with monthly data updates. But given the vast array of PC architectures and operating systems, these solutions can create costly software-development and technical-support nightmares.
Enter the Internet. Third-party and database companies are rushing to develop products that allow data access through the Web. Any user with an Internet-capable computer can use tools to access data through a web-based dynamic data application. A web browser serves as the only software a client computer needs to access and manipulate data. The web server provides the data-access and manipulation tools.
Traditionally, a CGI script or program was used to allow the web server to access data in a database. As database-driven applications are becoming more popular, developers are searching for easier and more efficient ways to access data.
Allaire's Cold Fusion is a development tool that can be used with Windows 95 or Windows NT web servers to provide access via the Web to any database the web server can access using ODBC. It runs as a multithreaded Windows NT system service and works with any ODBC-compliant database. Cold Fusion uses SQL to retrieve and manipulate the data, including adding tables to a database, adding records to a table, and updating multiple rows in a table using an action query.
A Cold Fusion web application uses template files instead of static HTML pages. When users request a Cold Fusion template file, the template is processed by Cold Fusion, which generates a standard HTML page that is sent back to the user's browser; see Figure 1.
A Cold Fusion template file is an ASCII text file that looks a lot like an HTML file at first glance. You can use any standard HTML tag in a Cold Fusion template that you would use in a static HTML web page. As the HTML standard develops, you can incorporate new HTML tags in your templates. As long as the browser can interpret the HTML tags, you don't need to worry about compatibility issues between Cold Fusion and HTML.
In addition to the HTML tags and standard text you want the template to generate, you must also include Database Markup Language (DBML) tags to tell Cold Fusion what information the user has requested and what to do with the result set. Cold Fusion uses the SQL statement you include in a DBQUERY DBML tag to connect to the database using ODBC. It queries the database, then generates the dynamic web page using the HTML tags in your template and the results of the query.
To illustrate how you can use Cold Fusion, I developed an online ordering system that allows users to shop from a catalog, placing orders when they have filled their shopping carts. The products displayed to users through the Web come from the same database we use to maintain product and price lists, thus the information is up-to-date and accurate, 24 hours a day, 7 days a week. This type of application frequently is referred to as a "shopping cart" application by web developers. For an alternative approach to web shopping carts, see "Implementing a Web Shopping Cart," by Chris Baron and Bob Weil (DDJ, September 1996).
The Online Ordering System contains three major components:
At the heart of the Cold Fusion system is the Cold Fusion Administrator. It resides on the web server and allows you to manage and maintain ODBC data sources, links from the web server to ODBC-compliant databases. Without an entry in the Cold Fusion administrator, a database is not accessible by the web server. For the online catalog, I created a data source named "calico" to access my product database. Because the product database is a Microsoft Access 7.0 database, I specified the Access 7.0 ODBC driver as the connection source.
You also use the Cold Fusion Administrator to map physical directories to logical template file paths, configure the debugging output and error logging behavior of Cold Fusion, and configure the caching of the DBML.EXE script and ODBC libraries. All of these settings are created by default when you install Cold Fusion. You can edit them if you require a setting other than the installation default.
DBML.EXE is the Cold Fusion stub program (14 KB) that passes relevant CGI data from the web server to the Cold Fusion NT service, then receives the dynamic page back from the service and passes it back to the web server to display. The two most-basic tags are those you use to do the bulk of the work:
DBML tags, like HTML tags, may have attributes you can set to define the behavior of the query. Two DBQUERY attributes are optional -- the maxrows parameter defines the maximum number of rows you want Cold Fusion to return, and the debug parameter instructs Cold Fusion to return the data source name and number of records with the result set. The three mandatory attributes for DBQUERY are name, datasource, and SQL. You use the name of the query after the query is performed if you want to display the results to the user. The datasource parameter links the query to the data source you defined in the Cold Fusion Administrator. The SQL parameter defines the SQL statement you want to execute against the data source.
When a user accesses our site, the database generates a unique shopper ID and assigns it to that user. The ShopperID field in the Access database is an AutoNumber field that is generated when Cold Fusion performs an Insert into the table using the SQL statement: INSERT INTO hns_orders (customerfirstname) VALUES ('Temporary Shopper'). I accomplish this by redirecting the default URL, http:// www.calico-company.com/hotnstuf/, to a Cold Fusion template file using the URL http://www.calico-company.com/cgi-shl/dbml.exe?Template=hnsdefault.dbm. The template, hnsdefault.dbm, contains a DBQUERY tag instructing Cold Fusion to insert a new row into the hns_orders table in the Calico datasource: <DBQuery Name="InsertShopper" DataSource="Calico" SQL="INSERT INTO hns_orders (customerfirstname) VALUES('Temporary Shopper')">. (The complete Cold Fusion template is available electronically; see "Availability," page 3.)
Cold Fusion executes the INSERT statement as soon as it processes the template, then it retrieves the ShopperID number from the table by executing Max(shopperID) immediately after it adds the new row: <DBQuery Name="FindMax" DataSource="Calico" SQL="SELECT Max(shoppingid) as shopid FROM hns_orders">. Now I can use shopid to uniquely identify the shopper and to display it to the user with the DBOUTPUT tag: You are shopper <dboutput query="FindMax"> #shopid# </dboutput>. (See Figure 2.)
Cold Fusion uses the pound sign to indicate the name of a database field within dboutput tags, parameters passed with a URL, or form fields passed when users click a Submit button on a form. In this example, #shopid# represents the database field generated by the DBQUERY named "FindMax."
If the shopper decides to buy an item, I can uniquely identify the purchases by the shopper ID. To do this, I must pass the shopper ID to each template while the shopper is browsing our product database. Cold Fusion allows you to pass parameters to DMBL.EXE as part of the URL. For instance, to pass the shopper ID to a template, called hnsprods.dbm, that lists all of our products from a particular manufacturer. Within the referring template file, I use the URL http:// www .calico-company .com/cgi-shl/ dbml .exe? Template=hnsprods.dbm&shopid=<dboutput query="FindMax">#shopid#</dboutput>. I can add as many parameters as I want by concatenating each parameter to the list with an ampersand (&). To specify the category ID of the products I want to list within the referring template file, I use the URL http://www. calico-company.com/ cgi-shl/dbml.exe? Template=/hnsprods .dbm&shopid= <dboutput query= "FindMax">#shopid# </dboutput>&catid=1.
I have four categories of products in my database, each identified by a unique category ID. When I pass the category ID to the template that handles the product listing, it uses the information to generate a product listing: <DBQuery Name="ListProducts" DataSource="calico" SQL= "SELECT * FROM hns_products WHERE productcategory = #catid# ORDER BY producttype, productname">.
When I display the results of the query, I use a DBOUTPUT tag (see Listing One with the Group parameter to group the products by product type. Because I want the shopper to be able to purchase a product directly from the list, I put the results inside a form. The Action parameter of the <form> HTML tag specifies the URL to call when users press the Submit button at the bottom of the order form.
As this example demonstrates, you can nest DBOUTPUT statements to customize your data output. In this case, the first DBOUTPUT tag specifies the group and the second DBOUTPUT statement lists all the products that belong to the group. The second DBOUTPUT tag iterates through the query-result set until it gets to the last product. As in HTML, you must terminate some DBML tags: <DBOUTPUT> requires a terminating </DBOUTPUT>.
When using a form to allow users to enter information, you can pass information to a Cold Fusion template on the URL line or as a hidden-field type. In the hnsprods.dbm file, I use hidden fields to specify the shopper ID (shopid) and product ID (prodid). Cold Fusion also uses hidden-field types to validate data and specify error messages to users. For instance, the hidden fields in Listing Two tell Cold Fusion that users must enter a value into the field named "quantity" and the value in the quantity field must be a number. If users don't enter a value or they enter a value that isn't a number, they receive the error message specified in the hidden field's value parameter; in this case, either "You must enter a quantity" or "The quantity must be a number."
When users submit the order form, the hnsorder.dbm template processes the order, totals all the items in the shopping cart, then gives instructions to place the final order or to continue to shop. SQL statements inside DBQUERY tags perform all the database manipulation and calculation. DBOUTPUT tags display the results; see Listing Three.
With the DBIF and DBELSE tags, I can do conditional processing and formatting of result sets. In this case, if the MfgId field of the database is blank, the template instructs Cold Fusion not to display the field: <dbif #MfgID# is not "">(#MfgID#)</dbif>.
Using the same tags, I can restrict submitted orders to those with a total cost of $15 or more. If a user has only $13 worth of merchandise in a shopping cart, he must continue shopping; otherwise, he can fill out the purchase information to submit his final order; see Listing Four.
If the user decides to place the final order, he enters his shipping and credit-card information into a form, then submits the information. The user's shipping, order, and credit-card information are now in my database. In addition, my Cold Fusion template instructs Cold Fusion to generate an e-mail message to my sales department listing the information needed to fill the order. When the message is received, the salesperson calls to confirm the order and processes the shipment.
The DBMAIL tag tells Cold Fusion to generate an e-mail message and send it to a specified recipient or list of recipients. The DBMAIL tag can take four parameters:
From -- the e-mail address to be listed on the e-mail's from line.
To -- the e-mail address to whom the message will be sent.
Query -- the query name to be used in any output in the message.
Subject -- the subject text to be listed on the e-mail's subject line.
In my application, I need to send only one e-mail message to a single recipient; see Listing Five. I just as easily could have a table in my database with e-mail addresses of all the sales people in a department. Using this table and a DBQUERY to retrieve all the e-mail addresses, I could send a message to each e-mail address in the table by adding the QUERY parameter to the DBMAIL tag, using the data result set in the TO line of the message in Listing Six.
Tools such as Cold Fusion that link databases to the Web are surfacing as developers and users begin to grasp the power and potential of the Web. This new technology is in its infancy and each new wave of products will bring innovative and better ways to exploit this new medium. It is only a matter of time before databases have built-in data types for video and audio. Already, client-side tools such as Java and ActiveX give power to the client beyond simple "browsing." The Internet has a massive structure for information exchange. It is now up to developers and vendors to build on existing principles of client/server development to create the applications and the tools to deliver the information in an efficient, reliable, and meaningful manner.
DDJ
<DBOUTPUT Query="ListProducts" Group="ProductType"><center><HR></center> <H2>#ListProducts.ProductType#</H2> <DBOUTPUT> <FORM ACTION="http://www.calico-company.com/cgi-shl/dbml.exe? Template=hnsorder.dbm" METHOD=Post> <input type=hidden name=shopid value=#shopid#> <input type=hidden name=prodid value=#productid#> <input type=hidden name=quantity_required value="You must enter a quantity."> <input type=hidden name=quantity_number value="The quantity must be a number."> <center><HR width=500></center> <P><STRONG><Font Size=+1>#ProductName#</font></STRONG> <dbif #MfgID# is not "">(#MfgID#)</dbif> <br><STRONG>Price:</STRONG> #DollarFormat(PriceEach)# each (#PerCase# per case) <br>Quantity: <input type=text name=quantity value="1" size=3> (this must be a number) <p><input type=submit value=" Order Now "> </form> <EM>#ProductDescription#</EM> </DBOUTPUT> </DBOUTPUT>
<input type=hidden name=quantity_required value="You must enter a quantity."><input type=hidden name=quantity_number value="The quantity must be a number.">
<DBQUERY Name="InsertItem" DataSource="Calico" SQL="INSERT INTO hns_orderdetail(Shoppingid, ProductID, ProductQuantity) VALUES(#form.shopid#, #form.prodid#,#form.quantity#)"> <DBQUERY NAME="FindOrders" DataSource="Calico" SQL="SELECT hns_products.ProductName, hns_orderdetail.ProductQuantity, hns_products.MfgID FROM (hns_orderdetail INNER JOIN hns_orders ON hns_orderdetail.ShoppingID = hns_orders.shoppingId) INNER JOIN hns_produ cts ON hns_orderdetail.ProductID = hns_products.ProductID WHERE (((hns_orderdetail.ShoppingID)=#form.shopid#))"> <DBQuery NAME="TotalOrder" DataSource="Calico" SQL="SELECT Sum(hns_products.PriceEach) AS TotalCost FROM (hns_orderdetail INNER JOIN hns_orders ON hns_orderdetail.ShoppingID = hns_orders.shoppingId) INNER JOIN hns_products ON hns_orderdetail.ProductID = hn s_products.ProductID WHERE hns_orderdetail.ShoppingID=#form.shopid#"> <DBQuery NAME="TotalCost" DataSource="Calico" SQL="SELECT Sum([ProductQuantity]*[PriceEach]) AS RealCost FROM (hns_orderdetail INNER JOIN hns_orders ON hns_orderdetail.ShoppingID = hns_orders.shoppingId) INNER JOIN hns_products ON hns_orderdetail.ProductID = hns_products.ProductID WHERE hns_orderdetai l.shoppingid=#shopid#"> <DBOUTPUT QUERY="FindOrders"> <li><strong>#ProductQuantity# #ProductName#</strong> <dbif #MfgID# is not "">(#MfgID#)</dbif> </DBOUTPUT> </ul> <DBOUTPUT QUERY="TotalCost"> <p><strong>TOTAL : </strong>#DollarFormat(RealCost)# </DBOUTPUT>
<DBIF #RealCost# less than 15><P><strong>The minimum order we can mail is $15.00. You have not reached that amount yet. <P>To continue shopping, press the BACK button on your browser to get back to the product listing you were viewing.</strong> </dbelse> <P>To finalize your order and submit your order form with the items listed, fill out the information below.</strong> </dbif>
<DBMAIL QUERY="FindOrders" FROM="calico@supernet.net" TO="calico@supernet.net" SUBJECT="Hot N Stuf Order Form">
<DBQUERY NAME="FindEmail" DataSource="calico" SQL="SELECT email FROM recipients"> <DBMAIL QUERY="FindEmail" FROM="calico@supernet.net" TO="#email#" SUBJECT="Hot N Stuf Order Form"> Here is the information for a new order placed through the Online Catalog. </DBMAIL>DDJ
Copyright © 1997, Dr. Dobb's Journal