Integrating a web site with a structured database poses a particular challenge.
How can a developer provide a flexible, easy to use, yet robust search interface for users to query the information stored in the database? In this article Beth provides a complete solution for this problem using Visual FoxPro, ADO and XML, by providing a dynamic searching mechanism in the middle tier that users can control from the front end.
If you've ever developed a database application, you know how important queries are for locating information accurately and quickly. Users want to query data in a variety of combinations, which means that their search interfaces need to be robust and easy to use. When these applications move to the web, the need for a scalable, dynamic and flexible search mechanism becomes more crucial, and providing a flexible design becomes more of a challenge. If you're developing a web application that allows users to perform dynamic searches, this article is for you.
XML does well in heterogeneous environments or in situations where data must be marshaled across the internet
When building our components, we must consider what format will be used to pass data back and forth from our middle tier objects to our client. We call these containers of data “resources.” Resources in their purest sense can take the form of ADO recordsets, XML streams, VFP cursors, arrays, strings or anything else that can contain data or state information. In distributed applications, however, we need a resource that can be easily marshaled across our application tiers. This usually weeds out VFP cursors and arrays because they cannot natively marshal themselves across process boundaries.
ADO recordsets work well in homogeneous environments where the servers are all Windows servers on the same network. XML streams do well in heterogeneous environments or in situations where data must be marshaled across the Internet. Our “data source” components are the business objects responsible for executing the queries and creating the resources that are passed back. I have chosen in this example to pass back our data in disconnected ADO recordsets, because our middle-tier components and the database are on the same Windows network.
Next, we need to figure out how we are going to pass search criteria parameters from the client to the data source components. When a user is searching for something, we usually present her with a screen to enter filter criteria. These criteria are interpreted by our application and translated into a query statement against the database. We need to construct a search criteria resource that can package up the user's criteria and send it to the data source. I'm going to show you how to create two types of search criteria resources: XML and disconnected ADO recordsets.
In this example, I chose to use Visual FoxPro to build my components because of its elegance and object-oriented nature. However, I use ADO in my data source components as the method for querying and returning data from the database to the web server, so any COM language like Visual Basic would do just fine. All the source code I will be presenting can be downloaded from www.flashcreative.com or from my website at www.BethMassi.com.
The example application I have created consists of a Visual FoxPro Database, Visual FoxPro middle tier DLL, and few ASP pages (search and results pages) for ADO and XML search criteria resources. If you plan on a large volume of users on your site, you may want to place the component in Microsoft Transaction Server (or Components Services in Windows 2000. In our application, however, we are making queries against a VFP database that doesn't support MTS transactions. Additionally, even if we were using a database that was COM-TX or XA compatible, we are not writing to it, so transaction support is unnecessary and would just be additional overhead.
How Does It Work?
To perform complex queries in our web application, the search page collects the search criteria from the user and then calls the data source component's GetRSSearchCriteriaResource or GetXMLSearchCriteriaResource interfaces (see Figure 1). These are public methods defined in the data source's CAbstractDataSource class definition. These interfaces pass back the Search Criteria Resource (SCR) that can take the form of either an ADO recordset or an XML stream. The client can be the web server managing the resource, or we could even have the client browser manipulate an XML resource directly. Internet Explorer 5 and higher supports XML and comes with the MSXML parser to manipulate XML on the client. I will show how to do this later in the article.
Having the middle tier generate the SCR makes it easer on the presentation service, because it simply collects the criteria from the user and places it in the SCR. The presentation service also doesn't need to know the details of the underlying data. The downside is that we are making an extra call to the middle tier. If we wanted to avoid this, we could have the presentation service create the SCR itself. The search criteria resource contains fields from the data source that created it. By default it contains only the fields from the data source's primary table. However, I'll show you how we can easily specify additional elements in the SCR from other data sources.
The client fills the search criteria resource with the user's selection criteria and then passes it back into an interface called GetDataSourceNameBySearchCriteria, where DataSourceName is the name of the particular data source component we are calling. There are two data sources in the example: Customer and CustomerNote. The search interface on Customer is GetCustomerBySearchCriteria and the search interface on CustomerNote is GetCustomerNoteBySearchCriteria. The Search Criteria Manager (SCM) on the middle tier then takes the SCR and iterates it, calling on the Syntax Object to create the SQL-SELECT clauses, which are used to generate the SQL statement in the Data source (Figure 2). The class definitions for the SCM's and Syntax classes are located in the accompanying code in the file CSearchCriteria.prg.
The Search Criteria Resource (SCR)
Packaging the Search Criteria Data
The Search Criteria Resource is the mechanism by which the search criteria is passed between the component and the client. They can take the form of ADO Recordsets or XML streams. The Search Criteria Manager dictates the structure of these resources. By default, the SCR has the following character properties:
- Table - The name of the base table
- Name - Field Name
- Type - ADO
DataTypeEnumrepresented as a character - Value - Field Value
- EndValue - Field End Value (for retrieving ranges of data)
- Order - The order
- Operator - Comparison operator:
LIKE,BETWEEN,$,= - Options - Order options like
DESCENDING
This list of properties is defined by the Search Criteria Managers and can be subclassed to allow additional properties. In the case of an ADO recordset, the properties above would represent the fields collection in the recordset and you would have X number of rows depending on how many searchable fields you wanted to provide. In the case of an XML stream, you would have <FIELD> nodes for each searchable field with the properties above being elements of the field node.
<?xml version='1.0'?>
<SEARCHCRITERIA>
<FIELD>
<TABLE>CUSTOMER</TABLE>
<NAME>lastname</NAME>
<TYPE>129</TYPE>
<ORDER></ORDER>
<OPTION></OPTION>
<OPERATOR>LIKE</OPERATOR>
<VALUE></VALUE>
<ENDVALUE></ENDVALUE>
</FIELD>
.
.
.
</SEARCHCRITERIA>
The client receives this SCR, which it then fills in with the appropriate values, and passes it back via the GetDataSourceNameBySearchCriteria() method.
Data Source Functionality
Adding Additional Search Criteria
By default, the SCR contains only fields from the data source's primary table, as declared in the cTableName property of the data source. The SCM creates a default set of search criteria by calling the GetEmptyResource() method of the data source. The GetEmptyResource() method creates an empty ADO recordset that has the exact same structure as the data source's primary table. This method is called transparently when the GetXMLSearchCriteriaResource() or GetRSSearchCriteriaResource() methods are called from the client. In this example, we need to provide additional fields in the search criteria resource for our CustomerNote data source, to allow the client to search on note contents. Therefore, in the data source method AddSearchCriteriaInfo_Post, we have additional code to create extra nodes/rows on the resource before it is sent to the client. So, in the CustomerNote data source in aDatasources.prg, you will see:
Protected Function AddSearchCriteriaInfo_Post( roRS )
*----------------------------------
With This.oSearchReferences.oSearchCriteriaManager
.AddSearchElements( @roRS, "Customer", "LastName", adChar )
.AddSearchElements( @roRS, "Customer", "FirstName", adChar )
EndWith
Return FILE_OK
Endfunc
The fields passed to the Search Criteria Manager's AddSearchElements() method are the SCR, the table name, the field name and the data type. This causes a new node/row of search criteria data to be added to the SCR.
The Search Criteria Manager (SCM) Classes
Generating and Iterating Search Criteria Resources
The Search Criteria Managers are not only used when sending SCRs out of the data source, but also (and more importantly) they are used to iterate the SCRs being sent into the data source to create the clauses of the SELECT statements. To understand exactly how the clauses are constructed, we must look deeper into the Search Criteria Manager. The SCM “manages” the Search Criteria Resources. The SCMs are the objects that dictate the structure of these SCRs as well as the method for iterating them. The SCM is controlled by the data source and can be different, depending on the SCR. In the example, I have included two SCM's; CSearchCriteriaManagerXML and CSearchCriteriaManagerRS, which are subclasses of CAbstractSearchCriteriaManager. The Data source's CreateFilterFromResource() method instantiates the proper SCM based on the type of the SCR. If the SCR is an XML stream, the Data source instantiates the CSearchCriteriaManagerXML object; if the SCR is an ADO recordset, the CSearchCriteriaManagerRS is instantiated. If the type of SCR cannot be determined, a FILE_ERROR is returned from the CreateFilterFromResource() method. The SCM iterates through the SCR and, for each row or node in the SCR, it calls upon the Syntax Object to help construct a WHERE clause.
The Search Syntax Classes
Creating the Clauses
The Search Criteria Manager controls how the Search Criteria Resources are created and sent out to the client. However, in order to create the clauses of the SELECT statement, the SCM and the Syntax Object (SO) must work together when SCRs are sent back into the data source. The SCM is in charge of creating and iterating through the SCR, while the Syntax object is responsible for creating the clauses of the SELECT statement. Because of the differences in SQL languages in different database backends, the data sources can specify which Syntax object to use to produce their clauses. The Syntax object is a concrete class, which inherits from CAbstractSearchSyntax. I have provided three classes for you that inherit from this class. They are CVFPSearchSyntax, CSQLSearchSyntax and CoracleSearchSyntax, for use respectively with Visual FoxPro, Microsoft SQL-Server and Oracle databases. You can add additional search syntax classes for other databases or other criteria sets as needed.
We can have our business objects dictate what our search form will look like by sending XML directly to the client
The Syntax object is instantiated by the SCM and is called upon to generate the proper clauses for the database being used by the data source. The data source property cSyntaxClass holds the name of the class to use. In our example, we are using a VFP database, so the CVFPSearchSyntax object is instantiated. The Syntax Object then creates a Clauses object to hold all of the generated clauses. This makes it easier for us to access all the clauses of the SELECT statement via one object. In our example, the Syntax object creates WHERE and ORDER BY clauses and places them in the Clause Object's cWhere and cOrder properties. As the Syntax object creates the WHERE clause, it is also internally managing the ORDER BY clause by filling and sorting an aOrderClause[] array, which is converted to an ORDER BY clause after the WHERE clause is constructed. The example provided creates a WHERE clause with the search criteria being ANDed together. To create more complex queries using the OR operator, you can modify the SCR to hold the additional property (or create a hierarchical XML document) and then enhance the SCM to handle it properly.
The Syntax object is called by the SCM every time a search criteria value is encountered while parsing the SCR (XML stream or ADO recordset). It constructs just the piece of the where clause that the field is involved in.
Consider this clause in VFP:
WHERE "columbia" $ lower(custnote.note)
This is the same clause in SQL-Server:
WHERE custnote.note LIKE '%Columbia%'
The syntax is different because SQL-Server is not case sensitive by default like VFP. Because of differing syntax, the Syntax object's GetFilterSyntax() methods may have different or additional code for constructing and supporting database-specific WHERE clauses. After these strings are constructed, they are put into a Clauses Object and passed back to the GetDataSourceNameBySearchCriteria() method. Here the clauses are tacked onto the SQL SELECT statements before calling ExecuteSQLQuery() to perform the query on the database.
The CSearchClausesParameter Class
Storing the clauses in a Search Parameter Object
In order to more easily and flexibly handle all the generated clauses of the SQL-Select, a CSearchClausesParameter object (subclass of CAbstractParameter) is created to hold all of the generated clauses in properties. The protected oSearchClauses property of the Data source references the search clauses object after the call to CreateFilterFromResource(). This way, we can easily retrieve the clauses in the GetDataSourceNameBySearchCriteria() methods.
FUNCTION GetCustomerNoteBySearchCriteria(txSCR)
LOCAL loADO, lcSelect
loADO = THIS.GetADOAggregateParameter()
loADO.oCommand.ActiveConnection = loADO.oConnection
lcSelect = [ SELECT CustNote.*, ] + ;
[ RTRIM(Customer.LastName)+', '] +;
[ RTRIM(Customer.FirstName)] +;
[ AS FullName ] + ;
[ FROM CustNote ] + ;
[ INNER JOIN Customer ON ] + ;
[ CustNote.Customer_ID = ] + ;
[ Customer.Customer_ID ]
If This.CreateFilterFromResource(txSCR) = FILE_OK
With this.oSearchClauses
*-- Apply the filter to the select statement
loADO.oCommand.CommandText = lcSelect + .cWhere + .cOrder
EndWith
Else
loADO.oCommand.CommandText = lcSelect + [ WHERE 1 = 0 ]
Endif
This.oSearchClauses = Null
This.ExecuteSQLQuery( loADO )
RETURN loADO.oRecordSet
ENDFUNC
The Front-End ASP Client
Searching Using Disconnected ADO Recordsets
In the example, I have a search page called adosearch.htm that presents some of the search criteria data to the user. For simplicity, I have kept the search screen simple. The user enters filter criteria and the form is submitted back to an ASP page called List.asp on the server, which places the form data into an ADO recordset SCR and calls the middle tier to execute the query.
In the List.asp page we need to fill our search criteria resource with the user's search criteria by finding the appropriate rows in the recordset and setting the value column equal to the submitted form's value. This is done with the recordset's Find method. You will also notice that I am specifying an order by setting the Order column's value. As you can see, you could easily design a much more flexible search screen by letting the user specify beginning and ending values, as well as orders.
Dim obj, rs, oSCR
Set obj = Server.CreateObject("FlexSearch.CustomerNote")
Set oSCR = obj.getRSSearchCriteriaResource()
oSCR.Find "Name = 'lastname'",,1,1
oSCR("Value") = Request.Form("LastName")
oSCR("Order") = "1"
oSCR.Find "Name = 'subject'",,1,1
oSCR("Value") = Request.Form("subject")
oSCR.Find "Name = 'note'",,1,1
oSCR("Value") = Request.Form("note")
oSCR.Find "Name = 'time'",,1,1
oSCR("Order") = "2"
oSCR("Option") = "DESC"
Once we've filled the SCR, we pass it into the middle tier and get back a resource with our data in it.
Set rs = obj.getCustomerNoteBySearchCriteria(oSCR)
Set obj = Nothing
Set oSCR = Nothing
The variable rs now holds a reference to an ADO recordset containing our data, which we can iterate and display in an HTML table. I typed in “F” for the Customer Last Name, giving me all the notes that belong to a customers whose last name is like “F” (Figure 3). In an actual live web application, you probably would not want to bring down all the notes in the search. I would allow hyperlinks on the customer name, for example, and display the individual record and the note in a separate window. For this example, however, I wanted to show the notes so we could see that our search was working properly.
The Front-End XML Client
Searching Using XML in Internet Explorer 5
This next example is really fun. Instead of a simple static search form, we are going to send to the browser our XML search criteria resource for it to manipulate. That way, we can have the business objects in our middle tier dictate what our search form will look like. If we add a field to the data source, we do not have to do anything to the search form. An easy way to do this is by creating an HTML page for the IE browser with the XML SCR as a “data island.” In Internet Explorer 5, you can use the <XML> tag to create a data island inside your HTML. Data islands are XML data referenced or included in an HTML page. The XML data can be included within the HTML or it can be in an external file. In the example file xmlsearch.asp we generate an XML data island from the middle tier:
<XML ID="MyXMLDoc">
<%
Dim obj, xmlSCR
Set obj = Server.CreateObject("Flexsearch.CustomerNote")
xmlSCR = obj.GetXMLSearchCriteriaResource()
Set obj = Nothing
Response.Write(xmlSCR)
%>
</XML>
This code places the XML SCR generated by our middle-tier data source between the <XML> tags in our generated HTML page. To bind the XML to a table and controls, we have to specify the DATASRC element of the <TABLE> tag and the DATAFLD elements of the HTML controls:
<TABLE DATASRC="#MyXMLDoc">
<THEAD>
<TH>Field</TH>
<TH>Value</TH>
<TH>Order</TH>
</THEAD>
<TR>
<TD>
<DIV DATAFLD="name"></DIV>
</TD>
<TD>
<INPUT TYPE=text DATAFLD="value">
</TD>
<TD>
<INPUT TYPE=text DATAFLD="order">
</TD>
</TR>
</TABLE>
This HTML block binds the XML tree in the data island called MyXMLDoc to a DIV tag and two HTML inputs. Now the user can manipulate the XML SCR directly in the browser. Neat!
Now comes the tricky part. When the user wants to submit the search we have a special method of posting XML back to the server. You'll notice that there are no <FORM> tags in the generated HTML page. We don't want to submit the form the normal way because we don't want the values of the HTML controls, but instead want the XML tree. Internet Explorer and the MSXML parser include an object called XMLHTTP that we will use to post XML back to an ASP page on the server. Using the XMLHTTP object, you can post the XML tree itself as an object. In the following script, I create an XMLHTTP object and use it to post the XML tree MyXMLDoc to an ASP page on the server called post.asp.
<SCRIPT language="VBScript">
Sub submitInfo
dim httpObj
Set httpObj = CreateObject("Microsoft.XMLHTTP")
httpObj.Open "POST", "post.asp", false
httpObj.send MyXMLDoc.XMLDocument
document.all("results").innerHTML = httpObj.responseText
Set httpObj = Nothing
End Sub
</SCRIPT>
Now that we have posted the XML Search Criteria Resource to the server, we need to instantiate our middle-tier object and pass it the XML SCR. You'll see that post.asp has less code than our previous ADO example, because we do not have to fill the SCR manually, but instead directly pass in the XML SCR. To get at the XML tree in the Request Object, I instantiate the XMLDOM object and use the load method to load it. I then can access the XML SCR by referring to oSCR.xml:
<%
Dim rs, oSCR, obj
Set oSCR = Server.CreateObject("Microsoft.XMLDOM")
oSCR.async=false
oSCR.load(Request)
Set obj = Server.CreateObject("Flexsearch.CustomerNote")
Set rs = obj.GetCustomerNoteBySearchCriteria(oSCR.xml)
Set oSCR = Nothing
Set obj = Nothing
%>
The variable rs now holds a reference to a disconnected ADO recordset containing our data, which we can iterate and display in an HTML table. To display the generated HTML in the browser, you will notice in the client script a line that sets the innerHTML of a span tag called results to the responseText of the XMLHTTP Object. This is how the data is transmitted back to the client. As you can see, you could use the XMLHTTP object to send updated XML as well as HTML back to the client. Figure 4 shows our XML search criteria screen and a part of the results underneath.
You'll notice that I searched for all notes whose subject field started with INT and also specified an order by customer last name and then by note time. The XML SCR contains more data that you can display and allow the user to manipulate, like end values and ordering options. You can even bind data to <SELECT> tags and other HTML controls. I just left that out for the sake of simplicity.
Conclusion
Providing flexible searching on your data is not always easy, and constructing a simple, yet flexible user interface can be even more difficult. If you take special care in designing a good object-oriented framework for your middle tier, you will have an easier time adapting to other technologies like XML or ADO+. Take a more detailed look at the source code for examples of common design patterns used to connect and retrieve the data, as well as in the searching mechanism I described. Any way you look at it, distributed applications are here to stay. It's up to developers like us to find new ways to provide features on the web that users took for granted on their desktop. I hope this article has shown you a few fun and efficient ways to create dynamic queries against your remote data without sacrificing user interface flexibility or ease of use.
