Working with record sets

The cfquery, cfldap, and cfpop tags return the results of a database query in a record set. In some cases, you might want to search the record set. This section describes the reasons and procedures for indexing the results of database, LDAP, and pop queries. It also describes how a database can direct the indexing process, using different values for the type attribute of the cfindex tag.

Indexing database record sets

The following are the steps to perform a Verity search on record sets:

Write a query to generate a record set.
Index the record set.
Search the record set.

Performing searches against a Verity collection rather than using cfquery provides faster access, because the Verity collection indexes the database. Use this technique instead of cfquery in the following cases:

You want to index textual data. You can search Verity collections containing textual data much more efficiently with a Verity search than with a SQL query.
You want to give your users access to data without interacting directly with the data source itself.
You want to improve the speed of queries.
You want your users to run queries but not update database tables.

Indexing the record set from a ColdFusion query involves an extra step not required when you index documents. You must code the query and output parameters, and then use the cfindex tag to index the record set from a cfquery, cfldap, or cfpop query.

You write a cfquery that retrieves the data to index, then you pass this information to a cfindex tag, which populates the collection. The cfindex tag contains the following attributes that correspond to the data source:

The cfindex
attribute

Description

key

Primary key of the data source table

title

Specifies a query column name

body

Column(s) that you want to search for the index

The cfindex attribute	Description
key	Primary key of the data source table
title	Specifies a query column name
body	Column(s) that you want to search for the index

Using the cfindex tag on large custom query data can cause a "Java out of memory error" or lead to excessive disk use on your computer. Because ColdFusion reads custom queries into memory, if the query size is larger than your physical memory, then paging of physical memory to disk occurs. The size of physical memory used is the smaller of the actual physical memory on your computer and the Java Virtual Machine (JVM) masimum memory parameter. You can specify the JVM parameter in the Administrator or in the configuration file cfsuionmx/runtime/bin/jvm.config by the argument [-Xmx512m].

The following procedure assumes that you have a Verity collection named CodeColl. For more information, see "Creating a collection with the cfcollection tag". The following procedure uses the CompanyInfo data source that is installed with ColdFusion.

To index a ColdFusion query:

Create a ColdFusion page with the following content:

<html>
<head>
  <title>Adding Query Data to an Index</title>
</head>
<body>

<!--- retrieve data from the table --->
<cfquery name="getEmps" datasource="CompanyInfo">
  SELECT * FROM EMPLOYEE
</cfquery>

<!--- update the collection with the above query results --->
<cfindex 
  query="getEmps"
  collection="CodeColl"
  action="Update"
  type="Custom"
  key="Emp_ID"
  title="Emp_ID"
  body="Emp_ID,FirstName,LastName,Salary">

<h2>Indexing Complete</h2>

<!--- output the record set --->
<p>Your collection now includes the following items:</p>
<cfoutput query="getEmps">
  <p>#Emp_ID# #FirstName# #LastName# #Salary#</p>
</cfoutput>
</body>
</html>

Save the file as collection_db_index.cfm in the myapps directory under the web root directory.
Open the file in the web browser to index the collection.
The resulting record set appears:

Using the cfindex tag for indexing tabular data is similar to indexing documents, with the following exceptions:

You set the type attribute to custom when indexing tabular data.
You refer to column names from the cfquery in the body attribute.

To search and display database records:

Create a ColdFusion page with the following content:

<html>
<head>
  <title>Searching a collection</title>
</head>
<body>

<h2>Searching a collection</h2>

<form method="post" action="collection_db_results.cfm">
  <p>Collection name: <input type="text" name="collname" size="30" maxLength="30"></p>

  <p>Enter search term(s) in the box below. You can use AND, OR, NOT, 
  and parentheses. Surround an exact phrase with quotation marks.</p>
  <p><input type="text" name="criteria" size="50" maxLength="50">
  </p>
  <p><input type="submit" value="Search"></p>
</form>

</body>
</html>

Save the file as collection_db_search_form.cfm in the myapps directory under the web_root.
This file is similar to collection_search_form.cfm, except the form uses collection_db_results.cfm, which you create in the next step, as its action page.

Create another ColdFusion page with the following content:

<html>
<head>
<title>Search Results</title>
</head>

<body>

<cfsearch 
   collection="#Form.collname#"
   name="getEmps"
   criteria="#Form.Criteria#">


<!--- output the record set --->
<cfoutput>
Your search returned #getEmps.RecordCount# file(s).
</cfoutput>

<cfoutput query="getEmps">
  <p><table>
  <tr><td>Title: </td><td>#Title#</td></tr>
  <tr><td>Score: </td><td>#Score#</td></tr>
  <tr><td>Key: </td><td>#Key#</td></tr>
  <tr><td>Summary: </td><td>#Summary#</td></tr>
  <tr><td>Custom 1:</td><td>#Custom1#</td></tr>
  <tr><td>Column list: </td><td>#ColumnList#</td></tr>
</table></p>

</cfoutput>

</body>
</html>

Save the file as collection_db_results.cfm in the myapps directory under the web_root.
View collection_db_search_form.cfm in the web browser and enter the name of the collection and search terms; for example, search the CodeColl collection for lightner or crooks.
The following figure shows how the output appears:

Indexing cfldap query results

The widespread use of the Lightweight Directory Access Protocol (LDAP) to build searchable directory structures, internally and across the web, gives you opportunities to add value to the sites that you create. You can index contact information or other data from an LDAP-accessible server and allow users to search it.

When creating an index from an LDAP query, remember the following considerations:

Because LDAP structures vary greatly, you must know the server's directory schema and the exact name of every LDAP attribute that you intend to use in a query.
The records on an LDAP server can be subject to frequent change, so re-index the collection before processing a search request.

In the following example, the search criterion is records with a telephone number in the 617 area code. Generally, LDAP servers use the Distinguished Name (dn) attribute as the unique identifier for each record so that attribute is used as the key value for the index.

<!--- Run the LDAP query --->

<cfldap name="OrgList"
  server="myserver"
  action="query"
  attributes="o, telephonenumber, dn, mail"
  scope="onelevel"
  filter="(|(O=a*) (O=b*))"
  sort="o"
  start="c=US">

<!--- Output query record set --->
<cfoutput query="OrgList">
  DN: #dn# <br>
  O: #o# <br>
  TELEPHONENUMBER: #telephonenumber# <br>
  MAIL: #mail# <br>
=============================<br>
</cfoutput>

<!--- Index the record set --->
<cfindex action="update"
  collection="ldap_query"
  key="dn"
  type="custom"
  title="o"
  query="OrgList"
  body="telephonenumber">

<!--- Search the collection --->
<!--- Use the wildcard * to contain the search string --->
<cfsearch collection="ldap_query"
  name="s_ldap"
  criteria="*617*">

<!--- Output returned records --->
<cfoutput query="s_ldap">
  #Key#, #Title#, #Body# <br>
</cfoutput>

Indexing cfpop query results

The contents of mail servers are generally volatile; specifically, the message number is reset as messages are added and deleted. To avoid mismatches between the unique message number identifiers on the server and in the Verity collection, you must re-index the collection before processing a search.

As with the other query types, you must provide a unique value for the key attribute and enter the data fields to index in the body attribute.

The following example updates the pop_query collection with the current mail for user1, and searches and returns the message number and subject line for all messages containing the word action:

<!--- Run POP query --->

<cfpop action="getall"
  name="p_messages"
  server="mail.company.com"
  userName="user1"
  password="user1">

<!--- Output POP query record set --->
<cfoutput query="p_messages">
  #messagenumber# <br>
  #from# <br>
  #to# <br>
  #subject# <br>
  #body# <br>
<hr>
</cfoutput>

<!--- Index record set --->
<cfindex action="update"
  collection="pop_query"
  key="messagenumber"
  type="custom"
  title="subject"
  query="p_messages"
  body="body">

<!--- Search messages for the word "action" --->
<cfsearch collection="pop_query"
  name="s_messages"
  criteria="action">
  
<!--- Output search record set --->
<cfoutput query="s_messages">
  #key#, #title# <br>
</cfoutput>

Using database-directed indexing

You can use the cfindex tag with a database that contains information on how to construct, or populate, the index. The cfindex tag has a type attribute, which can have custom, file, or path as its value. When type=custom, ColdFusion populates a collection with the contents of the record set. When type=file or type=custom, the record set becomes the input to perform any action-as defined by the action attribute-that uses the key attribute as input for filenames or filepaths.

The following figure shows a database that you can use to populate a collection:

The database (record set) that, working with the cfindex tag, can direct the indexing process of a collection

The following code shows how to populate a collection named snippets with files that are specified in the description column of the database:

<html>

<head>
  <title>Database-directed index population</title>
</head>

<body>

<cfquery name="bookquery" 
    datasource="book">
    SELECT * FROM book where bookid='file'
        </cfquery>

<cfoutput query="bookquery">
    #url#,#description# <br>

<cfindex collection="snippets" action="update" type="file" query="bookquery" key="description" urlpath="url">

</cfoutput>
</body>
</html>

Use the following code to search the snippets collection and display the results:

<cfsearch name="mySearch" collection="snippets" criteria="*.,.*">

<cfdump var="#mySearch#">

The following code shows how to populate the snippets collection with paths that are specified in the description column of the database:

<html>

<cfquery name="bookquery" 
    datasource="book">
    SELECT * FROM book where bookid='path1' or bookid='path2'
        </cfquery>

<cfoutput query="bookquery">
    #url#,#description# <br>
    
<cfindex collection="snippets" action="update" type="path" query="bookquery" key="description" urlpath="url" >

</cfoutput>

Developing ColdFusion MX Applications with CFML
Building a Search Interface