Loading Data (CFMX)
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 you could easily convert it to connect to an MS SQL server. There are two pages in this walkthrough. The first builds a web page that displays a grid. The second web page connects to a datasource and supplies the grid with data.
Data Handler (getHandler) in Coldfusion MX
The getHandler is the script the Grid calls to load data into the grid.
In this example, the gethandler is called load_data.cfm.
The grid may return all or none of the following parameters to the gethandler (depending on the configuration of the grid)
| load_data.cfm?StartRecordIndex=0&pagesize=13&sortcolumn=CustomerName&sortdirection=ASC&searchstring=abc |
- StartRecordIndex- The record number to start returning data at.
- PageSize - How many records to return
- SortColumn - The data should be sorted by this xml field
- SortDirection - The direction the data should be sorted.
- SearchString - The text to search by (LOOKUP gethandlers only)
If this gethandler is being written for a LOOKUP field, only the searchstring parameter needs to be accounted for. The searchstring parameter is the text that is being typed by the user.
First we set up our CFM 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. Note: currently the Nitobi XML API is a simple cftemplate include. Later we will introduce CFMODULE or CFC support, depending on feedback.
| <!--- We include the Nitobi CF XML library 1.0 ---> <cfinclude template = "nitobi.xml.cfm"> |
Now we get our PageSize (how many records to return)
| <cfif parameterexists(PageSize) is not "YES"> <CFSET PageSize = 10> </cfif> |
Now we get our StartRecordIndex from the querystring (the orginal start position, ie: "start at the nth record")
| <cfif parameterexists(StartRecordIndex) is not "YES"> <CFSET StartRecordIndex = 0> </cfif> |
Now we collect the current sort column. Note: you must put your default sort column here also (maybe your primary key). In this case, our default sort column is ContactID. Also note that grid may not return a sortcolumn at all if there is no current sort column.
| <cfif parameterexists(SortColumn) is not "YES" OR SortColumn is ""> <!--- We set a default sort column incase none is defined ---> <CFSET SortColumn = "ContactID"> </cfif> |
Now we collect our sort direction. This will be either "Asc" or "Desc".
| <cfif parameterexists(SortDirection) is not "YES"> <CFSET SortDirection = ""> </cfif> |
Now we do our database query. Because we have do paging, we can either do this in the SQL query or in our loop. For simplicity, and because the Access databases we use in the demos do not support any paging keywords (like LIMIT or SKIP), we will retrieve all the records up to the last record we need, and in our loop, just disregard everything less than StartRecordIndex.
| <cfparam name="TotalRecordsToRetrieve" default="10"> <cfset TotalRecordsToRetrieve = #PageSize# + #StartRecordIndex#> <cfquery name="CustomerQuery" datasource="NitobiTestDB"> SELECT TOP #TotalRecordsToRetrieve# * FROM tblContacts3k ORDER BY #SortColumn# #SortDirection# </cfquery> |
Now we set up the EBAGetHandler API and data structures with a single call to EBAGetHandler_ProcessRecords
| <CFOUTPUT> #EBAGetHandler_ProcessRecords()# </CFOUTPUT> |
Now we define several data columns for the grid.
| <CFOUTPUT> #EBAGetHandler_DefineField("ContactName")# #EBAGetHandler_DefineField("ContactEmail")# #EBAGetHandler_DefineField("JobTitle")# #EBAGetHandler_DefineField("CompanyName")# #EBAGetHandler_DefineField("PhoneNumber")# #EBAGetHandler_DefineField("Address")# </CFOUTPUT> |
Now we loop through our recordset
| <cfparam name="CurrentRecord" default="0"> <cfoutput query="CustomerQuery"> <cfset CurrentRecord = (CurrentRecord + 1)> <cfif CurrentRecord GREATER THAN StartRecordIndex > #EBAGetHandler_CreateNewRecord(ContactID)# #EBAGetHandler_DefineRecordFieldValue("ContactName", ContactName)# #EBAGetHandler_DefineRecordFieldValue("ContactEmail", ContactEmail)# #EBAGetHandler_DefineRecordFieldValue("JobTitle", JobTitle)# #EBAGetHandler_DefineRecordFieldValue("CompanyName", CompanyName)# #EBAGetHandler_DefineRecordFieldValue("PhoneNumber", PhoneNumber)# #EBAGetHandler_DefineRecordFieldValue("Address", Address)# #EBAGetHandler_SaveRecord()# </cfif> </cfoutput> |
Finally, we output the XML stream to the client.
| <CFOUTPUT> #EBAGetHandler_CompleteGet()# </CFOUTPUT> |
You can test this gethandler in your browser by calling it like this:
| load_data.cfm?StartRecordIndex=50&PageSize=20&SortColumn=&SortDirection=Asc&TableID=_default |
This should return the top 20 records after record # 50. No sort column has been defined (which is the case sometimes), but the results should be returned in ascending order according to the default sort column.
Comments:
|
Note: This article was taken from the Nitobi knowledgebase. To search the knowledgebase, go to http://www.nitobi.com/kb/. |
