In the fast-paced world of web development, you need quick solutions to tough problems, and when management wants web-based access to the corporate database pronto, you simply don't have the time to start coding from scratch. What you need is an off-the-shelf solution such as Microsoft's FrontPage 97.
FrontPage 97 is a web publishing and development tool suite that features just about everything you need to create and manage web sites and intranets -- including a tool called the Internet Database Connector (IDC). Using the IDC, you can create web-ified front ends to ODBC-compliant databases such as Informix, Sybase, Oracle, and the like. Whether the web-based front end to your database is used to query records, to create tables, or to manage an entire database is up to you.
Before you can determine if FrontPage 97 is the right solution for your intranet, you need to know what platforms the software is available for and on which platforms you can deploy. FrontPage 97 is currently available for Windows 95/NT. There's also a Macintosh version of FrontPage, but FrontPage 1.0 for Mac and FrontPage 1.1 for Windows are entirely different beasts. While FrontPage 1.1 for Windows is severely limited, FrontPage 1.0 for Mac has most of the features of FrontPage 97, including the Internet Database Connector.
Most servers -- database or otherwise -- are UNIX based, which is why you can obtain server extensions that allow you to deploy on Windows 95, NT, and UNIX systems. You install these extensions on your web server. Currently, there are extensions (free from Microsoft at http:// www.microsoft.com/frontpage/wpp.htm) for the servers in Table 1.
The key parts of the FrontPage 97 interface for working with the Internet Database Connector are the Database Connector Wizard and the Database Connector Bot.
The Database Connector Wizard is the front-end tool you use to create hooks for connecting to and working with your databases. You use this wizard to generate a script that can be interpreted by the Database Connector Bot. The script created by the wizard contains query templates that are used with HTML forms.
The Database Connector Bot is the back-end interface that does the actual work. When you install the FrontPage extensions on your web server, this bot is installed on the server and runs automatically when needed.
Using the FrontPage Editor, you can access the Database Connector Wizard from the New Page dialog box; select File | New, then the choose the entry for the Database Connector Wizard. The first thing that appears is the set-up page; see Figure 1. Use this set-up page to specify the location of the database's source file and the URL to the results page.
In the ODBC data source field, enter the full path to the database source file, such as /usr/sybase/qdb.data. In the Query Results template field, enter the URL path to the results page, such as ../queryres.htx. HTX is a Microsoft format for HTML page templates and other data files. When you create a results page using the Database Results template, the page is usually saved in this format with the .HTX extension.
You can also enter a default user name and password. This information is saved unencrypted in the database script. For a secure database, I do not recommend entering a default user name or password.
The Advanced Options button provides access to a dialog box that allows you to fine-tune parameters of the IDC. As Figure 2 shows, you can define how to perform a query, connection settings, limits, and device drivers. For optimum performance, I recommend overriding most of the defaults.
On the Query tab, the two settings you should set are the Cached Results Expiration Time and Query Timeout. Set the caching time based on the capabilities of your web server and volume of database transactions. Base the setting for the query timeout on a time window that incorporates the speed of the network and the urgency of the data request.
On the Connection tab, you will want to consider using most of these options. You should use the read-only access setting if personnel are only allowed to query the database. You may also want to log the ODBC calls, which will give you a clear record of transactions. To optimize performance, you may want to turn on connection pooling and define the network packet size. Finally, set a logon timeout; a good setting usually would be 90 seconds or 120 seconds.
The Limits tab lets you set limits on the size of retrievals. Your database's driver may have specific limits. Configure the limits appropriate for your network and database driver. For optimum performance, limit large retrievals by setting specific IDC limits.
As you can see in Figure 3, the next wizard page lets you enter templates for SQL queries. You build query templates using SQL and parameter assignments. The parameters are replaced dynamically at run time with the data entered in corresponding fields on an HTML form, which is how you will perform database searches and updates. Using a printed list of database descriptors, consider table elements users may want to query, add, delete, or update. For each element, create a parameter. This parameter is associated with an input field on a form.
As Figure 3 illustrates, you may want to build a SELECT statement for a customer database with three parameters: sel_cust, sel_table, and sel_cond. In this way, users can build a query by entering *, customer, and cust=walk-in in the text fields of a fill-out form. The actual query sent to the database looks like Listing One.
After you create the necessary SQL queries, click on the Next button. You can now define default parameter values, such as those in Figure 4. The default values are used when the Internet Database Connector performs a query and are always combined with user input.
When you are done entering default values, click on the Finish button. Before the Database Connector Wizard can create your database script, you must specify a destination folder for the script. Write down the full file path to this script. You will need it for your form handler. Listing Two is a sample script created by the Database Connector Wizard.
After creating the database script, you need to create an HTML form that uses the script. Input fields contained in the form must correspond to parameters defined in the script. When the form is submitted for processing, the IDC replaces the parameter names with the actual values entered in these fields.
If you use the FrontPage Editor to help you create the necessary form, be sure to select the Internet Database Connector as the form handler. Then, name the form input elements using the parameter names you selected for the query script. Matching form names to parameter names is easy. All you have to do is open the properties dialog associated with the input element and enter the parameter name in the Name field. Finally, with the form properties dialog box open, click on the Settings button and enter the URL path to the database script.
Listing Three presents a web page containing a form that can be used to query a database.
The output of database transactions are displayed in a results page. FrontPage 97 has a template for the results page. To use this template, select File | New in the FrontPage Editor, then select Database Results.
Before you add result fields to the page, save the template using the name and URL path you selected when setting up the Internet Database Connector Wizard. You can add result fields to the template using the Database submenu, which is accessed by selecting Edit | Database. Figure 5 shows this menu's options.
Start by creating a detail section to hold the results of the first query. To do this, select Detail Section from the Database menu. You will need to add a detail section for each SQL query your script contains.
After you create the detail section, you need to specify the formatting of the results. Most databases return results as a database record with columns of data cells. Each column in the returned table has a name. To insert the column, the column must be identified by its database descriptor. To do this, select Database Column Value from the Database menu; then enter the column name in the dialog box provided.
If you remember that the general syntax for a Database Column Value is <%column_name%>, you can avoid having to go through the menu and the associated dialog box. To do this, simply copy a previous value and change the markup accordingly. The copy is necessary because FrontPage will otherwise replace the greater-than/less-than symbols with their character entity values: > and <.
To see the search parameters that returned the table record in the results page, use the IDC Parameter Value option of the Database menu. Enter a parameter name in the dialog box provided. Repeat this procedure for each parameter you want on the results page. The IDC will replace the parameter names with the actual value input for the parameter.
With this in mind, you probably want to precede the parameter names with descriptive text, such as "You searched for...". As with column values, you can enter parameter placeholders directly. Use the syntax <%idc.parameter_name%>.
The Internet Database Connector Wizard also lets you perform conditional actions based on the results of the query. These conditional actions are in the form of If-Then and If-Then-Else structures.
Listing Four is an example results page. Examine this HTML source to see how you can create your own results page.
The task of connecting your database to the Web is made easier with FrontPage 97. If you need an off-the-shelf solution for connecting an ODBC-compliant database to the Web, FrontPage 97 is worth a try.
DDJ
use customer_db go SELECT * FROM customer WHERE cust=walk-in go
Datasource: syb_data Template: results.html SQLStatement: use customer_db +go +SELECT %sel_cust% FROM %sel_table% WHERE %sel_cond% +go Expires: 30 Content-Type: text/html MaxFieldSize: 8192 MaxRecords: 120 Username: sybase_proj Password: sybase_proj DefaultParameters: sel_cust=*, sel_table=curr_cust ODBCOptions: SQL_NOSCAN=1, SQL_QUERY_TIMEOUT=15, SQL_TXN_ISOLATION=1, SQL_ACCESS_MODE=1, SQL_OPT_TRACE=1, SQL_OPT_TRACEFILE=custumer_db.log, SQL_LOGIN_TIMEOUT=90, SQL_MAX_LENGTH=8192, SQL_MAX_ROWS=120
<html> <head> <title>Customer Service Web: Queries</title> </head> <body bgcolor="#FFFFFF"> <h2 align=center>Customer Search</h2> <form action="cust_db.idc" method="post"> <p><b>Example:</b> </p> <p>SELECT * FROM cust_table WHERE cust = walk-in </p> <p> <select name="option" size="1"> <option selected>SELECT </option> </select> <br> <input type="text" size="60" name="sel_cust" value="*"> <br> <select name="from" size="1"> <option selected>FROM </option> </select> <br> <input type="text" size="60" name="sel_table" value="cust_table"> <br> <select name="where" size="1"> <option selected>WHERE </option> </select> <br> <input type="text" size="60" name="sel_cond"> </p> <p><input type="submit" value="Submit Query"> </p> </form> </body> </html>
<html>
<head>
<title>Customer Search Results</title>
</head>
<body bgcolor="#FFFFFF">
<h2 align="center">Customer Search Results</h2>
<hr size="5" noshade width="75%">
<p><%begindetail%></p>
<p><%Customer_Name%></p>
<table border="5" cellpadding="5">
<tr>
<td><%Customer_Name%></td>
<td><%Customer_Adress%></td>
<td><%Customer_Phone%></td>
<td><%Customer_AccNum%></td>
<td><%Customer_AccBal%></td>
</tr>
</table>
<p><%enddetail%></p>
<hr size="5" noshade width="75%">
<p>The SQL statement you entered was: </p>
<p>SELECT <%idc.sel_cust%> FROM <%idc.sel_table%>
WHERE <%idc.sel_cond%></p>
</body>
</html>