Writing a getHandler (Coldfusion)

A getHandler is the page that delivers properly formatted XML data to the combobox. The combobox call the getHandler repeatedly as the user types to retrieve the correct list of records.

The combobox sends special querystring parameters to the getHandler. A typical request might look like:

get.cfm?StartingRecordIndex=12&PageSize=12 &SearchSubstring=a&ComboId=myCombo&LastString=Arlene

These parameters help you build your SQL select statement. for a complete explanation of these parameters, see the article titled "Constructing SQL Queries for Combo V3"

This simple walkthrough will show you how to connect a basic grid to a datasource. In this case the datasource is an Access MDB file, although we have provided template SQL for a variety of databases in the SQL Queries tutorial.

The GetHandler

First we set up our Coldfusion page by defining the language we"ll be working in. We also need to include the Nitobi XML API (nitobi.xml.cfm) which is included.

The following code snippets will connect to a database and output 4 columns of xml data. You can output as many columns as you like and you need only use the ones you want in the ComboBox. See the bottom of this page for the corresponding Combo Tag.

Next we retrieve the querystring parameters that will let us write a SQL query. We need to collect information on the PageSize (number of records to return), SearchSubString (the text to search for) and LastString (the string at the bottom of the current list - useful for databases that do not support proper paging like MS Access).

<cfif parameterexists(PageSize) is not "YES">
<CFSET PageSize = 10>
</cfif>

<cfif parameterexists(SearchSubString) is not "YES">
<CFSET SearchSubString = "">
</cfif>

<cfif parameterexists(LastString) is not "YES">
<CFSET LastString = "">
</cfif>

Now we set up the EBAGetHandler API and data structures with a single call to EBAGetHandler_ProcessRecords

<CFOUTPUT>

#EBAGetHandler_ProcessRecords()#

</CFOUTPUT>

Now we define four data columns for the combo.

<CFOUTPUT>

#EBAGetHandler_DefineField("ContactName")#
#EBAGetHandler_DefineField("ContactEmail")#
#EBAGetHandler_DefineField("JobTitle")#
#EBAGetHandler_DefineField("CompanyName")#
#EBAGetHandler_DefineField("ContactImage")#

</CFOUTPUT>

Now, assuming we've already set up our datasource in ColdFusion administrator, we can do our query. The following SQL query will work for the basic search modes like Filter, Classic, and Compact. The SmartSearch, and SmartList modes need double wildcard searching ("LIKE '%SearchSubString%'")

<cfquery name="CustomerQuery" datasource="NitobiTestDB">

SELECT TOP #PageSize# * FROM tblCustomers WHERE ContactName > '#LastString#' AND ContactName LIKE '#SearchSubString#%' ORDER BY ContactName

</cfquery>

We can now loop through our records

<cfoutput query="CustomerQuery">

   #EBAGetHandler_CreateNewRecord(ContactID)#
   #EBAGetHandler_DefineRecordFieldValue("ContactName", ContactName)#
   #EBAGetHandler_DefineRecordFieldValue("ContactEmail", ContactEmail)#
   #EBAGetHandler_DefineRecordFieldValue("JobTitle", JobTitle)#
   #EBAGetHandler_DefineRecordFieldValue("CompanyName", CompanyName)#
   #EBAGetHandler_DefineRecordFieldValue("ContactImage", "scustomerimage.gif")#
  #EBAGetHandler_SaveRecord()#

</cfoutput>

Finally, we output the XML stream to the client.

<CFOUTPUT>

#EBAGetHandler_CompleteGet()#

</CFOUTPUT>

Here is the entire code sample:

<cfinclude template = "nitobi.xml.cfm">





<CFOUTPUT>

#EBAGetHandler_ProcessRecords()#

#EBAGetHandler_DefineField("ContactName")#
#EBAGetHandler_DefineField("ContactEmail")#
#EBAGetHandler_DefineField("JobTitle")#
#EBAGetHandler_DefineField("CompanyName")#
#EBAGetHandler_DefineField("ContactImage")#

</CFOUTPUT>





<cfif parameterexists(PageSize) is not "YES">
<CFSET PageSize = 10>
</cfif>

<cfif parameterexists(SearchSubString) is not "YES">
<CFSET SearchSubString = "">
</cfif>

<cfif parameterexists(LastString) is not "YES">
<CFSET LastString = "">
</cfif>




<cfquery name="CustomerQuery" datasource="NitobiTestDB">

      SELECT TOP #PageSize# * FROM tblCustomers WHERE ContactName > '#LastString#' AND ContactName LIKE '#SearchSubString#%' ORDER BY ContactName

</cfquery>


<cfoutput query="CustomerQuery">

   #EBAGetHandler_CreateNewRecord(ContactID)#
   #EBAGetHandler_DefineRecordFieldValue("ContactName", ContactName)#
   #EBAGetHandler_DefineRecordFieldValue("ContactEmail", ContactEmail)#
   #EBAGetHandler_DefineRecordFieldValue("JobTitle", JobTitle)#
   #EBAGetHandler_DefineRecordFieldValue("CompanyName", CompanyName)#
   #EBAGetHandler_DefineRecordFieldValue("ContactImage", "scustomerimage.gif")#
  #EBAGetHandler_SaveRecord()#

</cfoutput>


<CFOUTPUT>
#EBAGetHandler_CompleteGet()#
</CFOUTPUT>

The Combo Tag

Now on the HTML side, here is an example of a combo tag that would bind to this data.

<ntb:Combo id="myCombo" Mode="filter">
<ntb:ComboTextBox DataFieldIndex=0></ntb:ComboTextBox>
<ntb:ComboList DatasourceUrl="get.cfm" PageSize="5">
    <ntb:ComboColumnDefinition HeaderLabel="Customer Name" DataFieldIndex=0 >
    </ntb:ComboColumnDefinition>
    <ntb:ComboColumnDefinition HeaderLabel="Customer eMail" DataFieldIndex=2>
    </ntb:ComboColumnDefinition>
</ntb:ComboList>
</ntb:Combo>

Notice in this tag the the first column (Customer Name) has DataFieldIndex = 0 meaning it will bind to the first data column (CustomerName). The second column we defined here (Customer eMail) is bound to DataFieldIndex = 2, meaning the 3rd column of data (CustomerEmail).

View Printable Version

Nitobi Product Knowledgebase