koen brenner: coldfusion and some other things

Today, I discovered that empty list items were causing a query to bomb with the helpful “You have an error in your SQL syntax” message. The SQL attempting to execute was SELECT name, group_id FROM riders WHERE group_id IN (11,,,,,,,,,,,,), where the value 11,,,,,,,,,,,, is generated from a variable called groupidlist.

There are a few ways to deal with this situation. My first instinct was to remove the empty list items from groupidlist. I prefer to clean things up as early as possible, in case this value might be stored or used again somewhere else down the line. I can easily remove the empty items from the list by performing the following:

<cfset groupidlist = arrayToList(listToArray(groupidlist)) />

The listToArray function removes empty list items as a bonus.

The other option, which should be performed as well, is to use cfqueryparam in my SQL statement.

SELECT name, group_id 
FROM riders
WHERE group_id IN (<cfqueryparam cfsqltype="CF_SQL_INTEGER" list="Yes" separator="," value="#groupidlist#" />)

The following characters must be escaped in JSON strings:

• Quotation mark (”)
• Reverse solidus (\)
• Control characters, such as Return and Line feed. (U+0000 through U+001F)

Quotation marks and reverse solidus (backslashes) are easily escaped by inserting a reverse solidus before the offending character. For example:

"I enjoyed the book "American Gods"" -> "I enjoyed the book \"American Gods\""
"The file is located at "c:\documents\mydocument.txt"" -> "The file is located at "c:\\documents\\mydocument.txt"

As for control characters, these can be escaped by following this pattern:

"U+0001" -> "\u0001"

There are a few different ways of inserting new and existing data into a database table using SQL. Here are a few examples:

INSERT INTO table_name
VALUES (value1, value2, value3, ...)

INSERT INTO table_name (column_1, column_2 ...)
VALUES (value1, value2 ...)

The simplest insert statement, of course, is used when you just want to insert one record and be done with it. If you do not specify a column list as in the second example, you must specify values for all the columns in the table (minus auto-incrementing columns).

SELECT column1, ... [or *]
INTO new_table_name [IN external_database_name]
FROM table_name
[LEFT JOIN join_table_name
ON table_name.column1 = join_table_name.column1
WHERE table_name.col1 = 'value']

Usually, this type of insert is used when making a quick backup table. The structure of the source table, along with the data, is copied into the new table.

INSERT INTO table_name (column1, column2, column3, ...)
SELECT column1, column2, ...
FROM source_table_name
[WHERE column1 = 'value']

A sub-select allows you to insert rows from one table into another already existing table.

I was messing around with XSL templates today in an attempt to clean up my transformations and to make things more modular and maintainable. The apply-templates directive is pretty awesome, but it wasn’t long before I hit my first snag.

I pass a “primarySection” parameter into the XSL that essentially drives the initial order of the sections. I need to display the section that matches this parameter first, followed by the rest of the sections in a specific order.

First, I apply the dynamically named template:

<xsl:apply-templates select="*[name()=$primarySection]" />

Then, I apply all remaining templates in order:

<xsl:apply-templates select="*[name()='Section1' and name()!=$primarySection]" />
<xsl:apply-templates select="*[name()='Section2' and name()!=$primarySection]" />
<xsl:apply-templates select="*[name()='Section3' and name()!=$primarySection]" />
<xsl:apply-templates select="*[name()='Section4' and name()!=$primarySection]" />
<xsl:apply-templates select="*[name()='Section5' and name()!=$primarySection]" />
<xsl:apply-templates select="*[name()='Section6' and name()!=$primarySection]" />

I’m not very happy with the application of the remaining templates. Is there a better way to do this?

Sometimes it’s impossible to control the content of incoming XML. We can ask our customers to please leave out the junk, but we can’t expect them to be able to tell what plays nice in HTML and what’s just dirty. When creating and modifying content, a typical process includes pasting text straight from word processing software, such as Microsoft Word.

There are a number of special characters inserted by Microsoft Word that do not translate well to HTML. For example (my favorites) are the grave ( ` ) and acute ( ´ ) accents, the double grave ( ̏ ) and double acute accents ( ˝ ), and the emdash (—).

There are some very handy resources for converting these special characters using ColdFusion. Check out the safetext() UDF for stripping out lots of stuff, not just the characters mentioned above. Raymond Camden “borrows” from the safetext() UDF to make things simple with the following code:

<cfset goodText=replaceList(badText, chr(8216) & "," &
chr(8217) & "," & chr(8220) & "," & chr(8221) & "," & chr(8212) & "," &
chr(8213) & "," & chr(8230),"',',"","",--,--,...")>

First, I’ll use ColdFusion to read in the XML document, protecting these special characters from being converted incorrectly. If I use the XMLParse() function and pass in a file path as the first parameter, ColdFusion will convert special characters to gobbley-gook, and then I won’t be able to use the conversion code described above. So I have to first read in the file, taking advantage of the charset attribute of the cffile tag, and then run XMLParse() on the file content.

<cffile action="read" file="c:/path/to/my/xml/document.xml" variable="myXMLText" charset="utf-8" />
<cfset myXMLDoc = XMLParse(myXMLText) />

Now I can extract my XML text and attribute nodes, making sure to run the result through the cleansing process described above. The last thing I want is dirty HTML.

Almost anything accomplished on the web with a standard GET or POST request resulting in a web page refresh can be done using Ajax. All links on a page could pull back and display new information on a web page instead of doing a typical web page refresh. Bits of content can be replaced or added extremely quickly. Information can be saved to a database without interrupting the flow of the application.

1. Database record pagination: When a web application returns more than 20 records to a web page, it is appropriate to create links to scroll through the data. These links can call an Ajax request to retrieve and display the next or previous records from the database.
2. Edit in place: Edit in place is a popular Ajax-enabled feature in Web 2.0 sites. Clicking what appears to be just text on a web page immediately shows the text in an input box, ready to edit and save. See this demo for an example.
3. Ajax tabs: When the user clicks an Ajax powered tab, the content for the tab is retrieved and displayed by Ajax.
4. Star ratings: Many sites use an Ajax powered star rating feature. Blank stars are provided to the user to rate a book, movie, restaurant, or other item. The user clicks the star that represents their opinion about the item. The result is immediately sent to the server and saved by the database, but the user does not experience any interruption in their browsing experience.

The main objective of using Ajax is to no longer effect the user experience with the request/refresh nature of the web browser. The user does not lose their place on your web site or on your web page. Their experience becomes more smooth, and as a result, more pleasurable.

An Ajax request returns text and is called the “response”. The Ajax response takes one of the following formats: plain text, HTML, XML, or JSON and is processed by JavaScript. Each format may be handled in a slightly different way. We’ll explore each type.

Plain Text
Handling plain text may be the easiest of all the types. Mainly, plain text is used for a straight output to the browser.

document.getElementById("myText").innerHTML = myAjaxRequest.responseText;

HTML
Handling HTML is identical to handling plain text. HTML returned from an Ajax request will be set to appear on the web page or replace existing content.

document.getElementById("myHTML").innerHTML = myAjaxRequest.responseText;

XML
The XML format is the original and default output format of an Ajax request. In order to handle this type of data, we will use Javascript to parse the XML. We use some of the same methods to parse XML as we would to read the document object model (DOM) of the web page.

// get an array of player elements from the responseXML
var players = myAjaxRequest.responseXML.getElementsByTagName('player');
// loop over the array
for (var i=0;i<players.length;i++) {
  // create a player container div
  var x = document.createElement('div');
  // create an h2 element 
  var y = document.createElement('h2'); 
  // display the player name in the h2 element
  y.appendChild(document.createTextNode(getNodeValue(players[i],'name')));
  // append the h2 element (name) to the player div
  x.appendChild(y); 
  // display the player div in the myXML identified div on the web page
  document.getElementById('myXML').appendChild(x);
}

JSON
JSON stands for JavaScript Object Notation. The JSON format is a string that can be interpreted as a JavaScript object. In order to process this response, you use JavaScript’s eval() method to convert the string into a real JavaScript object, which you can then read into new or existing elements in the web page. This is similar to the way XML is parsed and displayed, but there are minor differences in the way the text is extracted from the response.

// get an array of player elements from the response
var data = eval('(' + myAjaxRequest.responseText + ')');
// loop over the array
for (var i=0;i<data.mariners.length;i++) {
  // create a player container div
  var x = document.createElement('div');
  // assign a css class to the div
  x.className = 'player';
  // create an h2 element
  var y = document.createElement('h2'); 
  // display the player name in the h2 element
  y.appendChild(document.createTextNode(data.mariners[i].player.name));
  // append the h2 element (name) to the player div
  x.appendChild(y);
  // display the player div in the myJSON identified div on the web page
  document.getElementById('myJSON').appendChild(x);
}

To Know: Document Object Model
Studying the Document Object Model will make parsing incoming XML much easier. See Tellme for a full Document Object Model reference.

Other References
AJAX:Getting Started at the mozilla developer center
The AJAX response: XML, HTML, or JSON? at QuirksBlog

A radio button value can be defaulted as selected by inserting “checked” into the input tag.

<input type="radio" name="btn1" value="yes" checked>

The “checked” property can be inserted into the input tag based on records stored in a database. Here are two examples:

A database column can store a yes/no, true/false, or 0/1 value. You’ll have to check the value in the database then output the “checked” property accordingly.

<cfif myQuery.yesnofield is "Yes">
	<input type="radio" name="YesOrNo" value="Yes" checked /> Yes
<cfelse>
	<input type="radio" name="YesOrNo" value="No" /> No
</cfif>

Instead of using a standard select input field in your form, you may want to use radio buttons. In the same way that you would set the “select” property of a value tag based on database values, you can also set the “checked” property in a list of check boxes.

<cfoutput query="myinformation">
	Name: <input type="name" name="myName" value="myinformation.name" />
	<cfloop query="favoriteColors">
		<cfif myinformation.favoriteColor is favoriteColors.favoriteColor>
			<cfset radioChecked = "checked" />
		<cfelse>
			<cfset radioChecked = "" />
		</cfif>
		<input type="radio" name="favoriteColor" value="#favoriteColors.favoriteColor#" #radioChecked# />
	</cfloop>
</cfoutput>

ColdFusion 8 Release Date Contest Over - Koen Brenner Is Victorious

A huge thanks to Ben Nadel for hosting the ColdFusion 8 Release Date Contest. Amazingly, I was able to stretch my psychic muscles just far enough to walk off with the prize. Congratulations to Adobe for releasing the long-awaited ColdFusion 8. I can’t wait to get my hands dirty.

I spent the last few years as a development manager, barely keeping up with the meetings, documentation, and system research necessary for my team to be successful. I didn’t have a lot of time to be involved or even to pay very close attention to the ColdFusion community. I must say I missed it, to say the least.

I feel real lucky to get to be a developer again. I certainly enjoyed managing an awesome group of programmers, but it feels better to me to be the one in the saddle, rather than the one leading the horse.

My first challenge as a ColdFusion developer was to rediscover the community and plug myself back in. This is a collection of resources I have found to be invaluable for bringing me back up to speed and for staying that way.

Blogs

There are some amazing bloggers out there, constantly sharing a wealth of information with the community. The current blogs I have found are listed in my Blogroll in the sidebar, or you can visit my shared Google Reader ColdFusion tag, or you can download my OPML (Outline Processor Markup Language) file that can be imported into your favorite reader. If you have discovered a ColdFusion blog I do not have listed, please let me know.

Mailing Lists

It seems to me that the most popular of the mailing lists is CF-Talk, hosted by House of Fusion. There is a steady flow of questions and solutions bouncing around on this list. I’ve learned a ton just by browsing the messages.

Magazines

Although it may be practically impossible to hack your way through the barrage of obnoxious advertising on the Sys-con website, I still believe that the ColdFusion Developer’s Journal is worth it.

CFUGs

Mostly I just like to say CFUG out loud. Also for years now, local ColdFusion User Group meetings have brought together isolated pockets of ColdFusion developers to discuss current methodologies and to experience practical and high-level presentations that inspire and grow the community from within. If there is a User Group near you, I would highly encourage you to attend a meeting.

………

This, of course, is only the beginning. There are many forums, framework-specific websites, tutorial/learning sites, open source repositories, and conferences to discover. I wouldn’t want to take all the fun out of it.