0

Paginating Records in CF with One SQL Server 2005 call (update 1)

Posted : Jun 9, 2008 11:18 AM
ColdFusion, SQL Server 2005

I've been meaning to update folks on the excellent use I've been getting out of this way of paginating records with SQL Server 2005 via CF, and James Allen's comment today gave me an excuse to finally update folks:

  1. Note that I've been using this method in production at www.LandsofAmerica.com - see some example search results, as well as all our sister sites: www.landsofTexas.com, www.landsofCalifornia.com, etc. Our combined network gets upwards of 15 ColdFusion requests per second, and this dynamic stored procedure has held up extremely well, despite the enormous load and wide variety of possible WHERE clauses in the search.
  2. And now to answer James's comment, "I've been using your SP extensively around my new site and it's working great. However, I am now tring to get it to work with a table join and I just can't get it to work with a table or alias prefix (in the columns parameter)." - The "sample DAO CFC method" code below should give you what you need to fix the issue you refer to. :)

First, the Stored Procedure SQL again:

USE [propertyControlCenter]
GO
/****** Object:  StoredProcedure [dbo].[sproc_pagination]    Script Date: 06/09/2008 11:28:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ================================================== 
-- Author:        Aaron Longnion 
-- Create date: 10/18/2007 
-- Description:    A gereral-use way of dynamically 
--        creating SQL to easily paginate the results; 
--        also includes a total record count based on 
--        the search criteria in the WHERE clause 
-- ================================================== 
CREATE PROCEDURE [dbo].[sproc_pagination] 
    -- Add the parameters for the stored procedure here 
    @SqlColumns VARCHAR(MAX), 
    @SqlFriendlyColumns VARCHAR(MAX), 
    @SqlTableClause VARCHAR(MAX), 
    @StartRow INT, 
    @EndRow INT, 
    @SqlWhere VARCHAR(MAX), 
    @SqlRowNumOrderBy VARCHAR(MAX), 
    @SqlOuterOrderBy VARCHAR(MAX) 
AS 
DECLARE @rsSQL NVARCHAR(MAX) 
DECLARE @rcSQL NVARCHAR(MAX) 
BEGIN 
    -- SET NOCOUNT ON added to prevent extra result sets from 
    -- interfering with SELECT statements. 
    SET NOCOUNT ON; 
    -- build pagination SQL, using StartRow and EndRow to determine 
    -- which results to output 
    SET @rsSQL = N' WITH tempTable AS ( ' + 
        N' SELECT ' + 
            @SqlColumns + 
        N' , ROW_NUMBER() OVER(ORDER BY ' + 
            @SqlRowNumOrderBy + 
        N' ) AS RowNumber ' + 
        N' FROM ' + 
            @SqlTableClause 
    IF @SqlWhere + '' <> '' 
        BEGIN 
            SET @rsSQL = @rsSQL +                  
                N' WHERE ' + 
                    @SqlWhere 
        END 
    SET @rsSQL = @rsSQL + 
        N' ) SELECT ' + 
            @SqlFriendlyColumns + 
        N' FROM tempTable ' + 
        N' WHERE RowNumber >= ' + 
            CAST(@StartRow AS NVARCHAR(32)) + 
        N' AND RowNumber <= ' + 
            CAST(@EndRow AS NVARCHAR(32)) + 
        N' ORDER BY ' + 
            @SqlOuterOrderBy 
     
    -- uncomment PRINT to debug 
    --PRINT @rsSQL 
    EXEC sp_executesql @rsSQL 
    -- build second recordset simple for the count 
    SET @rcSQL = 
            N'SELECT COUNT(*) AS CountAll FROM ' + 
                @SqlTableClause 
    IF @SqlWhere + '' <> '' 
        BEGIN 
            SET @rcSQL = @rcSQL +                  
                N' WHERE ' + 
                    @SqlWhere 
        END 
     
    EXEC sp_executesql  @rcSQL     
    SET NOCOUNT OFF; 
END 

PRINT 'sproc_pagination created'

Second, some sample Gateway CFC method code:

	<cffunction name="searchInventory"
				output="false"
				access="public"
				returntype="struct"
				hint="Returns total count and recordset based on search criteria">

		<cfargument name="SearchCrit"
					required="true"
					type="struct"
					hint="Search criteria, usually passed in from the URL scope" />

		<cfset var retStruct = StructNew() />

		<cfparam name="Arguments.SearchCrit.SiteStateID" type="string" default="" />
		<cfparam name="Arguments.SearchCrit.State_ID" type="string" default="" />
		<cfparam name="Arguments.SearchCrit.Region_ID" type="string" default="" />
		<cfparam name="Arguments.SearchCrit.County_ID" type="string" default="" />
		<cfparam name="Arguments.SearchCrit.Style" type="string" default="" />
		<cfparam name="Arguments.SearchCrit.Style_ID" type="string" default="" />
		<cfparam name="Arguments.SearchCrit.SqFt" type="string" default="" />
		<cfparam name="Arguments.SearchCrit.Price" type="string" default="" />
		<cfparam name="Arguments.SearchCrit.AuctionDateStart" type="string" default="#DateFormat(Now(), 'yyyy-mm-dd')#" />
		<cfparam name="Arguments.SearchCrit.AuctionDateEnd" type="string" default="" />
		<cfparam name="Arguments.SearchCrit.ListingType" type="string" default="" />
		<cfparam name="Arguments.SearchCrit.PageNum" type="numeric" default="1" />
		<cfparam name="Arguments.SearchCrit.RecordsPerPage" type="numeric" default="15" />
		<cfparam name="Arguments.SearchCrit.sortby" type="string" default="sqft" />
		<cfparam name="Arguments.SearchCrit.sortbyDirection" type="string" default="DESC" />

		<!--- derive start and end rows --->
		<cfset Arguments.StartRow = ( (Arguments.SearchCrit.PageNum-1) * Arguments.SearchCrit.RecordsPerPage )+1 />
		<cfset Arguments.EndRow = (Arguments.StartRow + Arguments.SearchCrit.RecordsPerPage)-1 />

		<cfset retStruct = DAO.searchInventory( ArgumentCollection=Arguments ) />

		<cfset retStruct.PagingData = Server.Utilities.Toolbox.getPagingLinks(
															RecordsPerPage = Arguments.SearchCrit.RecordsPerPage
															, RecordCount = retStruct.RC
															, NumberOfLinks = 10
															, RecordLimit = 1000000
															, QSItemsToStrip = "messageCode,searchIt"
															) />

		<cfreturn retStruct />

	</cffunction>


Third, a sample DAO CFC method. This is the key: see how the "friendly" (no table aliases) names of the columns are in the "local.SqlFriendlyCols" section. Notice how the column names in the "local.SqlCols" clause contain references to table aliases in "local.SqlTableClause". The only caveat for this, which I haven't found a work-around yet, is that you'll get an "ambiguous column" SQL error if you have a JOIN and try to sort by a column that does not have a unique name :

 

	<cffunction name="searchInventory" access="package" output="false" returntype="struct">

		<cfset Var local = StructNew()>

		<!--- columns --->
        <cfsavecontent variable="local.SqlCols">
            I.listing_city,
			ST.State_name,
			ST.State_shortCode,
			S.listing_type,
			I.listing_price,
			I.listing_photo,
			I.listing_id,
			I.listing_state_id,
			I.listing_lot_size,
			I.listing_county_id,
			C.county_name
        </cfsavecontent>

		<!--- friendly cols (if no JOINs/aliased table names, inherit from SqlCols above) --->
        <cfsavecontent variable="local.SqlFriendlyCols">
            listing_city,
			State_name,
			State_shortCode,
			listing_type,
			listing_price,
			listing_photo,
			listing_id,
			listing_state_id,
			listing_lot_size,
			listing_county_id,
			county_name
        </cfsavecontent>

		<!--- tables --->
		<cfsavecontent variable="local.SqlTableClause">
				listing_(nolock) I
			INNER JOIN
				listing_types(NOLOCK) S
				ON S.listing_type_ID = I.listing_type_ID
			INNER JOIN
				counties(nolock) C
				ON C.County_ID = I.listing_County_ID
			INNER JOIN
				states(NOLOCK) ST
				ON ST.State_ID = I.listing_State_ID			
		</cfsavecontent>

		<!--- conditions --->
        <cfoutput>
            <cfsavecontent variable="local.SqlWhere">
				I.listing_Active = 1
				AND I.listing_Acct_Active = 1
				AND I.listing_Status = 0
				<!--- dynamic info --->
				<cfif Len(Arguments.SearchCrit.siteStateID)>
					AND I.listing_state_id = #Arguments.SearchCrit.siteStateID#
				</cfif>
				<cfif ListLen(Arguments.SearchCrit.State_ID)>
					AND I.listing_state_id IN(#Arguments.SearchCrit.State_ID#)
				</cfif>
				<cfif ListLen(Arguments.SearchCrit.Region_ID)>
					AND C.listing_Region_id IN(#Arguments.SearchCrit.Region_ID#)
				</cfif>
				<cfif ListLen(Arguments.SearchCrit.County_ID)>
					AND I.listing_County_id IN(#Arguments.SearchCrit.County_ID#)
				</cfif>
				<cfif ListLen(Arguments.SearchCrit.listing_type)>
					AND S.listing_type_id IN(#Arguments.SearchCrit.listing_type#)
				<cfelseif ListLen(Arguments.SearchCrit.listing_type_ID)>
					AND S.listing_type_id IN(#Arguments.SearchCrit.listing_type_ID#)
				</cfif>
				<cfif ListLen(Arguments.SearchCrit.SqFt)>
					AND I.listing_size >= #Val(ListFirst(Arguments.SearchCrit.listing_size))#
					AND I.listing_lot_size <= #Val(ListLast(Arguments.SearchCrit.listing_size))#
				</cfif>
				
				<cfif ListLen(Arguments.SearchCrit.listing_Price)>
					AND I.listing_Price >= #Val(ListFirst(Arguments.SearchCrit.listing_Price))#
					AND I.listing_Price <= #Val(ListLast(Arguments.SearchCrit.listing_Price))#
				</cfif>
            </cfsavecontent>
        </cfoutput>

		<!--- order --->
		<cfoutput>
	        <cfsavecontent variable="local.SqlRowNumOrderBy">
	            #Arguments.SearchCrit.sortby# #Arguments.SearchCrit.sortbyDirection#
	        </cfsavecontent>
			<!--- used for order column that has an alias prefix --->
	        <cfsavecontent variable="local.SqlOuterOrderBy">
	            #ListLast(local.SqlRowNumOrderBy, ".")#
	        </cfsavecontent>
		</cfoutput>

		<!--- dynamically get the data based on the SQL built above --->
        <cfset local.returnStruct = Server.DAO.Common.getPaginatedData(	SqlCols = local.SqlCols,
																		SqlFriendlyCols = local.SqlFriendlyCols,
																		SqlTableClause = local.SqlTableClause,
																		StartRow = Arguments.StartRow,
																		EndRow = Arguments.EndRow,
																		SqlWhere = local.SqlWhere,
																		SqlRowNumOrderBy = local.SqlRowNumOrderBy,
																		SqlOuterOrderBy = local.SqlOuterOrderBy ) />

		<cfreturn local.returnStruct />

	</cffunction>


And finally, the getPaginatedData() method:

 

	<cffunction name="getPaginatedData" access="public" output="false" returntype="struct" hint="generic way to get pagination data from the dB">

		<cfargument name="SqlCols"
					type="string"
					required="true"
					hint="list of table columns ('col1, col2, col3', 'a.col1, b.col2, c.col3', or 'a.col1 AS acol, b.col2 AS bcol, (SELECT c.col3 FROM c_table) AS subCol3' are all acceptable" />

		<cfargument name="SqlFriendlyCols"
					type="string"
					required="true"
					hint="list of table columns ('col1, col2, col3' is acceptable" />

		<cfargument name="SqlTableClause"
					type="string"
					required="true"
					hint="list of table columns (most JOINs and sub-SELECTs are acceptable" />

		<cfargument name="StartRow"
					type="numeric"
					required="true"
					hint="beginning row number to display" />
		<cfargument name="EndRow"
					type="string"
					required="true"
					hint="last row number to display " />

		<cfargument name="SqlWhere"
					type="string"
					required="true"
					hint="WHERE clause filters" />

		<cfargument name="SqlRowNumOrderBy"
					type="string"
					required="true"
					hint="usually the PK of the primary table" />

		<cfargument name="SqlOuterOrderBy"
					type="string"
					required="true"
					hint="chose from the columns from SqlFriendlyCols" />

		<cfset var returnStruct = StructNew() />

		<cfstoredproc procedure="sproc_pagination" datasource="#Variables.DSNs.primary#">

			<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="SqlColumns" value="#Trim(Arguments.SqlCols)#">
			<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="SqlFriendlyColumns" value="#Trim(Arguments.SqlFriendlyCols)#">
			<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="SqlTableClause" value="#Trim(Arguments.SqlTableClause)#">
			<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="SqlStartRow" value="#Arguments.StartRow#">
			<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="EndRow" value="#Arguments.EndRow#">
			<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="SqlWhere" value="#Trim(Arguments.SqlWhere)#">
			<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="SqlRowNumOrderBy" value="#Trim(Arguments.SqlRowNumOrderBy)#">
			<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="SqlOuterOrderBy" value="#Trim(Arguments.SqlOuterOrderBy)#">
			<!--- return data + count --->
			<cfprocresult name="rs" resultset="1">
			<cfprocresult name="recordcount" resultset="2">

        </cfstoredproc>

		<cfset returnStruct.rs = rs />
        <cfset returnStruct.rc = recordcount.countAll />

		<cfreturn returnStruct />

	</cffunction>

 

Let me know what you think.

	<cffunction name="searchInventory" access="package" output="false" returntype="struct">

		<cfset Var local = StructNew()>

		<!--- columns --->
        <cfsavecontent variable="local.SqlCols">
            I.listing_city,
			ST.State_name,
			ST.State_shortCode,
			S.listing_type,
			I.listing_price,
			I.listing_photo,
			I.listing_id,
			I.listing_state_id,
			I.listing_lot_size,
			I.listing_county_id,
			C.county_name
        </cfsavecontent>

		<!--- friendly cols (if no JOINs/aliased table names, inherit from SqlCols above) --->
        <cfsavecontent variable="local.SqlFriendlyCols">
            listing_city,
			State_name,
			State_shortCode,
			listing_type,
			listing_price,
			listing_photo,
			listing_id,
			listing_state_id,
			listing_lot_size,
			listing_county_id,
			county_name
        </cfsavecontent>

		<!--- tables --->
		<cfsavecontent variable="local.SqlTableClause">
				listing_(nolock) I
			INNER JOIN
				listing_types(NOLOCK) S
				ON S.listing_type_ID = I.listing_type_ID
			INNER JOIN
				counties(nolock) C
				ON C.County_ID = I.listing_County_ID
			INNER JOIN
				states(NOLOCK) ST
				ON ST.State_ID = I.listing_State_ID			
		</cfsavecontent>

		<!--- conditions --->
        <cfoutput>
            <cfsavecontent variable="local.SqlWhere">
				I.listing_Active = 1
				AND I.listing_Acct_Active = 1
				AND I.listing_Status = 0
				<!--- dynamic info --->
				<cfif Len(Arguments.SearchCrit.siteStateID)>
					AND I.listing_state_id = #Arguments.SearchCrit.siteStateID#
				</cfif>
				<cfif ListLen(Arguments.SearchCrit.State_ID)>
					AND I.listing_state_id IN(#Arguments.SearchCrit.State_ID#)
				</cfif>
				<cfif ListLen(Arguments.SearchCrit.Region_ID)>
					AND C.listing_Region_id IN(#Arguments.SearchCrit.Region_ID#)
				</cfif>
				<cfif ListLen(Arguments.SearchCrit.County_ID)>
					AND I.listing_County_id IN(#Arguments.SearchCrit.County_ID#)
				</cfif>
				<cfif ListLen(Arguments.SearchCrit.listing_type)>
					AND S.listing_type_id IN(#Arguments.SearchCrit.listing_type#)
				<cfelseif ListLen(Arguments.SearchCrit.listing_type_ID)>
					AND S.listing_type_id IN(#Arguments.SearchCrit.listing_type_ID#)
				</cfif>
				<cfif ListLen(Arguments.SearchCrit.SqFt)>
					AND I.listing_size >= #Val(ListFirst(Arguments.SearchCrit.listing_size))#
					AND I.listing_lot_size <= #Val(ListLast(Arguments.SearchCrit.listing_size))#
				</cfif>
				
				<cfif ListLen(Arguments.SearchCrit.listing_Price)>
					AND I.listing_Price >= #Val(ListFirst(Arguments.SearchCrit.listing_Price))#
					AND I.listing_Price <= #Val(ListLast(Arguments.SearchCrit.listing_Price))#
				</cfif>
            </cfsavecontent>
        </cfoutput>

		<!--- order --->
		<cfoutput>
	        <cfsavecontent variable="local.SqlRowNumOrderBy">
	            #Arguments.SearchCrit.sortby# #Arguments.SearchCrit.sortbyDirection#
	        </cfsavecontent>
			<!--- used for order column that has an alias prefix --->
	        <cfsavecontent variable="local.SqlOuterOrderBy">
	            #ListLast(local.SqlRowNumOrderBy, ".")#
	        </cfsavecontent>
		</cfoutput>

		<!--- dynamically get the data based on the SQL built above --->
        <cfset local.returnStruct = Server.DAO.Common.getPaginatedData(	SqlCols = local.SqlCols,
																		SqlFriendlyCols = local.SqlFriendlyCols,
																		SqlTableClause = local.SqlTableClause,
																		StartRow = Arguments.StartRow,
																		EndRow = Arguments.EndRow,
																		SqlWhere = local.SqlWhere,
																		SqlRowNumOrderBy = local.SqlRowNumOrderBy,
																		SqlOuterOrderBy = local.SqlOuterOrderBy ) />

		<cfreturn local.returnStruct />

	</cffunction>

Related Content:
Paginating Records in CF with One SQL Server 2005 call
tags:
ColdFusion, SQL Server 2005
 
0

Does your email validation allow plus signs?

Posted : Apr 30, 2008 1:21 PM
ColdFusion

Go ahead and check. I'll wait.

 

I'm not kidding, go ahead.  It'll be alright.  You're not the only one whose says made_up_account_here+do_not_spam_me@gmail.com is an invalid address.

 

"er, are you sure a plus sign is valid?", you ask?  Yep.  I'm certain.  All the time, I use my gmail accounts nifty feature that allows me to use, say, made_up_account_here+twitter@gmail.com to filter emails from twitter to a twitter "label".  But some sites, especially blog sites, always tell me my email is invalid when I put in the plus sign.  Well, your site is wrong, and I'd appreciate it if you'd fix it.  ;-)  (when you get time of course)

 

anyways, after you checked, what did you find?

 
0

Bad Day at the Office video

Posted : Jan 15, 2008 1:35 PM
ColdFusion, Videos

I usually don't post stuff like this, but since I had a good laugh (cuz can't we all relate?) and wanted to try the embed feature on the new InstantSpot 2.0 software.

 

 
0

Help requested: best way to batch process images?

Posted : Jan 3, 2008 9:04 AM
ColdFusion

A help request to anyone who has experience with heavy batch processing of images.  Below are the requirements, and based on these can anyone tell me if I'm doing the wrong thing by proposing that we use CF 8, with it's new cfimage tag and related image functions?

  1. get a recordset of potentially hundreds of thousands of URLs to images (mostly JPEGs and PNGs, with potentially some TIFFs and BMPs)
  2. iterate over those URLs, resize each image to "web-optimal" sizes, and save those to disk in an organized directory structure
  3. batch processing should not stop just because of 404's or other issues: in CF, obviously I could just do try/catch exception-handling to deal with such issues gracefully)
  4. log, or otherwise report, details of successes and failures during the batch process
  5. log, or otherwise report, fatal errors
  6. Windows 2003 compatible

I welcome any and all suggestions.  I don't know if there is some type of image processing software that can do this type of batch processing more efficiently than CF 8, or if we would get significant gains from using Java, .NET, perl or other languages directly?  (note that we have very limited experience with non-CF programming, so we'd only consider others if we'll see significant gains)

TIA for any help.  ;-} 

tags:
ColdFusion
 
0

a CFeMmy nomination - wow!

Posted : Dec 19, 2007 11:27 AM
ColdFusion

I'm humbled to be nominated for a CFeMmy award, the 8th category called "Best Newcomer (CF Blog that started in 2007)". Thanks to whomever got me on the list, as it's a truly excellent list, and I'm glad it's a long one since that means our CF community is growing quickly.

Vote at http://cfsilence.com/soundings/survey.cfm?id=EDC...

Also, thanks to the folks at InstantSpot.com for hosting my blog. Thanks to cfsilence.com for hosting the CFeMmys, and thanks to the CF community in general for all that you do and keeping me in a good job by supporting ColdFusion!

Note that I finally added an About section to my blog, in case your interested in a brief description of my background.

Peace.

tags:
ColdFusion
No comments for this entry. || Add Comment Add Comment
 
0

CF 8 Server Monitor vs. SeeFusion 4.0.6

Posted : Nov 27, 2007 11:43 AM
ColdFusion

I haven't seen a concise article on the primary (IMHO) similarities and differences between the CF 8 Server Monitor and SeeFusion.  I have 2 years experience with SeeFusion, and have used nearly all of it's features, and I have been researching and tinkering with the Server Monitor tools of CF 8 for the past few months.  Here's my top 10 lists of what I've found to be most important:

Primary Similarities

  1. Real-time monitoring UI
  2. Can toggle Monitoring ON/OFF on the fly (no CF server restart)
  3. Active request tracking
  4. Alerts which can send emails based on various CF events
  5. CF stack traces
  6. "Kill" command available to stop long-running/hung requests
  7. Reporting on slow pages and queries
  8. Filtering out pages not to monitor
  9. Can forcibly run Garbage Collection at any time
  10. API available to program against

Primary Differences

CF 8 Server Monitor

  1. Server and Application scope details
  2. UI includes CF Error reporting
  3. CFThread Monitoring
  4. Template Cache status
  5. Detailed Memory Usage Tracking
  6. CF Scope details
  7. Cached Query status/details
  8. Database Pool status
  9. Ability to create CF "Snapshots"
  10. Alerts can also call a CFC, Dump Snapshots, and reject new requests

SeeFusion

  1. Decoupled from CF - if CF goes down or hangs, SeeFusion is not affected and continues to get all information
  2. Counters: real-time view of current and recent requests
  3. Log large queries to stdout (based on # of rows)
  4. SeeFusion.trace() function for more granular, code-level request monitoring
  5. Streaming debugging output, with filtering and IP-restriction
  6. Logging of slow requests to a database via JDBC
  7. Logging of slow queries to a database via JDBC, including query exceptions
  8. Logging of SeeFusion counters to a database via JDBC
  9. Optional/configurable logging of user-defined events to a database via JDBC
  10. Configurable rowLimit parameter for limiting query result sets (and preventing OutOfMemory errors)

Sources:

  • http://seefusion.com/index.cfm?do=c.versions
  • https://admin.adobe.acrobat.com/_a227210/p59838964/
  • http://www.adobe.com/devnet/coldfusion/articles/monitoring_pt2_02.html
I think I got all the big items, but are there items I missed that are important to you?

 

 

tags:
ColdFusion
 

Search

Aaron  Longnion

Austin, TX 
Recent Blog Entries
Paginating Records in CF with One SQL Server 2005 call (update 1)
Does your email validation allow plus signs?
Bad Day at the Office video
Help requested: best way to batch process images?
a CFeMmy nomination - wow!
CF 8 Server Monitor vs. SeeFusion 4.0.6
Cheap CF 8 Beyond the Basics Training
Paginating Records in CF with One SQL Server 2005 call
Nearly 7 hours of Inexpensive ColdFusion 8 Training
CF 8: all the new Tags, Tag Attributes, and Functions
Archives
All Categories 
ColdFusion (28) 
SQL Server 2005 (1) 
Videos (1) 
Zen (8) 
tags
ColdFusion SQL Server 2005 Zen

View all tags