A Sip From The Firehose: Tuesday, March 31, 1998

Abstract: C++Builder 3 - A Customer and Orders Master Detail Web Server Application

Tuesday, March 31, 1998
Scotts Valley, CA

C++Builder 3 - A Customer and Orders Master Detail Web Server Application

In the previous SipFH, I showed you how to build a web server application using an HTML form to input survey information into an InterBase database. Due to the popular demand for more web server example applications for C++Builder, I offer up another example that uses the PageProducer component's custom HTML tag feature, the QueryTableProducer component, and SQL query components. C++Builder and Delphi can both be used to build your choice of CGI (Common Gateway Interface), WinCGI (Windows CGI), ISAPI (Microsoft Information Server API) and NSAPI (Netscape Server API) web server applications.

The Customer List PageProducer

For this example I will use the example database that comes with InterBase, the Employee.gdb database. My web server application will use the Customer table to display a list of customer names. I use the PageProducer component to create the HTML for the list of customers. The PageProducer HTMLDoc property contains the following text.

Customer and Order Web Application

Customer List

click a customer name to see their orders

<#custlist>

Notice that the HTML above contains a user defined html tag, <#custlist>. When the page producer sees a user defined tag, it calls the PageProducer's OnHTMLTag event handler. The tag is passed as an AnsiString to the event handler. Inside your event handler you can write whatever logic you want to handle each custom tag. A return parameter, ReplaceText, is defined for you to return HTML text that the PageProducer component will use as a substitute for the custom tag. My OnHTMLTag event handler is included below.

if (TagString == "custlist") {
  ReplaceText = "";
  // position the cursor at the first row in the database
  CustQuery->First();
  // go through the entire customer table generating href's
  while (!CustQuery->Eof) {
    ReplaceText = ReplaceText
      + "<a href="http://ww5.borland.com"
      + "/scripts/orders.exe/orders?cust_no="
      + CustQueryCUST_NO->AsString
      + "">"
      + CustQueryCUSTOMER->AsString +"</a><br>";
    // go to the next customer row
    CustQuery->Next();
  }
}

The OnHTMLTag event handler generates a separate line for each customer using a hypertext reference tag (<a href>) that refers back into the web server application using the /orders pathinfo string with a cust_no=nnnn name=value pair as a parameter to be used for a parameterized SQL query to find the orders for the selected customer.

The Customer Orders QueryTableProducer

When you click on a customer name in your browser, the web server application is executed using the pathinfo string /orders. The web server application uses the web modules action items list to get to the right OnAction event handler. The OnAction event handler takes the customer number from the name=value parameter pair and uses it to fire the OrderQuery parameterized SQL query to select the orders for the selected customer. The OrderQuery also uses a lookup field to get the customer name from the Customer table. The customer name is stored in the OrderQueryProducer's Header property. The OnAction event handler finally uses a QueryTableProducer component to display the orders as an HTML table. The code for the OnAction event handler is shown below.

OrderQuery->Close();
// get customer number from the content field
// store customer number into the params value
OrderQuery->ParamByName("cust_no")->Value =
Request->QueryFields->Values["cust_no"];
// open the query to fire the parameterized SQL statement
OrderQuery->Open();
OrderQueryProducer->Header->Clear();
OrderQueryProducer->Header->
Add("<h2>Orders for "+OrderQueryCustName->AsString+"</h2>");
// use QueryTable producer to return orders
Response->Content = OrderQueryProducer->Content();

Check Out the Customer and Orders Web Application

You can try the web application for your self, click here. The source code for the web server application is available in a zip file.

Steps to Build the Customer and Orders Web Server Application

This section lists the steps required to build and test the Customer and Orders web server executable. You can use any web server that supports ISAPI and runs under Windows 95 or Windows NT.

Step 1 - use the InterBase example database, EMPLOYEE.GDB

When you install C++Builder 3 Client/Server Suite, the EMPLOYEE.GDB example InterBase database is copied to the Borland SharedData folder. An alias is created, IBLOCAL, that points to that database. I use the IBLOCAL alias in the web server application.

Step 2 - Start C++Builder3 and Create a Web Server Application

Start up C++Builder 3. From the main menu chose File|New. Select the Web Server Application from the object repository. This will bring up the Web Server Application wizard. Chose the radio button for ISAPI/NSAPI to create the executable project and the main application source file and the Web Module.

Step 3 - Add a Database component to the Web Module

From the Data Access component palette drop a Database component into the Web Module. This will allow you to connect to the InterBase database, login to the database and stay connected between sessions. Set the AliasName property to the IBLOCAL alias. Set the DatabaseName property to any text string. Set the Params property to the following strings:

  USER NAME=SYSDBA
  PASSWORD=masterkey

Set the LoginPrompt property to false (the Params property will contain the needed username and password). Set the HandleShared property to True. Set the Connected property to true to turn on the connection to the InterBase database.

Step 4 - Add a Query component to the Web Module

From the Data Access component palette drop a Query component into the Web Module. Set the Name property to CustQuery. Set the DatabaseName property to the string you set in the Database component. Set the SQL property to Select * from Customer. Set the Active property to True.

Step 5 - Add a PageProducer component to the Web Module

The first page producer will be used to generate the list of customer hypertext references. Set the Name property to CustListPageProducer. Set the HTMLDoc property to the HTML strings listed above in The Customer List PageProducer section.

Step 6 - Add another Query component to the Web Module

From the Data Access component palette drop another Query component into the Web Module. Set the Name property to OrderQuery. Set the DatabaseName property to the string you set in the Database component. Set the SQL property to Select * from Sales where Cust_No = :Cust_No. Double click on the Params property to bring up the Query Parameters property editor. Set the Data type to Integer.

Step 7 - Add a QueryTableProducer component to the Web Module

The QueryTableProducer is used to generate an HTML table from a SQL query. Set the Query property to the OrderQuery. Right mouse click on the QueryTableProducer component to bring up the Response Editor. The response editor allows you to visually design your HTML table. Click the Add All Fields button to add all the columns of the Sales table to the list. Delete any columns that you do not want to show. You can also click on each column name and use the Object Inspector to set any properties for that column. I set the Title-Caption subproperty for each column to have the HTML table display a better column heading instead of the actual query column name.

Step 8 - Bring up the Web Module Action Editor

Right mouse click in the Web Module window. Select the Action Editor menu item from the pop-up menu.

Step 9 - Add the default action item

In the Action Editor click the Add button. This will create a web action in the Web Module Actions array. In the Inspector, set the Name property to DefaultAction. Set the Default property true - this will mean that any execution of this web server application will use this actions event handler for processing unless a pathinfo string is found to match a user request (see the next step). Switch to the events tab in the inspector. Double click in the OnAction event to create an event handler. Inside the event handler code put the following lines of code that will cause the Page Producer to create the customer list. The code is listed above in the Customer List PageProducer section.

Step 10 - Add the Orders Table Action Item

Go back to the Editing WebModule->Actions window to create another action handler. This action handler will be used to accept the request for a specific customer's orders to be retrieved. In the Editing Actions window, click the Add button. In the Inspector, set the Name property to OrdersAction. Set the PathInfo property (also called the URI in web circles) to the string /orders. In the events tab of the Inspector, double click in the OnAction event to create an event handler for this web action. When the user clicks on a customer name, the web server application will be re-called with the selected customer number in cust_no name=value pair. The executable will see this PathInfo string and know that it is to fire the action event handler that will generate the orders HTML table as a response. The code for the event handler is shown above in the Customer Orders QueryTableProducer section. An example of a generated customer orders HTML table looks like the following. Orders for Signature Design

PO	Rep	Order Date	Ship Date	Quantity	Item	Total Value
V9324200	72	8/9/93	8/9/93	1000	hardware	560000
V9324320	127	8/16/93	8/16/93	1	software	0
V9320630	127	12/12/93		3	hardware	60000
V9420099	127	1/17/94		100	software	3399
V9427029	127	2/7/94	2/10/94	17	hardware	422210

Step 11 - Building the Web Server Application ISAPI DLL

While in the environment, save the project and do a build of the web server application. This will create the .DLL file in your project folder. Copy the generated DLL to your web server applications folder, usually the SCRIPTS folder (or wherever you put your web applications).

Step 12 - Use your browser to try the Web Server application

Using your web browser type in the URL to your web server application DLL. You will key in something like http://ww5.borland.com/scripts/orders.dll. This will cause your web server to load the executable and display the list of customers as hypertext references. Click one of the customer names to re-execute the web server application to generate the HTML table of orders.

Watch Out for April Fools Day!!!

There's no fooling around going on here. You can build real world web server applications using C++Builder 3 Client/Server Suite. I hope this second example will help you get going.

davidi@inprise.com