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.
| <cfinclude template = "nitobi.xml.cfm"> |
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).
| <!--- Note: MS Access (the type of database we use in this example) has no native ---> <!--- support for 'Paging' which is what we're doing here. So instead we loop through ---> <!--- all the records and just return the ones we want. ---> <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:
| <!--- We include the Nitobi CF XML library 1.0 ---> <cfinclude template = "nitobi.xml.cfm"> <!--- First we set up the get handler by calling EBAGetHandler_ProcessRecords() ---> <!--- Then we define our data fields and give them the same names they have in the database ---> <!--- (you dont have to give them the same names, but it makes it easier to keep track of them ---> <CFOUTPUT> #EBAGetHandler_ProcessRecords()# #EBAGetHandler_DefineField("ContactName")# #EBAGetHandler_DefineField("ContactEmail")# #EBAGetHandler_DefineField("JobTitle")# #EBAGetHandler_DefineField("CompanyName")# #EBAGetHandler_DefineField("ContactImage")# </CFOUTPUT> <!--- Note: MS Access (the type of database we use in this example) has no native ---> <!--- support for 'Paging' which is what we're doing here. So instead we loop through ---> <!--- all the records and just return the ones we want. ---> <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 perform a query on our database. See the instructions for help setting this ---> <!--- sample database up. We have given it a datasource name of NitobiTestDB---> <cfquery name="CustomerQuery" datasource="NitobiTestDB"> SELECT TOP #PageSize# * FROM tblCustomers WHERE ContactName > '#LastString#' AND ContactName LIKE '#SearchSubString#%' ORDER BY ContactName </cfquery> <!--- Now we output our records as we loop through the query. ---> <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> <!--- Now we call EBAGetHandler_CompleteGet to return all our xml to the browser. ---> <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).
Comments:
|
Note: This article was taken from the Nitobi knowledgebase. To search the knowledgebase, go to http://www.nitobi.com/kb/. |
