William is the executive director of Global Internet Solutions and the author of several books, including Web Publishing Unleashed, FrontPage 97 Unleashed, Peter Norton's Guide to Java Programming, and Netscape ONE Developer's Guide. He can be contacted at [email protected].
LiveWire is a powerful tool for building web-to-database solutions. Whether you want to create a query engine or a complete database management system, you can rely on LiveWire to help you get the job done. The key to LiveWire is that you use server-side JavaScript and WYSIWYG development tools to create web-based applications. Although these applications can run in any JavaScript-compliant browser, they depend on a Netscape SuiteSpot-compliant web server to prepare the data. Additionally, your database server must be ODBC compliant.
If you read my previous column, by now you have either joined the LiveWire bandwagon or are still sitting on the sidelines contemplating whether LiveWire is the web-to-database solution you are looking for. Either way, you probably want to see what LiveWire is made of and if you can really create advanced applications with it. Enter the dynamic query engine, which I'll explore in this column.
Creating the Query Engine
When your LiveWire application sends commands to a database server, those commands are generally in the form of SQL statements. Traditionally, the SQL command used to query databases is the SELECT statement. Using LiveWire and HTML form elements, you can create a friendly interface (see Figure 1) that builds SELECT statements.
As you examine the figure, note that the interface takes advantage of the general syntax for SELECT statements, which is SELECT column(s) FROM table WHERE conditions_exist. Listing One is the source code for the main page of the query engine. As you examine this listing, note the three input fields that let users build the SELECT statement. When you submit the HTML form containing these fields, a request object property that corresponds to each named field in the form is created; your browser then loads the page called "rorig.htm."
Building the database query, passing the query to the server, and formatting the returned data are all handled in the search results page (rorig.htm). The first step is to check the database connection and redirect the browser to a connection page if there is a problem (see Example 1).
After connecting to the database and verifying the connection, you can build the query. Because you generally want the query to be persistent over multiple requests, you create the query using a property of the client object.
The final step is to format the data. Ideally, you want to ensure that the data retrieved from the server is presented in a sensible way. The easiest way to do this is to use the database.SQLTable() method, which automatically formats the data retrieval into an HTML table; see Example 2. Listing Two shows how Examples 1 and 2 come together in the finished page. Note that the project object is locked before you try to connect to the database. Locking the project object ensures that other clients cannot read or set project properties until you have finished connecting to the database and made the query.
Moving to Dynamic Queries
Although the query engine just presented works, it is neither dynamic nor intelligent. Let's say you are designing your query engine for a customer service department where users always work with two tables, called "customer" and "employee," in a particular database. The customer table is used to track information pertaining to customer accounts. The employee table is used to track customer-service representatives assigned to customer accounts, as well as other employees throughout the organization. Wouldn't it be great if you could customize the query engine for these tables? Well, you can. To do this, you will need to create scripts that can update the query page on the fly.
Before getting started, you need to sit down with the customer service folks to see how they currently use the company database. For this scenario, let's say the user generally performs two types of queries: limited searches that deal with only a few columns in the customer/employee table, or expanded searches that deal with all the columns in the customer/employee table. You note this as a design feature you want to implement in the query engine.
Now you are ready to design the interface for the query engine. Start by examining the database and obtaining a list of all the columns in the customer and employee tables. To ensure that the column names for these tables are dynamically accessible, you can put the column names into arrays as shown in Example 3.
Because end users are dealing with specific tables and columns, you can make it easier for them to choose specific columns using selection menus. To ensure that you can dynamically update a selection menu, you must label the menu with a unique name. As shown in Example 4, you can then populate the menu with a default set of column names. Since you know that you need to switch between limited and expanded searches in either the customer table or the employee table, you must design the interface to handle these tasks. As Example 5 shows, one way to toggle between search modes and tables is with radio buttons.
When the user changes the radio button for table selection, the searchCrit() function is called. The job of this function is to dynamically populate the selection menu. When you examine Example 6, you will see that the selection menu is accessed using the name assigned in the NAME attribute. If the user selects the customer table, the selection menu is populated with the elements from the tableOne array. Otherwise, the tableTwo array is used.
When the user changes the radio button for the search mode, the searchSize() function is called. This function uses the value assigned to the radio button to dynamically resize the selection menu. As you check Example 7, note that a reference to the radio button object is passed to the function.
Take a look at Listing Three to see how the dynamic query engine comes together. Although this is not the finished product, you can get a good idea of the basic structure of the search engine in an actual web page.
Making the Query Engine More Useful
In the real world, database retrievals are much more advanced than those allowed for in the example query engine. Often, users want to retrieve multiple columns from a table based on several different search criteria. Fortunately, once you have the basic structure for the query engine, updating it to meet the needs of your users is fairly easy.
Let's say that your analysis reveals the end-user's search generally looks like this:
- User typically selects no more than five columns, but sometimes wants to return all columns.
- User typically matches against no more than two different search criteria.
- Searches are AND/OR with parameters of >, <, =, or !=.
Figure 2 shows how the interface for the query engine can be updated to accommodate these requirements. The key is to ensure that the column choices and the search criteria selection menus can be updated on the fly.
Fortunately, implementing and repopulating multiple selection menus can be handled in almost the same way you would implement and repopulate a single selection menu. Example 8 shows a straightforward method of repopulating multiple selection menus based on user choices.
Similarly, you can dynamically resize multiple selection menus in much the same way you would dynamically resize a single selection menu. Example 9 shows one way to resize multiple selection menus.
Listing Four is the completed query engine. As you examine the source code, note that this updated search engine allows users to perform fairly complex database retrievals with an easy-to-use interface. Using the updated search engine, the structure of a query is transformed from the limited retrieval of SELECT column_name FROM table WHERE condition to a more powerful structure that reads:
SELECT column_name1[, column_name2, ... column_name5,]
FROM table WHERE conditionA [and/or conditionB]
[plus_additional_criteria]
Retrieving the Dynamic Query
After creating a dynamic search engine, you need a way to retrieve and display the queries. However, before you can retrieve anything from the database, you need to build the query. The first step is to build a list of the columns that the user can see and select from in the search results. Example 10 shows how this could be done. Note that if statements are used instead of else/if statements, just in case the user chooses an additional column but doesn't do so using the order you would normally expect.
Next, you need to determine the search criteria chosen by the user. Example 11 checks to see if the user selected a column name in the second search criteria menu, then adds the additional search criteria if necessary. The code also determines the table the user selected and whether there is miscellaneous search criteria entered into the last input field.
The final step is to put the query together and pass it to the server. When the server responds, you format the retrieval. Listing Five is the complete source for the query and result page.
Conclusion
With LiveWire, you can leverage the power of JavaScript and HTML to develop advanced applications with a minimum amount of fuss. The ease with which you can create applications in LiveWire can shave months off the development of any project, and the query engine developed in this column certainly gives you something to think about.
DDJ
Listing One
<HTML><HEAD> <TITLE>The Help Center: Database Query</TITLE> </HEAD> <BODY BGCOLOR="#FFFFFF"> <SERVER> if(client.query == null) client.query = ""; </SERVER> </p> <FORM METHOD="post" ACTION="rorig.htm"> <H1 ALIGN=CENTER>The Help Center: Database Query</H1> <HR SIZE=5 WIDTH=75%> <P><B>Example:</B> <P>SELECT * FROM employee WHERE emp_dep=Service <P> SELECT <BR> <INPUT type=text NAME="findWhat" SIZE=50 VALUE="*"> <BR> FROM <BR> <INPUT type=text NAME="findFrom" SIZE=50> <BR> WHERE <BR> <INPUT type=text NAME="findWhere" SIZE=50> </p> <P> <INPUT TYPE="submit" VALUE="Enter Query"> </FORM> </p> </BODY> </HTML> </p> </p>
Listing Two
<HTML><HEAD>
<TITLE>The Help Center: Search Results </TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF">
</p>
<SERVER>
</p>
project.lock();
database.connect(client.dbtype,
client.server,
client.id,
client.password,
client.database);
if(!database.connected()) redirect("connect.html");
</p>
client.query = "SELECT " + request.findWhat +
" FROM " + request.findFrom +
" WHERE " + request.findWhere
write(client.query);
database.SQLTable(client.query);
project.unlock();
</SERVER>
</p>
</BODY>
</HTML>
</p>
</p>
Listing Three
<HTML><HEAD>
<TITLE>The Selection Engine</TITLE>
<SCRIPT LANGUAGE="JavaScript1.1">
var datasetOne = "*,cust_name,cust_phone,cust_city,cust_state,cust_account,
cust_company,cust_fax,cust_email,cust_weburl"
</p>
var datasetTwo = "*,emp_name,emp_wphone,emp_city,emp_dep,emp_hphone,
emp_supervisor,emp_pager,emp_ssn,emp_alt"
</p>
tableOne = datasetOne.split(",")
tableTwo = datasetTwo.split(",")
</p>
function searchCrit(which) {
var dataObj = document.forms[0].table
var dataLen = dataObj.length
for (var i = 0; i < dataLen; i++) {
if (which == "customer") {
dataObj.options[i].text = tableOne[i]
} else {
dataObj.options[i].text = tableTwo[i]
}
}
history.go(0)
}
function searchSize(choice) {
var dataObj = document.forms[0].table
var whichDB = (document.forms[0].detTable[0].checked)?"customer":"employee"
dataObj.length = 0
for (var i = 0; i < choice.value; i++) {
if (whichDB == "customer") {
dataObj.options[i] = new Option(tableOne[i])
} else {
dataObj.options[i] = new Option(tableTwo[i])
}
}
dataObj.options[0].selected = true
history.go(0)
}
</SCRIPT>
</HEAD>
</p>
<BODY>
<H1>The Help Center: Query Database</H1>
<FORM>
</p>
Select a table:
<INPUT TYPE="radio" NAME="detTable" VALUE="customer"
onClick="searchCrit('customer')" CHECKED>Customer Accounts
</p>
<INPUT TYPE="radio" NAME="detTable" VALUE="employee"
onClick="searchCrit('employee')">Customer Service Reps
</p>
<P>
Search style:
<INPUT TYPE="radio" NAME="choiceCount" VALUE="6"
onClick="searchSize(this)" CHECKED>Limited
</p>
<INPUT TYPE="radio" NAME="choiceCount" VALUE="10"
onClick="searchSize(this)">Expanded
<P>
Select a database column:
</p>
<SELECT NAME="table">
<OPTION SELECTED>cust_name
<OPTION>cust_phone
<OPTION>cust_city
<OPTION>cust_state
<OPTION>cust_account
</SELECT>
</FORM>
</BODY>
</HTML>
</p>
</p>
Listing Four
<HTML><HEAD>
<TITLE>The Help Center: Database Query</TITLE>
<SCRIPT LANGUAGE="JavaScript1.1">
</p>
var datasetOne = "*,cust_name,cust_phone,cust_city,cust_state,cust_account,
cust_company,cust_fax,cust_email,cust_weburl"
</p>
var datasetTwo = "*,emp_name,emp_wphone,emp_city,emp_dep,emp_hphone,
emp_supervisor,emp_pager,emp_ssn,emp_alt"
</p>
tableOne = datasetOne.split(",")
tableTwo = datasetTwo.split(",")
</p>
function searchCrit(which) {
var dataObj = document.forms[0].tableCol
var dataObj2 = document.forms[0].tableCol2
var dataObj3 = document.forms[0].tableCol3
var dataObj4 = document.forms[0].tableCol4
var dataObj5 = document.forms[0].tableCol5
var dataObj6 = document.forms[0].tableCol6
var dataObj7 = document.forms[0].tableCol7
var dataLen = dataObj.length
for (var i = 0; i < dataLen; i++) {
if (which == "customer") {
dataObj.options[i].text = tableOne[i]
dataObj2.options[i].text = tableOne[i]
dataObj3.options[i].text = tableOne[i]
dataObj4.options[i].text = tableOne[i]
dataObj5.options[i].text = tableOne[i]
dataObj6.options[i].text = tableOne[i]
dataObj7.options[i].text = tableOne[i]
} else {
dataObj.options[i].text = tableTwo[i]
dataObj2.options[i].text = tableTwo[i]
dataObj3.options[i].text = tableTwo[i]
dataObj4.options[i].text = tableTwo[i]
dataObj5.options[i].text = tableTwo[i]
dataObj6.options[i].text = tableTwo[i]
dataObj7.options[i].text = tableTwo[i]
}
}
history.go(0)
}
function searchSize(choice) {
var dataObj = document.forms[0].tableCol
var dataObj2 = document.forms[0].tableCol2
var dataObj3 = document.forms[0].tableCol3
var dataObj4 = document.forms[0].tableCol4
var dataObj5 = document.forms[0].tableCol5
var dataObj6 = document.forms[0].tableCol6
var dataObj7 = document.forms[0].tableCol7
var whichDB = (document.forms[0].detTable[0].checked)?"customer":"employee"
dataObj.length = 0
dataObj2.length = 0
dataObj3.length = 0
dataObj4.length = 0
dataObj5.length = 0
dataObj6.length = 0
dataObj7.length = 0
for (var i = 0; i < choice.value; i++) {
if (whichDB == "customer") {
dataObj.options[i] = new Option(tableOne[i])
dataObj2.options[i] = new Option(tableOne[i])
dataObj3.options[i] = new Option(tableOne[i])
dataObj4.options[i] = new Option(tableOne[i])
dataObj5.options[i] = new Option(tableOne[i])
dataObj6.options[i] = new Option(tableOne[i])
dataObj7.options[i] = new Option(tableOne[i])
} else {
dataObj.options[i] = new Option(tableTwo[i])
dataObj2.options[i] = new Option(tableTwo[i])
dataObj3.options[i] = new Option(tableTwo[i])
dataObj4.options[i] = new Option(tableTwo[i])
dataObj5.options[i] = new Option(tableTwo[i])
dataObj6.options[i] = new Option(tableTwo[i])
dataObj7.options[i] = new Option(tableTwo[i])
}
}
dataObj.options[0].selected = true
dataObj2.options[0].selected = true
dataObj3.options[0].selected = true
dataObj4.options[0].selected = true
dataObj5.options[0].selected = true
dataObj6.options[0].selected = true
dataObj7.options[0].selected = true
history.go(0)
}
</SCRIPT>
</HEAD>
<BODY BGCOLOR="#FFFFFF">
</p>
<SERVER>
if(client.query == null)
client.query = "";
</SERVER>
</p>
<H1>The Help Center: Query Database</H1>
<FORM METHOD="post" ACTION="response.htm">
<B>
Select a table:
<INPUT TYPE="radio" NAME="detTable" VALUE="customer"
onClick="searchCrit('customer')" CHECKED>Customer Accounts
</p>
<INPUT TYPE="radio" NAME="detTable" VALUE="employee"
onClick="searchCrit('employee')">Customer Service Reps
</p>
<P>
Search style:
<INPUT TYPE="radio" NAME="choiceCount" VALUE="6"
onClick="searchSize(this)" CHECKED>Limited
</p>
<INPUT TYPE="radio" NAME="choiceCount" VALUE="10"
onClick="searchSize(this)">Expanded
<P>
Select database columns to return:
<P>
<SELECT NAME="tableCol">
<OPTION SELECTED>*
<OPTION>cust_name
<OPTION>cust_phone
<OPTION>cust_city
<OPTION>cust_state
<OPTION>cust_account
</SELECT> ,
<SELECT NAME="tableCol2">
<OPTION SELECTED>*
<OPTION>cust_name
<OPTION>cust_phone
<OPTION>cust_city
<OPTION>cust_state
<OPTION>cust_account
</SELECT> ,
<SELECT NAME="tableCol3">
<OPTION SELECTED>*
<OPTION>cust_name
<OPTION>cust_phone
<OPTION>cust_city
<OPTION>cust_state
<OPTION>cust_account
</SELECT> ,
<BR>
<SELECT NAME="tableCol4">
<OPTION SELECTED>*
<OPTION>cust_name
<OPTION>cust_phone
<OPTION>cust_city
<OPTION>cust_state
<OPTION>cust_account
</SELECT> ,
<SELECT NAME="tableCol5">
<OPTION SELECTED>*
<OPTION>cust_name
<OPTION>cust_phone
<OPTION>cust_city
<OPTION>cust_state
<OPTION>cust_account
</SELECT>
<P>
Enter Search Criteria:
<P>
<SELECT NAME="tableCol6">
<OPTION SELECTED>*
<OPTION>cust_name
<OPTION>cust_phone
<OPTION>cust_city
<OPTION>cust_state
<OPTION>cust_account
</SELECT>
</p>
<SELECT NAME="equality1">
<OPTION SELECTED>=
<OPTION>!=
<OPTION>>
<OPTION><
</SELECT>
</p>
<INPUT type=text NAME="entry1" SIZE=50>
</p>
<P>
<SELECT NAME="andOr">
<OPTION SELECTED>AND
<OPTION>OR
</SELECT>
<P>
<SELECT NAME="tableCol7">
<OPTION SELECTED>*
<OPTION>cust_name
<OPTION>cust_phone
<OPTION>cust_city
<OPTION>cust_state
<OPTION>cust_account
</SELECT>
</p>
<SELECT NAME="equality2">
<OPTION SELECTED>=
<OPTION>!=
<OPTION>>
<OPTION><
</SELECT>
<INPUT type=text NAME="entry2" SIZE=50>
</p>
<P>
Enter additional criteria:
<INPUT type=text NAME="entry3" SIZE=50>
</p>
<P>
<INPUT TYPE="submit" VALUE="Enter Query">
</FORM>
</p>
</BODY>
</HTML>
</p>
</p>
Listing Five
<HTML><HEAD>
<TITLE>The Help Center: Search Results </TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF">
</p>
<SERVER>
</p>
project.lock();
database.connect(client.dbtype,
client.server,
client.id,
client.password,
client.database);
if(!database.connected()) redirect("connect.html");
var tableColumns = request.tableCol
if (request.tableCol2 != "*") tableColumns += ", " + request.tableCol2
if (request.tableCol3 != "*") tableColumns += ", " + request.tableCol3
if (request.tableCol4 != "*") tableColumns += ", " + request.tableCol4
if (request.tableCol5 != "*") tableColumns += ", " + request.tableCol5
</p>
var searchCrit = request.tableCol6 + request.equality1 + request.entry1
if (request.tableCol7 != "*") searchCrit += " " +
request.andOr + " " + request.tableCol7 +
request.equality2 + request.entry2
</p>
var whichTable = request.detTable
if (request.entry3 != "") searchCrit += " " + request.entry3
client.query = "SELECT " + tableColumns + " FROM " +
whichTable + " WHERE " + searchCrit
write(client.query);
database.SQLTable(client.query);
project.unlock();
</SERVER>
</BODY>
</HTML>
