AdvancedSearch Method

The AdvancedSearch method is a public function in the Search component. It is called from the LoadCollection method in the TitleMatch component (in LitCritC.dll) as a result of the reviewer clicking Find in the Choose Title dialog box. AdvancedSearch does the following:

Accepts five parameters (TitleKeywords, AuthorKeywords, MediaTypes, and optionally Logon and ConnectionString). See Passing Parameters to AdvancedSearch.
Produces an SQL query string using the parameter values. See Assembling the SQL String.
Runs the query to select titles in the FmLib SQL Server™ database and opens a hierarchical recordset. See Opening the Connection.
Creates an empty structure for a recordset. See Creating the rsFlat Recordset Definition.
Copies data from the hierarchical recordset to the flat recordset (no hierarchy). See Copying Data to the rsFlat Recordset.
Returns the resultant flat ADODB recordset to the LoadCollection method. See Returning the Flat Recordset.

The complete code for the AdvancedSearch method is available in Search.cls in the code section.

Passing Parameters to AdvancedSearch

The method uses five parameters:

TitleKeywords contains the user entry in the Title text box.
AuthorKeywords contains the user entry in the Authors text box.
MediaTypes (optional) contains the selected media type.
Logon (optional) contains the user's logon name.
ConnectionString (optional) contains the pre-assembled string of connection information.

Assembling the SQL String

Steps in assembling the SQL string

Use the SHAPE command to define the structure of a hierarchical recordset. Hierarchical Recordsets in CML/LitCrit describes how a shaped recordset is created.
```
strSQL = "SHAPE {SELECT DISTINCT t.bib#,t.title,CONVERT(char(4),t.pubdate,112) AS pubdate,t.coll FROM title AS t WHERE "
```
Call the TypesFromArray method passing MediaTypes as a parameter. The string returned by TypesFromArray (strWhere) is the media type criteria. This string is appended to the SQL string. The CML/LitCrit application only allows you to choose one or all media types and strWhere can hold one media type or be blank. When the "all media types" option is chosen, the TypesFromArray method returns an empty string.
```
strWhere = TypesFromArray(MediaTypes)
If strWhere <> "" Then strSQL = strSQL & strWhere & " AND ("
```

Call the SearchString method in the CML Search component twice, once for the user entry in the Title text box and once for the user entry in the Authors text box of the Choose Title dialog box, to assemble the respective text box contents into strings that meet the syntax requirements of a full-text CONTAINS search.

For Title:

If TitleKeywords <> "" Then
   SearchString = TitleKeywords
   strSQL = strSQL & "CONTAINS(t.*,'" & SearchString & "')"
   strOR = " OR "
End If

For Authors:

If AuthorKeywords <> "" Then
   SearchString = AuthorKeywords
   strSQL = strSQL & strOR & "t.bib# IN (SELECT DISTINCT ta.bib# FROM titleauth AS ta JOIN author AS a ON ta.auth#=a.auth# AND CONTAINS(a.*,'" & SearchString & "')) "
End If

Add a sort order to the query string.

' Sort by title
If strWhere <> "" Then strSQL = strSQL & ") "
strSQL = strSQL & " ORDER BY t.title} "

Call the GetInfoFromTable method of the CML User component passing the alias of the reviewer as a parameter to the method, which returns the borrower# from the borrower table of the FmLib database. When no match for the alias is found in the borrower table, a borrower number of 0 is returned.
```
If Logon <> "" Then
   Set oUser = New CML.User
   nBorrower = oUser.GetInfoFromTable(Logon)
   Set oUser = Nothing
End If
```
A child recordset for the critique associated with a valid borrower and the title (bib#) is added to the query. The phrase "AS critique" creates the critique child recordset (chapter). The CML/LitCrit application has a business rule that limits a reviewer to exactly one critique per title; there cannot be multiple records in the critique child recordset, and this query could be written using joins and subqueries. However, the query would be complex to write and debug and since one child recordset is required to support multiple authors for a title, it makes good sense to take advantage of a child recordset for critiques as well. The "?" in the phrase "AND bib# = ?" indicates that bib# contains the value of parameter 0.
```
strSQL = strSQL & " APPEND ({SELECT objectid,isapproved FROM critique WHERE borrower#=" & nBorrower & " AND bib#=?} AS critique RELATE 'bib#' TO PARAMETER 0)"
   
```
An alternative to the preceding syntax is:
```
strSQL = strSQL & " APPEND ({SELECT objectid,isapproved FROM critique WHERE borrower#=" & nBorrower "} AS critique RELATE 'bib#' TO 'bib#')"
```
Add a child recordset that contains the first and last names of all authors associated with the title (bib#) to the query. The phrase "AS authors" creates the critique child recordset (chapter). Notice that after first usage, APPEND is implied by " , " and the Shape APPEND command is not repeated.
```
strSQL = strSQL & ", ({SELECT a.fname,a.lname FROM author AS a JOIN titleauth AS ta ON ta.auth#=a.auth# WHERE ta.bib#=?} AS authors RELATE 'bib#' TO PARAMETER 0)"
```

Opening the Connection

Create the recordset and connection objects using the MTS CreateInstance method, which allows the objects to run in the MTS run-time environment and to use the MTS resource dispenser to boost performance and scalability. MTS Contributions to the CML in Scenario 1 describes the benefits of deploying MTS.

Set cn = MTS.CreateInstance("ADODB.Connection")
Set rs = MTS.CreateInstance("ADODB.Recordset")

Open rs, an ADODB recordset, on the query.

The value of the StayInSync property of the recordset is set to True, which indicates that the row position in a child recordset (chapter) will change to correspond to a change in the row position of the parent recordset. For example, when the row in the title recordset (the parent recordset) changes, the reference to the critique and authors recordsets (child recordsets or chapters) also changes. The default value of the StayInSync property is True.

rs.StayInSync = True
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly

Creating the rsFlat Recordset Definition

Make the empty structure for a new recordset on the fly. This structure is an array defining the seven columns in the recordset. Each column definition contains an array of four required attributes:

The name of the column header. For example, bib# is a column header in the recordset.
The data type expressed as an integer. You can use the integer or the Visual Basic® constant. For example, CInt(adChar) in the pubdate column could just as well be written CInt(129).
The size of the column expressed as an integer. The integer – 1 indicates a fixed-length column.
Nullability of the column expressed as a Boolean. False indicates that NULLs are not allowed and True indicates that NULLs are allowed.

Use the RDSServer.DataFactory object to create the empty recordset (rsFlat).

Set df = MTS.CreateInstance("RDSServer.DataFactory")
Set rsFlat = df.CreateRecordSet(vColumnDefs)

Copying Data to the rsFlat Recordset

Copy each title record from the rs recordset to the rsFlat recordset. The title fields (bib#, title, pubdate, and coll) are copied at a 1:1 ratio, but the author fields (last and first name) require special handling because there can be multiple authors for a title. When rs, a hierarchical recordset, was created two child recordsets were also created; one for critiques and one for authors. The critique child recordset can only contain one row per title because a business rule limits each person (borrower#) to one critique per title in the FmLib database, but the authors child recordset often has multiple rows because a title can have more than one author.

Two ADODB fields are created, fldAuthors and fldCritique, and the contents of the authors and critique child recordsets are copied to their respective ADODB fields.

Set fldAuthors = rs("authors")
Set fldCritique = rs("critique")

FldAuthors contains first and last names of all the authors for a title; the value of fldAuthors is passed to the AuthorsToString method and the string returned by the method is copied into the authors field in the rsFlat recordset.

When a title has a critique fldCritique contains the objectId and the approval status (yes or no) of the critique. The values from fldCritique are copied to the rsFlat objectId and isApproved fields. If there is no critique, the objectId field in the rsFlat recordset is set to vbNullString (string value 0) and the isApproved field is set to NULL.

Returning the Flat Recordset

All the records from the hierarchical recordset are copied to the flat recordset (rsFlat). As each title record is added to rsFlat, the associated authors child recordset is copied to a ";"-delimited string and added as one field. The two fields from the critique child recordset are also added. After all records are copied to rsFlat the AdvancedSearch method returns the recordset to the LoadCollection method.

Set AdvancedSearch = rsFlat