I wrote this in my article on persistent CFCs for Community MX last week, but it's worth repeating here because I see a lot of people using CFCs in session and application scope that do not take this into account: you should always declare local variables at the top of your <cffunction> tag:

<cffunction name="blah" returntype="any">
<cfset var i = 0>
<cfloop from="0" to="10" index="i">
<!--- Some code --->
</cfloop>
</cffunction>

Many people are doing this, but I wonder how many people apply the same principle to recordsets within the CFQUERY tag. . . .I see this a lot:

<cffunction name="testRS" access="public" output="false">
  <cfquery name="rs" datasource="Northwind">
  SELECT * FROM Products
  </cfquery>
  <cfreturn rs>
</cffunction>

If this is in a persistent scope, the variable rs will be available even after the return call. In fact, it will hang around for the life of the persistent CFC. To properly scope the query, you should declare it first:

<cffunction name="testRS" access="public" output="false">
  <cfset var rs = "">
  <cfquery name="rs" datasource="Northwind">
  SELECT * FROM Products
  </cfquery>
  <cfreturn rs>
</cffunction>

Now, the rs query will be destroyed after the function returns the variable to the caller -- it is not persisted within the CFC. You can try it like this. Create a cfc:

<cfcomponent>

<cffunction name="testRS" access="public" output="false">
<cfquery name="rs" datasource="Northwind">
SELECT * FROM Products
</cfquery>
<cfreturn rs>
</cffunction>

<cffunction name="testRSBad" access="public" output="false">
<cfreturn rs>
</cffunction>

</cfcomponent>

The function testRSBad() looks like it should throw an error, because rs is not defined, however if this is in persistent state and you hit the testRS() method first, then rs is persisted for the entire session.

Try it out: make sure you have sessions turned on in the Application.cfm file. Then put some code on a page called testrs.cfm:

<a href="testrs.cfm?hit=true">Next page</a>

<cfif not isdefined("url.hit")>
  <cfset session.user1 = createobject("component","testuser").new()>
  <cfset session.user1.testRS()>
</cfif>

<cfdump var=#session.user1.testRSBad()#>

You have a link to the page, and you are instantiating the session instance of the CFC only once -- when you preview the page. When you hit the link, you will see the page again with the same recordset dumped out, but this time it is coming from the leftover persisted global variable rs that was not scoped properly in the CFC.

Cross-posted at Tom-Muck.com

I added new RSS feeds to the CMXtraneous blog -- full posts with HTML code. The original RSS feeds stripped HTML and also truncated posts to a small summary. Now, in the RSS module in the sidebar, you can get either the original RSS feed or the full post feed. This also works for individual blog author feeds as well.

The feeds are styled somewhat with XSL and CSS so that they can be read in a browser.

I was prompted by a question on the CMX forums today to finally break down and write a function to return a number of words from a database field, which I've been meaning to do for a long time. There are script examples on the web for ASP and ColdFusion code to truncate a specific database field to a certain number of words (split at the word rather than mid-word, as the LEFT function does), but there is no easy way to do it in SQL, unless you use a loop. The following function will truncate any field to a specific number of words. Pass in the string you want to parse, and the number of words to return.

CREATE FUNCTION fnGetNumberOfWords (
  @stringToSplit varchar(8000),
  @numberOfWords int
)

RETURNS varchar(8000) AS

BEGIN

DECLARE @currentword varchar(8000)
DECLARE @returnstring varchar(8000)
DECLARE @wordcount int
SET @wordcount = 0
SET @returnstring = ''
SET @currentword = ''
SET @stringToSplit = ltrim(rtrim(@stringToSplit))
Declare @index int

WHILE @wordcount < @numberOfWords AND len(@stringToSplit) > 0
  BEGIN
    Select @index = CHARINDEX(' ', @stringToSplit)
    if @index = 0
      BEGIN
        SELECT @currentword = ltrim(rtrim(@stringToSplit))
        SELECT @wordcount = @numberOfWords
      END
    else
      BEGIN
        IF (len(@stringToSplit) - @index > 0) BEGIN
        SELECT @currentword = ltrim(rtrim(LEFT(@stringToSplit, @index-1)))--the new shortened string
        SELECT @stringToSplit = RIGHT(@stringToSplit,LEN(@stringToSplit) - @index) -- the rest
      END
    END
  SELECT @returnstring = @returnstring + ' ' + @currentword
  SELECT @wordcount = @wordcount + 1
END

SET @returnstring = LTRIM(@returnstring)
RETURN @returnstring

END

Call it like this:

SELECT dbo.fnGetNumberOfWords(MyField, 10) FROM mytable

(returns first 10 words from MyField)

The advantage to doing it in the database rather than on the web page, is that you are only returning a small portion of the field to the web page, rather than the entire field. This can speed up the query. A few preliminary tests show that the smaller number of words you return, the quicker the query will execute. In other words, if your query returns a field that can contain up to 8000 characters in it (like a blog entry, for example) and you only need the first 50 words for a summary, the query to return the 50 words will be faster than a query that returns the whole field. Also, your scripted page will execute faster because it is simply displaying the field and not performing any further logic, looping, or parsing on the field.

I talked about user-defined SQL functions in one of my articles at Community MX as well:

Using CSV Strings in SQL Server: Part 2

Note that the function does not work on text or ntext data types. I hope you find it useful.